New Book: Knight’s SSIS 2012 24-Hour Trainer

I’m very excited to announce that you can now purchase my latest release book: Knight’s Microsoft SQL Server 2012 Integration Services 24-Hour Trainer. The 24-Hour series of books offered by Wrox is a great way to get kick started into learning a new tool. Not only are the lessons very focused, making it easier to learn something new, but you also receive a DVD that accompanies the book. The DVD walks you through each of the lessons with the author showing you an example of solving a problem using the tool. You can get your copy of my new SSIS book here!

Using Excel Macro (.xlsm) Enabled Files in SSIS

Recently while working for a client that was running SQL Server 2008 R2 I was tasked with loading an Excel Macro (.xlsm) enabled workbook.  If you have ever tried this yourself you may have tried using the default Excel provider, which does not work. In fact, this is the error message you get after trying to close the Excel connection manager.


I thought I’d go ahead and document the solution in both 2008 and 2012 for you.  Both solutions require you have the Microsoft Access Database Engine 2010 Redistributable driver installed so make that your first step before reading on.  Don’t worry it doesn’t require a server restart.

SSIS 2008

Create an OLE DB Connection Manager and use the Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider (Microsoft.ACE.OLEDB.12.0).  Then provide the path for your macro enable workbook in the “Server or file name property”.


Next click on All to modify the Extended Properties by adding Excel 12.0 Macro;HDR=YES.  Most of this text is self explanatory except the HDR which stands for header.  If you don’t want the first row of data to be the column header than change this to NO.


Click OK and use an OLE DB Source in your data flow to either select a sheet or query the workbook.

SSIS 2012

Guess what?  With SSIS 2012 you don’t have to do anything extra!  The default Excel Connection that didn’t work in 2008 does work now.  As long as you have the before mentioned Access driver you’re ready to go.  Shown below is the 2012 Source Assistant that you’ll noticed took care of the Extended Properties setting you had to manually do in 2008.  In 2012 you will use the normal Excel Source instead of the OLE DB Source.


Hope this helps!

New SSIS 2012 Expressions

SSIS 2012 has added a few new expression functions and I thought I’d spend a short time detailing each.


The LEFT function believe it or not is a new function.  In past versions of SSIS you had a RIGHT function available but no LEFT.  Well SSIS expression haters you now have what you want.  This function is described as:

Returns the left part of a character expression with the specified number of characters.


LEFT( «character_expression», «number» )


LEFT( [ProductName],3)




The new TOKEN expression is an interesting one.  It returns a string after a specified Token delimiter.  You can pass in multiple delimiters for you expression to parse and also specify the occurrence number you would like to return.  That means if you set the occurrence to 3 it would return the third instance of the token.  SSIS describes this function as:

Returns the specified occurrence of a token in a string. A token may be marked by a delimiter in a specified set of delimiters. The function returns an empty string if the occurrence is not found. The string parameter must evaluate to a character expression, and the occurrence parameter must evaluate to an integer.


TOKEN( «character_expression», «delimiter_expression», «occurrence» )


TOKEN("new expressions can be fun"," ",2)




TOKENCOUNT would likely be used in combination with the previously discussed TOKEN function.  The TOKENCOUNT function returns back the number of times a Token delimiter appears in a string value.  This would likely be plugged into the TOKEN expressions for the number of occurrences when trying to find the last occurrence.  SSIS describes this function as:

Returns the number of tokens in a string. A token may be marked by a delimiter in a specified set of delimiters. The string parameter must evaluate to a character expression.


TOKENCOUNT( «character_expression», «delimiter_expression» )


TOKENCOUNT("new expressions can be fun"," ")




Again this function answers the SSIS haters who don’t like the fact that there is an ISNULL function in the expression language but it doesn’t work like the T-SQL ISNULL. Currently if you wanted to accomplish the T-SQL ISNULL you would have do write an expression like this:

ISNULL(OrderDateSK) ? 19000101 : OrderDateSK

This uses the ISNULL function that returns back True or False if the field is NULL and also uses a conditional operator to determine how to react when it is NULL.  The REPLACENULL function will work much more like the T-SQL ISNULL function.  This function is described as:

Returns the value of the second expression parameter if the first expression parameter is null.


REPLACENULL( «expression», «expression» )


