locked
Additional columns in flat file RRS feed

  • Question

  • I am writing a small SSIS package, with 2 steps:

    The first step, connecting to my OLE DB Source, I am writing a SQL statement to select 20 columns from a table. The preview looks as I'd expect. Clicking on the properties, in the columns tab, I can see only the 20 columns.

    In the data flow to the flat file, in both the metadata and data viewer tabs, I can see my 20 columns, and no more.

    In the 2nd step, the flat file destination, in the mappings tab, the destination columns now include all columns from the original table from which I applied my select statement. There are about 100 columns and 80 of them have the value "<ignore>" on the input Column tab (the other 20 are mapped as I'd expect). When I try to right-click on any of the "ignore" rows and select delete, it does nothing (delete isn't greyed out, but clicking on it does nothing)

    When I run the package, a flat file is created with all 100 headings and a load of blank data.

    Why is the SSIS package retreiving the 80 columns when I haven't asked it to? And how can I stop it? I only want my flat file to contain my 20 columns that I specify.

    I'm using SQL Server Data Tools for Visual Studio 2013 (and I'm new to it)

    Wednesday, January 30, 2019 4:36 PM

Answers

  • Hi PetyrBaelish, 

    Just delete the Flat File Connection Manager and Destination, and recreate them following the steps in below link.

    SSIS FLAT FILE Destination

    It seems that the Flat File Connection Manager was designed for another Data Set, and it's data structure is fixed according to the original Data Set. You just need to create it according to the new Input.  

    Similar thread: Flat File Connection Manager Adding Blank Columns



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by PetyrBaelish Thursday, January 31, 2019 11:58 AM
    Thursday, January 31, 2019 6:27 AM

All replies

  • I guess you tried to retrieve data from the table. Right click on the OLE DB Source task and select "Show Advanced Editor...". On the tab "Column Mappings", click the button "Refresh" and see if that helps.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, January 30, 2019 5:05 PM
  • Hi PetyrBaelish, 

    Just delete the Flat File Connection Manager and Destination, and recreate them following the steps in below link.

    SSIS FLAT FILE Destination

    It seems that the Flat File Connection Manager was designed for another Data Set, and it's data structure is fixed according to the original Data Set. You just need to create it according to the new Input.  

    Similar thread: Flat File Connection Manager Adding Blank Columns



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by PetyrBaelish Thursday, January 31, 2019 11:58 AM
    Thursday, January 31, 2019 6:27 AM
  • I think what happened was you connected the flat file connection manager initially with select * from yourtable as the query which caused it to initialize flat file metadata with full 100 columns. Later you modified the query to include only 20 columns. But metadata once set will not get refreshed for the flat file.

    So what you need to do is this

    1. delete the existing flat file connection manager and flat file destination task

    2. add a new flat file destination task and a connection manager after linking current output from OLEDB source. This time the metadata will get created only with the 20 columns you chose

    3. go to mappins tab in flat file destination task and make sure the 20 columns are correctly mapped from source to your destination

    4. Execute the package and you can see data for 20 columns in your file.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 31, 2019 6:52 AM