SSIS: Transferring Data from Flat File to SQL "The column delimiter for column was not found."
Transfer data from Text File to SQL Server? Stuck with Not so properly delimited Text File?
Here goes the solution J
I was once having a text file which I was trying to use with SSIS and transfer data to SQL.
The following error was stopping me from transferring the data.
Error 0xc0202055: Data Flow Task 1: The column delimiter for column "Last Name" was not found.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "E:\Test.txt" on data row 2512725.
(SQL Server Import and Export Wizard)
Btw, the Text File Size was 1.5 GB, so no option of opening the file and Finding the Missing Delimiter.
Using SSIS 2012? Then you are lucky!! There is a Property called "AlwaysCheckForRowDelimiters" . We can set that to false and this will skip that row.
But I was using SQL 2008. What Next Now?
Thankfully, there is a CodePlex Component that could help us in this case.
Download the CodePlex Component. Once done, In your BIDS/SSDT Right Click the Toolbox and Click “Add Items” . Under the SSIS Data Flow Items, Choose DFS 2008.
Then, Under the Data Flow Task Page, Drag and Drop DFS 2008 from Source.
Double Click on the Source. We need to select “Treat empty Strings as Null” option.
Then when we go to the Columns Tab, we can see the data and the columns properly aligned.
Now comes the little overhead part. This task doesn’t create output Columns automatically. We have to create the columns manually. For this, go to the “Advance” Tab and then Click New->New Column. This would mean, say if you have 10 columns in the Table, then you have to manually create all the 10 columns.
Once the columns are created, add the Destination Component. Make sure the mappings are proper.
Now we are good to go! The DFS component takes care of the missing Delimiter! It solves a big issue!!
Happy Learning