SSIS Task for inconsistent column count import?

by

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.

Variables

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.

col1,col3,col2,col5,col4
1,3,2,5,4
1111,3333,2222,5555,4444
11,33,22,55,44
111,333,222,555,444

and

col1,col3,col7,col5,col4,col6,col2
-1111,-3333,-7777,-5555,-4444,-6666,-2222
-111,-333,-777,-555,-444,-666,-222
-1,-3,-7,-5,-4,-6,-2
-11,-33,-77,-55,-44,-666,-222

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.

More Related Contents:

  1. How to execute an SSIS package from .NET?
  2. Reverse engineering SSIS package using C#
  3. C#: SQL Server Connection String
  4. Delete from two tables? [closed]
  5. Using stored procedure output parameters in C#
  6. Insert entire DataTable into database at once instead of row by row?
  7. SQL Query slow in .NET application but instantaneous in SQL Server Management Studio
  8. Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
  9. SqlParameter does not allows Table name – other options without sql injection attack?
  10. Any way to SQLBulkCopy “insert or update if exists”?
  11. ‘datetime2’ error when using entity framework in VS 2010 .net 4.0
  12. What SqlDbType maps to varBinary(max)?
  13. How to keep single SQL Server connection instance open for multiple request in C#?
  14. Delete a single record from Entity Framework?
  15. How to pass XML from C# to a stored procedure in SQL Server 2008?
  16. How to use SqlCommand to CREATE DATABASE with parameterized db name?
  17. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  18. Failed to update .mdf database because the database is read-only (Windows application)
  19. What size do you use for varchar(MAX) in your parameter declaration?
  20. GO statements blowing up sql execution in .NET
  21. How to Identify the primary key duplication from a SQL Server 2008 error code?
  22. Provider named pipes provider error 40 could not open a connection to SQL Server error 2 [duplicate]
  23. Is it possible to use SqlGeography with Linq to Sql?
  24. “The operation is not valid for the state of the transaction” error and transaction scope
  25. Why does the SqlParameter name/value constructor treat 0 as null?
  26. How do I safely store database login and password in a C# application?
  27. Passing array to a SQL Server Stored Procedure
  28. Bulk Insert Sql Server millions of record
  29. Loading PictureBox Image From Database
  30. EF Core takes a lot of time, sometimes, to perform SELECT query

Leave a Comment