SSIS Configuration File (.dtsconfig) Behavior (relative path issue)

Let me start by saying that I prefer SQL Server configurations over all other configuration types in SSIS for the majority of configurable values in my SSIS projects because they are more secure, easier to decipher, and fit well into database deployment methods and tools that I use.

Unfortunately, not all configurations can be of the SQL Server configuration type as we need an initial configuration that stores the connection string for the database that the SQL Server configurations live in.

This is where it becomes necessary to use an XML configuration file type of SSIS configuration. One of the problems with using an XML config file is that SSIS does not understand relative paths. This means that all environments must store the config file in the exact same directory structure, otherwise the file will not be found (except in some circumstances which I'll explain later in this post).

To get around this problem we can use something called an Indirect XML configuration file configuration type to store this single configuration containing the connection string for the SSIS configuration database. See the first configuration in the screen shot below.

The beauty of this configuration type is that it allows you to use an XML config file in such a way that its location is not environment specific. It does this by getting the location of the XML config file from a Windows Environment Variable. Unfortunately this must be created in environments where SSIS packages are being developed (where BIDS is installed) and in runtime environments where the config file does not live in the same directory as the SSIS packages being executed. While this does require added effort, it only has to be done once per environment. Below is a screen shot of the creation of a sample Environment Variable.

If you store your XML configuration file (.dtsconfig) in the same directory as the rest of your SSIS packages (.dtsx) then it is not necessary to create this environment variable on machines where your packages will be executed by the SSIS runtime and/or dtexec/dtexecui. SSIS will attempt to find the Environment Variable and when it cannot, it will then look in the root path of the SSIS packages and use the config file found there.

However, if your design requires that you store your XML config file in a location other than your package location you must create the Environment Variable in all locations.

At the risk of repeating myself, BIDS will not be able to find your config file unless you hard-code it's absolute path in a regular XML configuration file configuration (non-Indirect XML configuration) or create an Indirect XML configuration using an Environment Variable. This is true even if the config file lives in the same directory as your packages. This works fine when there is only one developer and that developer uses only machine to develop. Once you have multiple development team members with potentially different directory structures, you can avoid many headaches by making your XML config file location environment agnostic. Don't fear the Environment Variable!

OLE DB Destination or SQL Server Destination

SSIS offers a number of Data Flow destination components. When working with SQL Server databases as a destination there are two obvious choices.

OLE DB Destination

SQL Server Destination

Microsoft claims up to a 15% performance increase by using the SQL Server destination. Therefore it seems like a good choice. However, there is one rather large caveat. SSIS must be running on the same SQL Server as the destination database. It cannot write to a remote SQL Server.

That said, I have seen many solutions that have both SSIS and the destination database on the same server so it seems to make sense to use it in these situations. Personally, I choose not to. By using the SQL Server destination you're locking yourself into having to make a code change if you decide to split your SSIS and database instances across multiple servers. Having SSIS code dependent on a solution's hardware architecture is something I always try to avoid.

SQL Bulk Copy

In an earlier post I talked about a generic SSIS package for staging data using SQL Bulk Copy. I have been using it extensively without fail . . . until just recently.

I got a new set of files from a new business unit that we were bringing into our data warehouse solution. Of their 20 files 14 loaded without issue and the remaining 6 failed with an error similar to the one below.


System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type int of the specified target column.


I kept going back to the developers that created the extract process and telling them that they were doing something wrong with their encoding or delimiters but neither us nor them could actually find the issue. Until now.

Turns out the issue is related to SQL Bulk Copy, numeric columns, and null values. Apparently SQL Bulk Copy does not, by default, keep nulls. Instead, it converts them to empty strings. Well, as I'm sure you know, SQL Server doesn't like it when you try to load empty strings, or any strings for that matter, into a numerically typed column.

There are a couple of solutions.

1. Make sure to replace all nulls with zeros when created flat files with numeric data that will be consumed by BCP.
2. Use the KeepNulls option when executing BCP. In my SSIS solution I changed my SqlBulkCopy object creation to look like this . . .

Dim bc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.KeepNulls, bulkCopyTransaction)

