SSIS Task for inconsistent column count import?


I regularly receive a feed files from different suppliers. Although the column names are consistent the problem comes when some suppliers send text files with more or less columns in there feed file.

  • SQL Server : Decimal Precision/Scale are yielding strange results
  • SQL Server : database design - composite primary key
  • MERGE Query running very slow in Management Studio 2008 Express
  • Multiple rows into a single row and combine column SQL
  • Get Connection String from Method returning Connection error Object Refrence not Set to the instance of object ???
  • Pass widget data using json and saving to database
  • Furthermore the arrangement of these files are inconsistent.

    Other than the Dynamic data flow task provided by Cozy Roc is there another way I could import these files. I am not a C# guru but i am driven torwards using a “Script Task” control flow or “Script Component” Data flow task.

    Any suggestion, samples or direction will greatly be appreciated.

    Some forums

  • Truncate/Clear table variable in Sql Server 2008
  • update database queue with lock and ordered output
  • sort results by column not row
  • SQL First In First Out Loyalty Point
  • Which SQL command can I use to see the structure of a table on SQL Server?
  • Paste MS Excel data to SQL Server
  • 2 Solutions collect form web for “SSIS Task for inconsistent column count import?”

    Off the top of my head, I have a 50% solution for you.

    The problem

    SSIS really cares about meta data so variations in it tend to result in exceptions. DTS was far more forgiving in this sense. That strong need for consistent meta data makes use of the Flat File Source troublesome.

    Query based solution

    If the problem is the component, let’s not use it. What I like about this approach is that conceptually, it’s the same as querying a table-the order of columns does not matter nor does the presence of extra columns matter.


    I created 3 variables, all of type string: CurrentFileName, InputFolder and Query.

    • InputFolder is hard wired to the source folder. In my example, it’s C:\ssisdata\Kipreal
    • CurrentFileName is the name of a file. During design time, it was input5columns.csv but that will change at run time.
    • Query is an expression "SELECT col1, col2, col3, col4, col5 FROM " + @[User::CurrentFilename]

    variables window

    Connection manager

    Set up a connection to the input file using the JET OLEDB driver. After creating it as described in the linked article, I renamed it to FileOLEDB and set an expression on the ConnectionManager of "Data Source=" + @[User::InputFolder] + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"text;HDR=Yes;FMT=CSVDelimited;\";"

    Control Flow

    My Control Flow looks like a Data flow task nested in a Foreach file enumerator

    control flow

    Foreach File Enumerator

    My Foreach File enumerator is configured to operate on files. I put an expression on the Directory for @[User::InputFolder] Notice that at this point, if the value of that folder needs to change, it’ll correctly be updated in both the Connection Manager and the file enumerator. In “Retrieve file name”, instead of the default “Fully Qualified”, choose “Name and Extension”

    Foreach File Enumerator - Collection tab

    In the Variable Mappings tab, assign the value to our @[User::CurrentFileName] variable

    Foreach File Enumerator - Variable Mappings tab

    At this point, each iteration of the loop will change the value of the @[User::Query to reflect the current file name.

    Data Flow

    This is actually the easiest piece. Use an OLE DB source and wire it as indicated.

    Data flow

    Use the FileOLEDB connection manager and change the Data Access mode to “SQL Command from variable.” Use the @[User::Query] variable in there, click OK and you’re ready to work.
    oledb file source

    Sample data

    I created two sample files input5columns.csv and input7columns.csv All of the columns of 5 are in 7 but 7 has them in a different order (col2 is ordinal position 2 and 6). I negated all the values in 7 to make it readily apparent which file is being operated on.




    Running the package results in these two screen shots

    5 column file 7 column file

    What’s missing

    I don’t know of a way to tell the query based approach that it’s OK if a column doesn’t exist. If there’s a unique key, I suppose you could define your query to have only the columns that must be there and then perform lookups against the file to try and obtain the columns that ought to be there and not fail the lookup if the column doesn’t exist. Pretty kludgey though.

    Our solution. We use parent child packages. In the parent pacakge we take the individual client files and transform them to our standard format files then call the child package to process the standard import using the file we created. This only works if the client is consistent in what they send though, if they try to change their format from what they agreed to send us, we return the file.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.