SQL Lion

Last week I come across a serious problem with SSIS while dealing with text files especially with tab delimited text files. It is showing the wrong data after importing into SQL SERVER database. I was shocked by this behavior of SSIS. The total row count in the destination table is much smaller than the source file row count. When I opened the same file in Excel or notepad, I saw the data is correct and properly aligned. Then I deeply dived into the problem and tried to figure out what may be the cause for such an unexpected behavior. Even I am surprised more when I implemented the same process in DTS package (SQL 2000); there it is showing me correct number of records at the destination. Pretty strange, all three products i.e. SSIS, DTS and Excel are from the same manufacturer (Microsoft), but showing different behavior for same type of process (importing data from text file).

Let us see the real problem behind the picture with some examples and in an elaborated way.

Let us consider the below dataset as our source text file data.

Note: The data shown above is a set of dummy data. Any resemblance to real values is purely coincidental.

Source Files:

The above set of data is perfectly correct and shows correct number of rows after importing into SQL Server database. But before going forward to the actual problem statement, let us see how to import text files. Let us consider “SQL Server Import and Export Wizard” to perform this simple task as this wizard is using SSIS package at the backend to perform any transformation of data. Follow the below steps to import text files into SQL Server:

Step 1: Open SSMS (SQL Server Management Studio), Right click on any database and navigate to [Tasks]→[Import Data…].

Step 2: In the “SQL Server Import and Export Wizard” dialog box, click on next.

Step 3: In the “Choose a Data Source” pane, select “Flat File Source” from the “Data Source” drop down box.

Step 4: Select the file “CarInfo.txt” that you have downloaded as the source text files from above in “FileName” text box.

Step 5: Check the option “Column names in the first data row” and click on “Columns” at the left pane.

Step 6: You can see the Columns and the data are aligned properly.

You may notice here that it has a total of 9 records.

Step 7: Now go back to “General” and select the second file named “CarInfowithMissingData.txt” in File Name text box. You may see a warning dialog box as below to restate the column metadata.

Click on “No” to restate the column information.

Step 8: Again go to “Columns”, you will notice that some of the cell don’t have data or NULL.

Here even if the data is missing in some Cells but it is aligned properly and we have same number of records as in the source. The data is missing because it is not there in the source itself.

Here if you open the text file in notepad, you will see that the data for some columns are blank but all blank cells are separated by tabs.

Step 9: Now go back again to “General” pane and load the data from the text file named “CarInfoWithImproperFormating.txt”.

This time you will notice that the data is not aligned properly. And moreover, we got only 6 records instead of 9 records. Surprisingly, the below records got merged with the upper records in some cases.  If we open the text file in notepad, we can see those empty cells are not separated with tabs. But if you open the same source file in excel, you will get the data in proper order and aligned properly.

So question is why is that so, why Excel; why not SSIS?

Let’s find out the story behind it, in my next post : << SSIS vs Text File Importing –II >>