REPLACENULL(  [OrderDateSK] , 19000101 )


19000101 (if OrderDateSK is evaluated as NULL)

SSIS Project Connection Managers

With the new Project Deployment Model in SSIS developers are gaining the benefit of a new design time only feature called Project Connection Managers.  If you’ve developed in SSIS previously Connection Managers require no further detailing, but for those new this is how SSIS connections to data that’s used as a source or destination.  This new feature is visible in the Solution Explorer of SSDT (SQL Server Data Tools). 

Below you will see the traditional method (Package Deployment Model) for developing package on the left, which has a similar option called Data Sources.  Data Sources were purely a design time feature available in SSIS 2005 and 2008 to help manage connections to multiple packages.  Basically you could change a Data Source created in the Solution Explorer and the next time you opened the package that used the connection it would update the metadata.  Sounds nice but the key there is the update doesn’t occur until the next time you open it.  So if you have hundreds of packages you would have to open all of them. 

The real solution to that problem is to use configurations but I’d like to focus on the differences between the old Data Source method of doing things and the new Project Connection Manager method.

Package Deployment Model Project Deployment Model
image image

The image on the right is of the new Project Deployment Model and you quickly notice the Data Sources folder is not available.  Instead you have Connection Managers which are similar in purpose but I think accomplish the job much better than Data Sources did.  They’ve actually been made less complex if that’s possible because all you have to do as a developer is create a Project Connection Manager and then it’s automatically created in every one of your SSIS packages.  As shown below you are able to clearly tell the difference between a Project Connection Manager and a regular Connection Manager that is only scoped to a single package.


If you have a regular Connection Manager that you would like to make a Project Connection Manager then you simply right-click on it and select to Convert it.


Another nice feature of Project Connection Managers is if you make a change to one the update applies to all the packages that use it without you having to open them each.

While this is a small new feature I thought it was pretty nice and worth spending some time discussing.

Converting to the SSIS 2012 Project Deployment Model

The new project deployment model in SSIS 2012 is the new standard for how packages are created, configured and deployed.  Any new packages that are created in SSIS will default to using the new deployment model. 

The most basic way of describing this change is now instead of deploying packages we now deploy projects.  Projects get deployed to the new Integration Services Catalog, which I’ll cover in a future post.  Also, you get the benefit of project parameters. which allow you to pass values very easy across multiple packages in a project.  Jamie Thomson has a great series on this topic as well I encourage you to read what he’s written here.

After upgrading to a project deployment model you also lose some capabilities you previously had.  For example, configurations that were a staple for how to configure you SSIS packages across multiple environments in previous versions of SSIS are no longer available.  The previously mentioned project parameters now replace configurations along with Environments, which live on the Integration Serves Catalog. 

Obviously these are a lot of new concepts and totally changes the way you think about deploying and configuring SSIS so I plan on explaining each of these in separate posts.  In a previous post I walked you through upgrading your SSIS packages to SSIS 2012.  As I described in that post, the upgrade wizard does not change the deployment model for you.  So after completing a package upgrade you will be running the legacy package deployment model as labeled below.  This means everything developed in the original packages is still available including configurations.  You can still run you packages using the package deployment model but I would recommend upgrading for the benefits detailed earlier and just like anything else new at Microsoft the old way of programming will likely be deprecated over time.


Once you are ready to convert from the package deployment model to the project deployment model right-click on the project name and select Convert to Project Deployment Model as shown below.


As soon as you select that you may get prompted with a warning telling you that Data Sources you have in the Solution Explorer will be removed with the project deployment model.  These are no longer supported with the project deployment model but are could be replaced with share connection managers.  Unfortunately, it does not automatically replace your data sources with shared connection managers.  Click OK on the warning.


The start of the wizard details the 7 step conversion process, which includes converting configurations to project parameters and changing execute package tasks.  Click Next when ready.


The first step is to select the packages you want to convert and provide and passwords that you may have used to encrypt them.  After making your selection click Next and the wizard will load the packages for making the conversion.


You will then be asked to assign a name to the project if different from the original name.  New in the new deployment model you can also assign a protection level to the project.  Once you’re done here click Next.


The wizard will also upgrade any Execute Package Tasks that it detects.  The task had a bit of an overhaul and now when using the project deployment model you now reference packages that are part of the project instead of a file or server that contains the package.  The old way of running packages is still available but it’s now referred to as an external reference as opposed to the new project reference.  The defaults here are generally acceptable but you should review them then click Next.


