SQL Server SSIS - Flat File Import to Staging Table
Hello,
I am importing a flat file source (csv) to a staging table, the flat file contains about 50 rows, and about 4gb of data.
Now, when I first define my flat file source, in the connection manager every row is defined a DataType of string [DT_STR] with a length of 50.
Is it suggested to use the "Suggest Type" button to have SQL define my rows or manually define them? Then, apply the same design to my staging table rows?
What is happening if I do use suggest type, then going straight to my database, I left SQL create the db, I get the following error:
Error: 0xC02020A1 at Stage Sales Extract, Flat File Source [41973]: Data conversion failed. The data conversion for column "COMMODITY" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
The COMMODITY row in the CSV file is defined as eight-byte signed integer [DT_I8], and in the staging table it is defined as "bigint".
When I say I let SQL create the table, in the OLE DB Destination, when I click it I have the option to select a "New" table, and the code is predefined for me with the data types and lengths.
Should I be manually defining my Data Types from the CSV to the staging?
If so, anyone have an example of the data types that I should be assigning to my rows based on the data in there and the finally in the staging table design?
New to SSIS/SQL, this was a project thrown at me and I am trying to get through this.
If I use [DT_STR] when defining my CSV Data Type (this is numbers and characters?), this should be set as varchar(lenght) in the table design?
Thank you for your help.
I am importing a flat file source (csv) to a staging table, the flat file contains about 50 rows, and about 4gb of data.
Now, when I first define my flat file source, in the connection manager every row is defined a DataType of string [DT_STR] with a length of 50.
Is it suggested to use the "Suggest Type" button to have SQL define my rows or manually define them? Then, apply the same design to my staging table rows?
What is happening if I do use suggest type, then going straight to my database, I left SQL create the db, I get the following error:
Error: 0xC02020A1 at Stage Sales Extract, Flat File Source [41973]: Data conversion failed. The data conversion for column "COMMODITY" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
The COMMODITY row in the CSV file is defined as eight-byte signed integer [DT_I8], and in the staging table it is defined as "bigint".
When I say I let SQL create the table, in the OLE DB Destination, when I click it I have the option to select a "New" table, and the code is predefined for me with the data types and lengths.
Should I be manually defining my Data Types from the CSV to the staging?
If so, anyone have an example of the data types that I should be assigning to my rows based on the data in there and the finally in the staging table design?
New to SSIS/SQL, this was a project thrown at me and I am trying to get through this.
If I use [DT_STR] when defining my CSV Data Type (this is numbers and characters?), this should be set as varchar(lenght) in the table design?
Thank you for your help.
I generally take the approach that I have no expectations of my incoming data, so I import the data into a staging table that is completely defined as VarChar(xx). Once it is in the saging table, I then do the manipulations, conversions, etc., in order to move it to the target table.
ASKER
So your setting your staging table as VarChar(MAX) then fine tuning it when importing from the staging table (in my case) the production table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks this has helped.
http://msdn.microsoft.com/en-us/library/ms141036.aspx
If you are unaware of the Data or Length of Data in the File. I would suggest you to use (DT_STR) with length (as suggested) .. It is advisable to build your own table accordingly and then Select from the Table List in the OLE DB Destionation..
(DT_STR,12,1252) -- means that it is a string dataType, with length -12 and Codepage -1252 (Default) that should be transformed to Varchar(12) in SQL