Data Warehouse QA Technical Screen Questions

I was recently asked to perform a technical phone screen of a data warehouse QA candidate. The project manager wanted to ensure that this person knew enough SQL to be able to efficiently test our data warehouse contents (not end-to-end processing testing).

So that I didn't sound like a bumbling fool when I made the call I put together a list of questions. It's by no means exhaustive but I figured it could be helpful to some . . .

When was the last time you used SQL on a day to day basis (how often do they use it and how rusty might they be)?

How would you rate your SQL from 1-10 (sets expectations for how well they will answer questions below)?

What database platforms/versions have you used (Oracle, SQL Server, MS Acess, MySQL, etc.)?

1) What are the two keywords necessary for any SQL statement?

A1) SELECT

A2) FROM

2) How do you limit the results of a query based one or more criterion?

A1) WHERE clause

  What is a join?

A1) It Combines records from two or more tables.

4)      What types of joins are there and what do they do?

A1) INNER JOIN

A2) LEFT OUTER JOIN

A3) RIGHT OUTER JOIN

A4) CROSS JOIN

A5) FULL OUTER JOIN

5)      What is the syntax for joining two tables?

 A1) FROM tableA

INNER JOIN tableB ON tableA.column1 = tableB.column2

6)      How do you get a single instance of each value of a column in a table?

A1) DISTINCT

7)    

            How do you get the number of occurrences of each distinct value in a table?

A1) COUNT, GROUP BY

8)     

            How do you get the largest value of a column in a table?

A1) MAX

      

9) What is the difference between putting criteria in a WHERE clause or a JOIN clause?

A1) When it is an INNER JOIN it does not affect the results.      

A2) When it is an OUTER JOIN, putting the criteria in the WHERE clause effectively makes it an INNER JOIN which is not usually the intended behavior.

A3) When it is an OUTER JOIN, putting the criteria in the JOIN clause allows the criteria to be applied to only those rows that are outer-joined and does not create the INNER JOIN – effect.

A4) Depending on the database platform and version, the performance could be affected.

     

10)   What is the difference between a WHERE clause and a HAVING clause?

A1) The WHERE clause applies the criteria at the row level whereas the HAVING clause applies it at the aggregate level.

11)   What is a derived table/inline view?

A1) It is a query that is aliased as a table and used like any other table in a FROM clause.

12)   What is a nested subquery?

A1) It is a query that is nested within a SELECT or a WHERE clause.

13)   What is a correlated subquery and when are they useful?

A1) It is a query that is used within a parent query and refers to the parent query.

A2) A correlated subquery is evaluated once for each row processed by the parent statement.

A3) They are useful in providing a set of values to be used in a WHERE clause.

Our data warehouse solution consists of the following.

       1)      Flat files that provide all source data

       2)      A staging database that the flat files are loaded into

       3)      A data warehouse database that contains the transformed data based on a documented set of rules.

1)      If you could assume that the flat files were loaded to the staging database without any errors, how would you test that the data was correctly transformed from the staging database to the data warehouse? What tools, if any, would you need?

A1) Write queries against the staging database that follow the transformation rules. Then write queries to pull the related rows from the

A2) Some kind of a query execution tool is necessary. Toad, RapidSQL, SQL Server Management Studio, etc.

2)      How do you test large sets of data where there could be millions of rows?

A1) Narrow down the results based on specific criteria.

A2) Apply aggregate functions such as SUM to determine if the test query aggregates to the same value as the data warehouse query.

A3) Join the test query/tables to the data warehouse query/tables and apply a WHERE clause that filters on only those values that do not equal each other.

3)      What data would you provide to the development team when you found inconsistencies between the documented transformations and what was in the data warehouse?

A1) The transformation rule

