none
SSMS 2012: Importing ".txt" data with SQL Server Integration Services (SSIS) - output file has extra 1 column and 2 rows (1 blank row & 1 "NULL" row). How to avoid the extra column and rows?

    Question

  • Hi all,

    I followed the steps of Mike Chapple's artice "Importing Data with SQL Server Integration Services (SSIS)" listed in http://databases.about.com/od/sqlserver/ht/importing_data_with_SSIS.htm to import a .txt file into the Testshc database of my SSMS 2012.  I have the following input .txt file:

    I got the following output result:

    This output file has extra 1 column (Column 3) and 2 rows (1 blank row and 1 "NULL" row).  How can I avoid these extra column and rows in executing this project?

    Please kindly help and advise.

    Thanks in advance,

    Scott Chang


    • Edited by Scott_Chang Wednesday, May 01, 2013 8:15 PM
    Wednesday, May 01, 2013 8:12 PM

Answers

  • Hi Scott,

    Extra column--

    Your txt file has a ';' right at the end in the first row. By the looks your txt file has 'first row as coloum names'. The last ';' is translated as a column in your data transfer process. And that adds an extra column in your DB table.

    So all you need to do is remove the last ';' in the first row of your txt file.

    Extra rows--

    The NULL row is the SQL place holder so you dont have to worry about that, its there by default.

    The blank row is again because you have a blank row in your txt file. Please check your txt file.
    you may have accedently clicked 'ENter' after the last row. and that adds an extra blank line in your DB table.

    So just check your txt file and delete everything after the last digit in the last row.

    This should fix the 2 issues.

    Hope this helps.

    Please mark if it does.

    Let me know how it goes.

    Thanks,
    megha

    • Proposed as answer by Megha Barpande Wednesday, May 01, 2013 11:32 PM
    • Marked as answer by Scott_Chang Thursday, May 02, 2013 11:49 AM
    Wednesday, May 01, 2013 11:32 PM