SSIS and parsing empty strings to integers
-
Using SQL Server 2008 R2 and SSIS, I just ran into a little problem. The source table has a VARCHAR column that contains mostly integers, also some NULLs, and some empty values. I'm copying rows to another table where the column is INTEGER, and SSIS is throwing: [ADO NET Destination [16]] Error: An exception has occurred during data insertion, the message returned from the provider is: The given value of type String from the data source cannot be converted to type int of the specified target column. After a few hours trying to narrow down what string value and which int column, I found that it is the empty strings causing the trouble. I have now wrapped the troublesome columns in CASE statements to replace empties with NULLs, but here's my question: If I execute the following in SSMS or via ADO.net
CREATE TABLE dbo.NumTest (Num int)
INSERT INTO dbo.NumTest VALUES ( '' )no Exception is thrown -- the resultant value is 0. So why the elephant does SSIS throw an Exception? Why is it trying to parse the string? Shouldn't it simply pass it to the database and react to any Exceptions? And how can I tell SSIS not to interfere?
-
Using SQL Server 2008 R2 and SSIS, I just ran into a little problem. The source table has a VARCHAR column that contains mostly integers, also some NULLs, and some empty values. I'm copying rows to another table where the column is INTEGER, and SSIS is throwing: [ADO NET Destination [16]] Error: An exception has occurred during data insertion, the message returned from the provider is: The given value of type String from the data source cannot be converted to type int of the specified target column. After a few hours trying to narrow down what string value and which int column, I found that it is the empty strings causing the trouble. I have now wrapped the troublesome columns in CASE statements to replace empties with NULLs, but here's my question: If I execute the following in SSMS or via ADO.net
CREATE TABLE dbo.NumTest (Num int)
INSERT INTO dbo.NumTest VALUES ( '' )no Exception is thrown -- the resultant value is 0. So why the elephant does SSIS throw an Exception? Why is it trying to parse the string? Shouldn't it simply pass it to the database and react to any Exceptions? And how can I tell SSIS not to interfere?
This is why we rip the T out of ETL, load the source into a staging table where every field is varchar and use a stored proc to do the transforms. SSIS is not alone in these idiotic inconsistencies, Biztalk and most of the big players in ETL also have them. It is also way easier to debug and test the transforms using a proc!
Never underestimate the power of human stupidity RAH
-
This is why we rip the T out of ETL, load the source into a staging table where every field is varchar and use a stored proc to do the transforms. SSIS is not alone in these idiotic inconsistencies, Biztalk and most of the big players in ETL also have them. It is also way easier to debug and test the transforms using a proc!
Never underestimate the power of human stupidity RAH
I hear ya. This is my first time (eight months now) using SSIS and I don't like it. Previously I always used code. I'm doing a lot more pieces of this SSIS with "Script Tasks" -- C# code.
-
I hear ya. This is my first time (eight months now) using SSIS and I don't like it. Previously I always used code. I'm doing a lot more pieces of this SSIS with "Script Tasks" -- C# code.
I had a class called filehandler, used in c# apps to load csv files, when we first started using SSIS I just hacked that class into the script objects, always intending to move to the SSIS data objects. It never actually happened, they are still using that class and a proc to do the transforms so SSIS is just an execution and scheduling shell.
Never underestimate the power of human stupidity RAH
-
Using SQL Server 2008 R2 and SSIS, I just ran into a little problem. The source table has a VARCHAR column that contains mostly integers, also some NULLs, and some empty values. I'm copying rows to another table where the column is INTEGER, and SSIS is throwing: [ADO NET Destination [16]] Error: An exception has occurred during data insertion, the message returned from the provider is: The given value of type String from the data source cannot be converted to type int of the specified target column. After a few hours trying to narrow down what string value and which int column, I found that it is the empty strings causing the trouble. I have now wrapped the troublesome columns in CASE statements to replace empties with NULLs, but here's my question: If I execute the following in SSMS or via ADO.net
CREATE TABLE dbo.NumTest (Num int)
INSERT INTO dbo.NumTest VALUES ( '' )no Exception is thrown -- the resultant value is 0. So why the elephant does SSIS throw an Exception? Why is it trying to parse the string? Shouldn't it simply pass it to the database and react to any Exceptions? And how can I tell SSIS not to interfere?
The problem you are experiencing is a data type issue. The source is varchar, which means SSIS read and mapped the source adaptor to varchar and its output column to varchar. Inserting that varchar into the int caused your problem. To prevent this issue, in the data flow task -> Right click on the source -> scroll down to “Show Advanced Editor” Select “Input and Output Properties” Navigate down to “Output Columns” Locate the column in question and change the data type to something more appropriate such as “four-byte signed integer [DT_I4]” SSIS tries to guess what the source data type is and land it in the same data type without knowing everything in advance. SSMS builds the query first with the set of data then does the insert, so it knows to recast the data to the proper type. You could also change the error handling of the destination object to ignore the error. While this works as well it could lead to other issues and is less than desirable.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.