Next you will be asked to select configurations to be converted.  Remember configurations will be replaced with project parameters.  If a configuration is not found for some reason (maybe the file connection couldn’t be made) you can also add it manually here.  When ready click Next.


To fully replace the configurations the wizard will create parameters that will store the same information the the configuration did.  Ideally if these configured values are the same across multiple packages you would choose to scope these as project parameter instead of package parameters, which is the default.  Parameters scoped at the package level are only available within that package.  Leaving theses all as project parameters will result in only 2 parameters created even though it appears from my screenshot that I will have 10 parameters because several of these are duplicated.  Click Next.


The parameters  that you just created can now be configured to have new values if you choose.  In my case I have connection strings in my value and I should verify that the provider used is appropriate for the version of SQL Server these packages will run on.  Click Next.


You can review the steps that are about to occur if you would like and click Convert when ready.


Once the conversion is complete you should see a screen similar to below.  You will notice it also prompt you to save as soon as you close the wizard.


A couple things to note about the conversion.  You Solution Explorer will appear different.  Notice Data Sources are no longer here, but you do have a Connection managers folder.  You’ll find Connection Managers to be a lot more useful and I will detail those in a later blog.  You will also see a section called Project.params.  These are your project parameters and can be defined by double-clicking on the object in the Solution Explorer.


The parameters are basically variables that can be defined across the entire project.  Again are what the wizard used to replace any configurations you used previously.  Below you will see what the definition of these project parameter store and then to assign them to configure an object in the package you will use configurations.  Luckily the wizard automatically did that for any configurations used previously.


So when you look at any connections or other objects that used configurations you will now find an expression that references the project parameter.  Notice below in SSIS 2012 it’s much easier to find connections that have expressions on them because they’re labeled with a little fx icon next to them.


When looking at the expressions on these connection you will see it does indeed reference the project parameter.


The last thing to point out that the wizard did for us is a change to Execute Package Tasks that I had.  The change is simple enough but it’s a new concept to 2012.  Basically instead of referring to a package that lives in the files system or SQL Server you can now reference package within the project.  This is called a Project Reference and the change is done automatically for you using the wizard.


I hope you found this post helpful as you walk through through converting your SSIS packages to use the new project deployment wizard.  In my next post I’ll walk you through deploying these package to the Integration Services Catalog.

Upgrading Packages to SSIS 2012

Many of you may now or in the future have the need to upgrade your SSIS packages to the new SQL Server 2012 SSIS.  Luckily, upgrading from SSIS 2005 or 2008 isn’t nearly as painful as you may have experienced upgrading old DTS (SQL Server 2000) packages.  If you’re considering the upgrade to 2012 here are some of the new features you have to look forward to:

  • Usability fixes in the development environment (Resolve References, Undo, Zoom, etc…)
  • New/Changed Tasks and Transforms (CDC Control Task, Execute Package Tasks, DQS Cleansing, etc..)
  • Project Deployment Model and Integration Catalog
  • New native Logging and Execution Reports

There is far more than I could list here but you can do a search for “What’s new in SSIS 2012” just as easily as I can Smile.

The upgrade process from 2005 or 2008 to SSIS 2012 is actually very straight forward.  There are a few things to note that are not upgraded automatically through the native upgrade steps.  The following are not upgraded and may need extra attention:

  • Deployment model stays as a Package Deployment unless you do another conversion tool.
  • Configurations are not upgrades.  For example, a configuration file may reference an old driver that is not longer used in 2012 so you would have to manually upgrade these.
  • Execute Package changes are not upgraded

To get started open your old SSIS solution in SQL Server Data Tools (new name for BIDS), which will automatically launch a conversion wizard.  This conversion wizard actually does two conversions.  The first conversion is more of a Visual Studio upgrade and the second specific to SSIS.  The screenshot below shows the start of the VS upgrade, click Next past the welcome screen.


The Visual Studio conversion will ask if you would like to back up the original files.  I generally go ahead and create a backup just incase, then click Next.


Prior to starting the SSIS Conversion tool you’ll get a warning to ensure the SSIS project is coming from a trusted source so you don’t risk a security threat.  Keep in mind the Visual Studio wizard stays open in the background.


Click Next past the welcome screen of the SSIS upgrade wizard.


Select the packages you wish to upgrade and ensure to provide any package passwords then click Next.  These passwords came from the ProtectionLevel setting of the original packages.


There are few settings you can manipulate prior the the upgrade that are pretty straight forward.  Shown below are the default settings and I will mention that even though connection string provider names are upgraded it will not upgrade them in configurations.  So if a configuration overrides a connection it will still have the old provider.  Click Next.


Review the actions that will be taken by the wizard then click Finish.


A successful conversion should look similar to the below image.  Hit Close, which will end the SSIS upgrade wizard and return you back to the final step of the Visual Studio upgrade.


You are returned to the Visual Studio upgrade where you simply need to hit Close to complete the upgrade.


Your packages will now be upgrade and should run without problem.  As mentioned before you may still want to upgrade things like the package deployment model, configurations, and execute package tasks.

SSIS – Inferred Member Insert in Fact Table Load

Recently while working on a different solution for loading inferred member solution in our fact table load I ran into this article on SQL Server Central article.  This article explains very well what an inferred member is.
This is a great article and I started using it but found the need to have some modifications.  The client I was working with rewrote the script component to fit these needs so with his permission I thought I would share it with you!  To make sure I give the proper credit thanks for the great script David Purdy!
The package below demonstrates a typical fact table load that performs lookups to join back to each dimension to return back to the proper surrogate key.  When that lookup does not return a key by default the package would fail but we are ignoring those nonmatches so that the next transform will pick them up.
In this solution the script component following the Lookup does a proper inferred member insert back into the dimension and returns the new key immediately after it is created.
The description property is used to centralize some of the logic for the insert and select required.  Here is the what is in the description:
insert into dbo.DimCustomer(CustomerAlternateKey,AuditIsInferred) Values(?,1);select CustomerKey from dbo.DimCustomer where CustomerAlternateKey = ?
This helps handle the insert into the dimension and returns the new surrogate key.  Notice that my example has a column called AuditIsInferred to identify if a record is inferred or not.  The example below shows a dimension with a single business key column but it you had multiples here is a template to follow:
insert into schemaname.tablename(businesskeycolumn1,businesskeycolumn2…,InferredColumn) Values(?,?…,1);select surrogatekey from schemaname.tablename where businesskeycolumn1 = ? and businesskeycolumn2 = ?…
You will need to ensure all columns are checked in the Input Columns page and then make your surrogate key readwrite because you will need to write and return back a value for this column.
You must also point to the correct connection for the dimension on the connection managers page.
Back on the Script page hit Edit Script and paste in the code below.  I’ve commented out instructions on how to adapt this code for your use.
' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports System.Data.OleDb
Imports System.Collections
Imports System.Reflection
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain

    Inherits UserComponent

    'If more than one natural key is used uncomment another line below
    Private parmNatKeyIns1, parmNatKeySel1 As OleDbParameter
    'Private parmNatKeyIns2, parmNatKeySel2 As OleDbParameter
    'Private parmNatKeyIns3,parmNatKeySel3 As OleDbParameter
    'Private parmNatKeyIns4,parmNatKeySel4 As OleDbParameter

    Private objInsCommand, objSelCommand As OleDbCommand
    Private inputBuffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer
    Public htCache As New Generic.SortedDictionary(Of String, Integer)

    Private CacheHits As Integer = 0
    Private InferredMembers As Integer = 0
    Private ForcedLookups As Integer = 0
    Private NullNKs As Integer = 0

    Public Overrides Sub PreExecute()
        'This Sub initializes the SQL Connection and Stored Procedure

        objInsCommand = New OleDbCommand
        objSelCommand = New OleDbCommand

        'Define the command object
        With objInsCommand

            .CommandText = Me.ComponentMetaData.Description.Split(New Char() {";"c})(0) + ";select scope_identity()"
            .CommandType = CommandType.Text
            .Connection = New OleDbConnection(Connections.Connection.ConnectionString)

            'Change to the proper data type for the natural key.
            'If there is more than one natural key uncomment as many lines as necessary
            parmNatKeyIns1 = .Parameters.Add("@NK1", OleDbType.VarWChar, 15)
            'parmNatKeyIns2 = .Parameters.Add("@NK2", OleDbType.VarWChar, 3)
            'parmNatKeyIns3 = .Parameters.Add("@NK3", OleDbType.VarChar, 10)
            'parmNatKeyIns4 = .Parameters.Add("@NK4", OleDbType.VarChar, 10)


        End With

        'Define the select command object
        With objSelCommand

            .CommandText = Me.ComponentMetaData.Description.Split(New Char() {";"c})(1)
            .CommandType = CommandType.Text
            .Connection = New OleDbConnection(Connections.Connection.ConnectionString)

            'If more than one natural key is used uncomment another line below
            parmNatKeySel1 = .Parameters.Add(parmNatKeyIns1.ParameterName, parmNatKeyIns1.OleDbType, parmNatKeyIns1.Size)
            'parmNatKeySel2 = .Parameters.Add(parmNatKeyIns2.ParameterName, parmNatKeyIns2.DbType, parmNatKeyIns2.Size)
            'parmNatKeySel3 = .Parameters.Add(parmNatKeyIns3.ParameterName, parmNatKeyIns3.DbType, parmNatKeyIns3.Size)
            'parmNatKeySel4 = .Parameters.Add(parmNatKeyIns4.ParameterName, parmNatKeyIns4.DbType, parmNatKeyIns4.Size)

        End With
    End Sub

    Public Overrides Sub PostExecute()

        'Finalize expensive objects
        htCache = Nothing

        Dim sMessage As String = Me.ComponentMetaData.Name & " (Inferred Members = " + InferredMembers.ToString + ";Cache Hits = " + CacheHits.ToString + ";Forced Lookups = " + ForcedLookups.ToString + "; Null NKs = " & NullNKs.ToString & ")"
        Me.ComponentMetaData.FireInformation(0, Me.ComponentMetaData.Name, sMessage, "", 0, False)

    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        'Change below to use the surrogate key column used for this lookup
        Dim SK_IsNull As Boolean = Row.CustomerKey_IsNull
        'Change below to use the natural key column used for this lookup.
        'If multiple natural keys were used uncomment before the 'Or' and add second column
        Dim NK_IsNull As Boolean = Row.CustomerAlternateKey_IsNull 'Or Row.xxx_IsNull 'Or Row.yyy_IsNull Or Row.zzz_IsNull
        'Change below to use the natural key column used for this lookup.
        'If multiple natural keys were used uncomment before the '+' and add second column
        Dim NK_CombinedKey As String = Row.CustomerAlternateKey.ToString '+ '+ Row.yyy.ToString + Row.zzz.ToString
        Dim SurrogateKey As Integer

        If NK_IsNull Then

            'Change below to use the surrogate key column used for this lookup
            Row.CustomerKey = -1
            NullNKs += 1

        ElseIf SK_IsNull Then

            If Not htCache.TryGetValue(NK_CombinedKey, SurrogateKey) Then

                'Change below to use the natural key column used for this lookup.
                'If multiple natural keys were used uncomment the next line add second column
                parmNatKeyIns1.Value = Row.CustomerAlternateKey : parmNatKeySel1.Value = parmNatKeyIns1.Value
                'parmNatKeyIns2.Value = : parmNatKeySel2.Value = parmNatKeyIns2.Value
                'parmNatKeyIns3.Value = Row.yyy : parmNatKeySel3.Value = parmNatKeyIns3.Value
                'parmNatKeyIns4.Value = Row.zzz : parmNatKeySel4.Value = parmNatKeyIns4.Value

                    SurrogateKey = CInt(objInsCommand.ExecuteScalar())
                    InferredMembers += 1
                Catch ex As Exception
                    SurrogateKey = CInt(objSelCommand.ExecuteScalar())
                    ForcedLookups += 1
                End Try
                htCache.Add(NK_CombinedKey, SurrogateKey)
                CacheHits += 1
            End If

            'Change below to use the surrogate key column used for this lookup
            Row.CustomerKey = SurrogateKey

        End If

    End Sub

End Class
Once this is done connect it back into your data flow stream and you should be prepared to handle inferred records now.  This process will need to be done for each lookup that you wish to allow inferred records in your dimensions.  Feel free to send me any questions or comments on how to improve it.  I’m definitely looking for ways to make it more dynamic or even a C# version of this!