SSIS 2012 Flat File Connection Manager

There have been several enhancements made and features added to Integration Services with the release of SQL Server 2012.

One of the SSIS components that has been enhanced is the Flat File Connection Manager. The Flat File Connection Manager no longer errors out when columns are missing from the Flat File. See Row2 in example below:

Figure 1

image

Figure 1 is a simple example of a text file with two columns and three rows that tests three different scenarios.  Row1 has values for both columns. Row2 is missing the last column and row3 contains extra columns which are not defined in SSIS package metadata.

Figure 2

image

In Figure 2 I setup a very simple SSIS package that pulls all the rows from the text file in Figure 1. Notice that row2 from the text file is missing the last column and returns an empty/null value. In the older version of SSIS row2 would have produced an error at run time.

The interesting thing about this example is that row3 col2 contains all the  values for columns that were not originally defined in the SSIS metadata. This means that unless you want all those values in the last column you should redirect those rows using a Conditional Split.

Advertisements

Tags:

4 Responses to “SSIS 2012 Flat File Connection Manager”

  1. Prasanna Says:

    how to configure if I need to error out in such situations in SSIS 2012

  2. Garrett Edmondson Says:

    @Prasanna – you can use IDTSComponentEvents.FireError Method but you need to think about what to do with the rows that were processed before error was fired. I would suggest using a conditional split to redierct the rows and/or to substitute in “dumby” values as needed. That way the rows with errors can be fixed and reloaded as needed.

  3. Zaim Raza Says:

    Hi Garrett,
    It’s a feature or bug ? i am specifically asking for last row it should parse as 5 columns as showing in the SSIS blog as well as…

    http://blogs.msdn.com/b/mattm/archive/2011/07/17/flat-file-source-changes-in-denali.aspx

    • Garrett Edmondson Says:

      It is meant to be a feature (by design ) because in certain situations SSIS wont fail when it otherwise would have with the older version. However, if you are unaware of this new behavior in SSIS then you might get unexpected results or bug like behavior. I can understand why some might think that it is a bug. I did at first !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: