CODEWITHSUNDEEP
sql-serverssisetllookupdata-conversion
user8504877
user8504877

Reputation:

SSIS Datatype Conversion issue

I have build a SSIS Package with FlatfileImport(csv)---DataConversion---Lookup Transformation---OLDDBDestination. This package has erros between DataConversion and SearchTransformation.

After the csv import I try to convert a csv field into decimal because in the DB the field has the format decimal but when I make a connection in the Look up Transformation from csv table to db table, I get an error with datatype is different.

Any idea what the problem is?

Upvotes: 1

Views: 1153

Answers (1)

userfl89
userfl89

Reputation: 4790

Make sure that the SSIS data types of both columns used in the lookup match. The data type resulting from the decimal conversion should be DT_NUMERIC, which corresponds to the SQL Server decimal data type as stated in the mapping chart of the documentation. To verify that the data type of the input column used in the mapping to match in the lookup is also DT_NUMERIC right-click the Lookup and select Show Advanced Editor. After this go to the Input and Output Properties tab, then the Lookup Input node, expand the Input Columns folder below that and highlight the column used in the lookup. The Common Properties window on the right will show the data type. If this is not DT_NUMERIC change the lookup to use a SQL query instead and cast this column as a decimal (SQL Server) data type with a SQL command, then verify that it is now DT_NUMERIC in the Advanced Editor. I'm assuming the Lookup is to a SQL Server database, if not see the other columns in the data mapping chart of the SSIS reference above. You will also want to ensure that the scale and precision is the same for both columns used in the Lookup, which can be viewed on the Advanced editor of the Lookup as well. For the Data Conversion Task, this can be found either on the regular editor or Advanced Editor by going to Input and Output Properties > Data Conversion Output > Output Columns > then select the converted column.

Upvotes: 1

Related Questions