A2) The test query

A3) The data warehouse query (if independent from above)

A4) Explanation of expected versus actual results

A One-Package, Generic SSIS Staging Process

Download script

I recently worked on a project that was flat file and staging intensive. By intensive I mean we had essentially 1.5 ETL developers and over 50 flat files to stage. We also had a very aggressive timeline. Two data marts with source data coming from 5 totally disparate and disconnected (hence the flat files) subsidiaries/source systems all over the world (think currency conversion, translation, master data management, etc.) and only three and a half months to get it done.

Was I going to create a package to stage each flat file? Ummm . . . no! So what was I to do? I was going to create a single, metadata driven package that would loop through a data set and populate my staging tables one by one. Below is a screenshot of all the components necessary to make this work. There is no Data Flow.

Before going into the details there are a few things to keep in mind.

1. This solution is based on text files. It can easily be tailored to read from relational sources and also non-relational sources with a little more tweaking.
2. What makes this design possible is a Script Task that alleviates the need for custom data flows for each extract file/table to staged.
3. The solution assumes that the staging tables can hold more than one day's/load's worth of data and therefore adds an ExtractFileID column to each staging table and to the source data as it is being loaded.
4. For the script task to work as-is it is necessary to create a staging table for each data source that will be staged. Each staging table must meet the following criteria:
• The staging table must contain column names that match the data source column names exactly.
• The staging table columns must have data types that are appropriate for the incoming data. If they are character data types they must be large enough that they will not truncate any incoming data.  

The metadata that drives the solution might be specific to your project so I won't go into it in this post. What you'll need to do is create the metadata tables necessary to create a data set that can by used by a Foreach Loop container that will loop through each file or table that needs to be staged.

However, you can test the Script Task by hard-coding a single file's metadata within the Script Task. That is exactly what I did to create the script. Once the script was working as expected I created the necessary metadata tables and data and passed the values into the script to make it more dynamic. Below is the method that does the bulk of the work (no pun intended). The entire script can be downloaded here.

Private Sub BulkCopyDelimitedFile(ByVal batchSize As Integer, _

            ByVal columnDelimiter As String, _

            ByVal extractFileID As Int32, _

            ByVal extractFileFullPath As String, _

            ByVal stageTableName As String)

        Dim SqlConnectionManager As Microsoft.SqlServer.Dts.Runtime.ConnectionManager

        'Set a connection manager object equal to the connection manager named "Stage"

        SqlConnectionManager = Dts.Connections("Stage")

        'Since the "Stage" connection manager is of type OLEDB we need to modify it to make it compatible with an ADO.NET connection

        Dim dbConn As SqlConnection = New SqlConnection(SqlConnectionManager.ConnectionString.Replace("Provider=SQLNCLI10.1;", ""))

        'Create a new StreamReader object and pass in the path of the file to be read in

        Dim sr As StreamReader = New StreamReader(extractFileFullPath)

        'Read in the first line of the file

        Dim line As String = sr.ReadLine()

        'Create an array of strings and fill it with each column within the extract file by using the Split function

        Dim strArray As String() = line.Split(columnDelimiter)

        'Create a DataTable object

        Dim dt As DataTable = New DataTable()

        'Create a DataRow object

        Dim row As DataRow

        'Open the database connection

        dbConn.Open()

        'Create a SQLTransaction object in which to execute the Bulk Copy process so that if it fails, all writes are rolled back

        Dim bulkCopyTransaction As SqlTransaction = dbConn.BeginTransaction()

        'Instantiate our SqlBulkCopy object and set appropriate properties

        Dim bc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.Default, bulkCopyTransaction)

        'Set the BulkCopy destination table name equal to the staging table name provided by the Foreach Loop Container

        bc.DestinationTableName = stageTableName

        'Set the BulkCopy batch size equal to the size contained in the metadata provided by the Foreach Loop Container

        bc.BatchSize = batchSize

        'For each column that was found in the extract file

        For Each columnName As String In strArray

            'Add a column in the data table

            dt.Columns.Add(New DataColumn(columnName))

            'Add a column mapping in the SqlBulkCopy object

            bc.ColumnMappings.Add(columnName, columnName)

        Next

        'Add the ExtractFileID column to the data table since it doesn't exist in the extract file and wasn't added in the previous For Each loop

        dt.Columns.Add(New DataColumn("ExtractFileID", System.Type.GetType("System.String")))

        'Add the ExtractFileID column mapping since it doesn't exist in the extract file and wasn't added in the previous For Each loop

        bc.ColumnMappings.Add("ExtractFileID", "ExtractFileID")

        'Move the the first row in the extract file after the header

        line = sr.ReadLine()

        Dim rowCount As Integer

        'Loop through all rows in the extract file

        While Not line = String.Empty
            rowCount += 1

            'Create a new data table row to store the extract file's data in

            row = dt.NewRow()

            'Add all column values to the row

            row.ItemArray = line.Split(columnDelimiter)

            'Add the ExtractFileId 

            row("ExtractFileID") = extractFileID

            'Add the newly created row to the data table

            dt.Rows.Add(row)

            'Move to the next row in the extract file

            line = sr.ReadLine()

        End While

        

        Try

            'Write the data table to the staging table

            bc.WriteToServer(dt)

            'If successful, commit the transaction

            bulkCopyTransaction.Commit()

        Catch ex As Exception

            'If an exception occurs, rollback the transaction

            bulkCopyTransaction.Rollback()

            'Set the Task result to Failure

            Dts.TaskResult = ScriptResults.Failure


            Throw ex

        End Try

        'Close the database connection

        dbConn.Close()

        'Close the BulkCopy object

        bc.Close()

        'Set the SSIS metadata variable equal to the number of rows loaded

        Dts.Variables("ExtractActualRowCount").Value = rowCount

    End Sub


