February 8, 2010 at 3:08 pm
I'm trying to use the Data Conversion Tool and no matter what i select I get an error such as this:
Validation error. Data Flow Task: SQL Server Destination [1060]: The column "Duration" can't be inserted because the conversion between types DT_R8 and DT_I4 is not supported. Package.dtsx
Is there no information on what Conversion is supported? I haven't found it.
February 8, 2010 at 3:49 pm
I found the matching format in the Data Conversion Tool and my limited test worked.
Thanks for the hints. I have A LOT to read since I'll receive no format training/classes.
I'm FAR from done, but this is a good start!
February 8, 2010 at 3:50 pm
The data conversion tool make a new column called cop of [column name]. This column is the one you need to insert into the final table as it should be of the correct data type. You will need to adjust the mappings in the data destination to accomplish this.
The other option is to create a new table in the destination server and use the new function in SSIS to create the table. This will ensure all of the data types in the Excel sheet will copy to the SQL server. You can then write a SQL query that moves the table to the destination using CAST and CONVERT to modify the data types.
February 9, 2010 at 5:07 pm
Alan. T. (2/8/2010)
You can then write a SQL query that moves the table to the destination using CAST and CONVERT to modify the data types.
Sorry, but in the DATA FLOW TRANFORMATIONS, what "task" can I use to write a Query that will perform some SQL commands, use a value from a FLAT FILE SOURCE task and then INSERT that data into a Database Table?
February 22, 2010 at 4:07 pm
I'm not a guru but have suffered through the same learning curve you're hitting now.
You can't think of SSIS in the same way as DTS or T-SQL. It just doesn't work that way.
Right now it sounds like you're working in a data flow. I'll do my humble best to talk you through what you need to consider but you'll have to use trial-and-error as well as the web and BOL for the specifics.
Once you read a source it's in metadata. So you have the data available for transformations. Those transformations are what you use instead of queries. If you need to convert data, you may need to use a Derived Column transformation and "replace" the original column in the rest of the flow. Giving up T-SQL was one of the most difficult things for me to do, honestly.
Inserting data is a destination, updating/deleting is actually a transformation. Confused yet?
Data viewers are your friend. Use them heavily to see exactly what's going on with each step. Look at both the basic and advanced options for each transformation. Note that your data is not in SQL once you bring it in as a source - it's metadata and completely different. It doesn't become SQL until you write it back to a table.
February 22, 2010 at 4:14 pm
Sorry I'm slow to answer but you can use SQL with the data source by selecting it in the drop down.
In the dataflow task the SQL operator is called the OLEDB command and you can input SQL there. Don't forget the data conversion tool it is a lot less work generally.
Alan
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply