When Identity Matters

In a perfect world identity columns should have no real-world meaning and should never be exposed to the user. However, this is not always the case. I've seen systems which use identity columns for booking numbers (which are then exposed to customers) and other systems where records in dimension tables start to be commonly refered to by their surrogate key values, rather than by natural key or name.



As soon as such values start to matter then a potential problem looms if you need to introduce a row with a particular value for the identity column. You can't just go ahead and issue a simple T-SQL INSERT statement, doing so will result in an error.



In order to successfully perform this type of insert you'll need to turn IDENTITY_INSERT on for the table in question. Like so,



SET IDENTITY_INSERT myTable ON



GO



INSERT myTable(ID, Name, Level)

VALUES (2315, 'A new test value', 5)



GO



Once you've finished inserting rows be sure to toggle IDENTITY_INSERT off for the table.



SET IDENTITY_INSERT myTable OFF



GO



Incidently if you are using SSIS to insert data using a Data Flow then take a look at the Advanced options for the SQL Destintion. Ticking the Keep Identity option on this page will allow you to insert values into an identity column.

Dynamic Filenames for FlatFiles with SSIS

Importing from flatfiles with SSIS is relatively quick and easy to setup. But what if you need to load from many files with the same structure but all with different filenames? You could do this by manually changing the properties of the connection, running the SSIS package to load one file and then repeating until all of the files had been loaded. It works, and I've done it myself when there have only been a handful of files to load into the database. Recently I was faced with a situation where there were in excess of 100 files which would all need to be loaded on an ongoiong basis. The manual approach wouldn't do the trick here - some automation was required.



The answer lay in using a dynamic filename for the source file. This is actually quite quick and easy to do once you know where to look. Here's what you need to do.


Bring up the properties on the connection to the flatfile and click on the plus sign to expand the list of expressions. Use the elipse button to bring up a window to allow you to add a new expression.

Select the ExcelFilePath option from the property dropdown list and then use the elipse button beside the expression column to build the expression that will return the value of the path and filename for the source file. In my case I concatenated together two package level variables I had created earlier.

You can use the Evaluate Expression button to check the value you get from the expression is what you expected to see.

From here you can build a data flow to load data (using this connection) just as you would using a hard coded connection.

This example has used an Excel flatfile. You can use a similar approach with text files. Rather than set an expression for the ExcelFilePath property, set the expression for the ConnectionString property.

Unexplained NULLs from Excel Sources in SSIS

Today when using SSIS to transpose some data I run into a problem which I'd seen before but had never had time to properly investigate and understand. Today the alternative to solving the problem was a lot of manual data manipulation so it was time to dig in and work out what was going on.


So, here's the problem, you may well have struck it yourself.


My Excel spreadsheet has a text column and three columns of integers.

But when I added an SSIS Excel Source and previewed the data I saw the following:

Rather than seeing the values of "Battery Point", 2, 1 and 1 for the first data row I had "Battery Point", 2, 1, NULL. Where had the value for the last column gone?

Some digging around MSDN led me to learn that the Excel driver reads 8 rows to determine the data type for each column in the source file. When a column has mixed data types the majority type wins, with numeric types winning over text types in the event of a tie. Cells in that column with the losing type(s) are given a NULL value. In my case I had one numeric cell and seven text cells in the first eight rows of the column. So my integer value of 1, despite the fact that it was in the first row, lost out under the collective weight of the following seven rows and was made into a NULL.

It is possible to avoid this by telling the driver to work in Import Mode. To do this bring up the Properties Page of the Connection object and add the value IMEX=1 to the extended properties of the connection string. You'll need to manually type the value into the connection string rather than using the ellipse or you won't be able to make the change.

So, in my example I start with this

And change it to this

A quick check of the preview from the Excel source reveals my value of 1 is back - the NULL is gone!

By the way, it's also possible to change the number of rows that Excel samples to guestimate the data type for each column to something other than the default value of 8. This is done by altering the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel and changing the value of TypeGuessRows to your new value.