Download script

SSIS Interview Questions

Below are some questions I recently asked while interviewing for an SSIS developer. Thought they might be helpful to others looking to hire. Answers to follow . . .

1. Have you used SSIS in a project/product type of environment as opposed to an operational/once-off type of an environment?
2. Have you worked with a hierachical/modular type of SSIS project with packages calling other packages? If so, did you pass values back and forth between the packages and if so, how?
3. Have you dealt with package versioning and if so, how?
4. Have you used any of the package configuration functionality within SSIS? If so, which types and for what types of values?
5. Have you used any kind of restart mechanisms in SSIS? If so, which one(s)?
6. Have you developed packages that could be migrated from one environment to another without any code changes (directory locations, database connections, etc.)? If so, how?
7. Which data flow transformations have you used?
8. Are you familiar with dimensional/star-schema modeling and concepts such as junk dimensions and slowly changing dimensions?
9. Have you used the Slowly Changing Dimension transformation? If so, what if any issues did you encounter while using it?
10. What kind of logging have you used with SSIS, if any?
11. Have you used package event handlers for any kind of processing logic?
12. What types of debugging tools/methods have you used with SSIS?
13. Have you used script components and if so, what language are you most comfortable with?
14. Where have you stored/deployed your production SSIS packages (file system or msdb)? What is your preference and why?

SSIS Error On OLE DB Command - Operand type clash: int is incompatible with date

Here's a good one - Operand type clash: int is incompatible with date.


It appears, although I have not fully confirmed, that SSIS does not play well with the new SQL Server DATE data type.


I ran into this issue when calling a stored procedure from an OLE DB Command transformation. The stored procedure had a parameter of data type DATE.


While not entirely satisfying, the quick fix for this is to change the parameter data type to DATETIME.


An issue was opened with Microsoft but they state they have not been able to reproduce the error.
http://connect.microsoft.com/SQLServer/feedback/details/628743/ssis-oledb-command-date-datatype-in-stored-procedure-sqlcommand-yields-operand-error