September 15, 2010 at 1:32 pm
SSIS Package is loading a .CSV file into a SQL 2005 table... failing w/ error:
There was an error with input column "Col_FLOAT_1" (93) on input "OLE DB Destination Input" (22). The column status returned was: "The value could not be converted because of a potential loss of data.".
Input data record on .CSV file
XYZ,0,-0.0367,0.1241,NULL,NULL,NULL,2,09/25/2010
SQL 2005 SQL Table DDL:
CREATE TABLE [dbo].[My_Table](
[Col_1] [varchar](50) NOT NULL,
[Col_2] [int] NOT NULL,
[Col_FLOAT_1] [float] NULL,
[Col_FLOAT_2] [float] NULL,
[Col_FLOAT_3] [float] NULL,
[Col_FLOAT_4] [float] NULL,
[Col_FLOAT_5] [float] NULL,
[Col_3] [int] NOT NULL,
[AsOfDate] [datetime] NOT NULL) ON [PRIMARY]
I understand I need to insert a DERIVED COLUMN in my data flow between my SOURCE .CSV file and TARGET SQL 2005 table. Not being a developer, I need help understanding how to populate the following DERIVED COLUMN screen values:
o Derived Column Name
o Derived Column
o Expression
o Data Type Length
o Precision
o Scale Code Page
NOTE: the business requirement is to retain NULL values on the SQL table "if" they are NULL in the .CSV file -- In the above example, I will need NULL on columns Col_FLOAT_3, Col_FLOAT_4, and Col_FLOAT_5
Help is greatly appreciated here. Thanks in advance!
September 16, 2010 at 7:14 am
In your flat file source, you can select the option
Retain null values from the source as null values in the data flow
Try it and let us know if it works.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 17, 2010 at 6:57 am
thx. yes, I already had that checkbox marked.. (Retain null values from the source as null values in the data flow)
The problem resides w/ the input record values containing the actual word 'NULL' between the columns:
This fails: XYZ,0,-0.0367,0.1241,NULL,NULL,NULL,2,09/25/2010
This works: XYZ,0,-0.0367,0.1241,0.1,0.1,0.1,2,09/25/2010
This works: XYZ,0,-0.0367,0.1241,,,,2,09/25/2010
How can I inspect the input record for the actual value 'NULL' then load the table with NULL values?
September 17, 2010 at 7:23 am
You have two options:
* read everything as string, replace the NULL strings and then convert everything to the appropriate datatype.
* use a script task to read the file and replace the NULL strings with empty strings (aka no data for that column in the specific row) and then read everything in with a dataflow
Whatever suits you best...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply