SSIS - SQL Server 2012

I have been thinking to explore SQL server 2012 since its lanuch date April 2nd 2012. I had opportunity to work using SQL server 2005, 2008 and 2008 R2 BI tools and now SQL server 2012 code name changed to DENALI. Microsft is boasting about Denali's capability in self service and corporate business intelligence and we can bet on this only after some years.

One of the main change that I liked in SSIS is its look which is totally changed from its predecessor. Interface is improved to include details about the selected task and sample to learn about it, added one more window to work with package parameters, tool box is having two more windows to add favourites and common tasks and the user can choose which task can be added into these windows.

Another useful but minor feature that developer would like is undo and redo feature which was not available in earlier versions.

In this post I would like to explain about another wonderful feature which will definitely a great help to the developers for loading ragged-right delimited file (A file with variable number of columns).

Appreciated Microsoft for including this feature in SQL 2012 as ragged-right delimted data load has given enough trouble to developers.

You need to disable the new processing behavior by changing the AlwaysCheckForDelimters property of the flat file connection manager to false.

Sample :

I have Order details file with four columns Item, Qty, UOM, price and Tax as shown in figure 1.

Figure 1

loaded this data into the table Order using SSIS package.



Figure 2

Now lets say some products which are tax free will not have the last column. We need to disable the property AlwaysCheckForDelimters of the flat file connection before loading the file as shown in figure 3.

See the below order file columns. you can see that tax column is missing and I did not change the flat file settings other than the above one.

SSIS package executed successfully and the tax field is empty for one record in the database.