SQL Server and MS BI With a Geek Touch

Bulk insert task is used to copy large amount of data into SQL Server tables from text files. For example, imagine a data analyst in your organization provides a feed from a mainframe system to you in the form of a text file and you need to import this into a SQL server table. The easiest way to accomplish this is in SSIS package is through the bulk insert task.

Configuring Bulk Insert Task

Drag the bulk insert task from the toolbox into the control flow window.

Read more…

What is For Loop Container?

For loop defines a repeating control flow. It has similar behavior as the “FOR” loop available in programming languages.  The For Loop container uses 3 expressions as definition

1. Initialization expression:  it assigns value to the loop counters. This expression is optional.
2. Evaluation expression : It contains the expression to test whether the loop should continue executing  or exit.
3. An optional iteration expression that increments or decrements the loop counter.

The expressions used must be valid SSIS expression. A variable is generally used in the expression.

Using and Configuring For Loop Container

(Task in hand: print counter variable value for each iteration times.)

Drag a for loop container into the SSIS Control flow window from the Toolbox. Double click on the for loop container to view the “For Loop Editor”. As mentioned earlier it contains 3 expressions. Now create a variable by name “TestVariable” of type “int32”.

Use this variable as counter in the container using expressions as shown below.

Read more…

In this blog, I will walk you through the creation of excel files ( xls / Excel 2003)  dynamically through SSIS.

Scenario: every day one of my process runs to pull data from SQL server table to excel file. I need to use unique file everyday with name as “taxonomy_<date>.xls” , for example on 14^th of January 2011 the file name should be “Taxonomy_01142011.xls” with the excel sheet name “TaxonomyValues”.

It is pretty simple to create this file dynamically. At first we need to set up an excel connection manager pointing to the file. The connection manager needs to be dynamically configured to point to the correct file everyday, in our case “Taxonomy_01142011.xls” on 14^th Jan. To do this,

1. go to properties window of the excel connection manager
2. click on expression and the browse ( … symbol) and choose “excel File Path” property. ( please refer the pictures below)
3. Copy and paste the expression given below or develop similar expression. Click on evaluate expression and it will display the file path as evaluated value.

“C:\\Taxonomy_”+ (MONTH( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) MONTH( GETUTCDATE()  ) DT_WSTR,2)MONTH( GETUTCDATE()  ))

+(DAY( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) DAY( GETUTCDATE()  ) DT_WSTR,2)DAY( GETUTCDATE()  ))

+(DT_WSTR,4)YEAR( GETUTCDATE()  ) +”.xls”

Also you need to use the settings as below for connection manager ( for .xls files)

Read more…

In 64 bit operating systems, SSIS transformations ( especially excel) and tasks throws errors that could be annoying. You will often come across errors as shown below.

SSIS package “Package.dtsx” starting.

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

Error: 0xC00F9304 at Package, Connection manager “Excel Connection Manager“: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. Read more…

Today, I came across a requirement, where the source excel sheet had data starting from row number 6 and column B. This was due to formating in the excel, which was also used as a report by many stake holders.

Although at first it might seem like there are no options in excel source to skip certain rows from the beginning of the sheet, it is not true. Fortunately, excel source provides the feature to select only required records from any sheet.  the OpenRowset property on the Excel Datasource component  allows you to specify the range to be considered by the datasource.

Read more…

This is a very common question asked to me by many developers. As a fresh developer few years ago, even I used to wonder about this service. The SSIS packages used to work perfectly with and with out the services being turned on (Start).

So what exactly does the SSIS service do?  The answer is simple.  The SSIS Service  manages the Integration Services server and its interface in SQL Server Management Studio. It also provides caching of SSIS components. The SSIS services is required to import/export packages, view running packages, and view stored packages in the “package store”.

SSIS services has nothing to do with the execution of packages. The package execution is controlled by package execution utility(dtexec). The services does not have any impact on

• SSIS package development
• SSIS Package  execution
• SSIS Package performance 
• Checkpoint restarts
• Logging fuctionalities.

sometimes, you may need to copy some/ all objects from one database to another.  well this can  easily done in SSIS using the task “Transfer SQL Server Objects Task“. The usage of this task is very simple. Drag the task from toolbar for control flow. Double click on the task to start using it.

The editor for the task opens. First thing that you need to do is to create the SMO connection managers for both source and destination SQL servers. This can be done by clicking on the drop down provided for source and destination server tabs as shown below.

Then select the source and destination database. You can then select the objects that need to be migrated. A detail lists of objects are given in the editor as shown below. Assume, you need to copy all the tables and two views.  To achive this, set the property “copy all table = true”.

You  also need to select few views hence, click on “collectionlist/browse button” in  “viewlist”. A pop up appears with the list of views, check the views that need to be copied.

click ok and then execute the task to copy all selected objects to destination database. You can also copy the data by setting the property “Copy Data” to true. To copy all objects in the database, you need not set each property, Instead you can set “CopyAllObjects” to true.

Adding audit information to your dataset such as user/ machine that modified  the data, package that inserted data into your table etc,. might be required while performing an ETL task. In SSIS you can achieve this by using the transformation “AUDIT” available in DFTs. Inside the DFT you can add “audit transformation” after any other transformation as shown.

You need to select the “Audit type” of your choice like package name, package ID, user name etc and assign appropriate column name to each of these  audit type .

Click OK. All the records in your dataset will now have the selected audit types as the extra columns.

In the above image you can see two new columns “executed_package_id” and “Execution_instanceID” added  to the dataset. You can use these columns in the following transformations as well as push it into a DB table. Hence any thing that gets modified in your table through packages can be tracked.

I am starting this post with an assumption that you know to configure logging in SSIS 2005/2008 for SQL Server provider.

There are few differences in the way the information is logged into the table when using SSIS 2008 compared to SSIS 2005.

In 2005 the events are logged into the table by name “sysdtslog90″ and in 2008 the events are logged into “sysssislog“. By default in 2005 the table is created under the user tables folder, however in 2008 it is created under system tables. You can how ever create a table before the execution of packages  in user tables folder , this will restrict the table from getting created under system tables folder.

A stored proc is created along with the table. In ssis 2005 it is named as “sp_dts_addlogentry” . In 2008 it is named as “sp_ssis_addlogentry” and is created under “system SPs ” by default.

You can add additional columns to the tables used for logging and update the values in these columns. This is usually done to customize the logging.

Developers using SSIS 2005, were never satisfied with the lookup component . The lookup component in ssis 2005 didn’t support case insensitive comparison.  This always resulted in missing some records in the output or even used to cause the failure of the lookup trasformation.

However in SSIS 2008, we now can do a case insensitive comparison.  This is done by setting the “Cache Mode” property of the lookup to nocache ( refer pic ). Setting this property results in executing the comparison on sql server directly, this would result in case insensitve comparision( provided the DB uses case-insensitive collation, which is the default setting of Sql Server). Setting the “CacheMode” to full cache will result in loding the query result to cache & then performing a case sensitive comparison.

After setting the “CacheMode”, you need to specify the OLEDB Connection manager and the Query/table name .

Rest of the execution proceeds as it was in SSIS 2005.

An alternate way of performing case insensitive comparison is by using a query at both source and the lookup using the functions like upper() and lower() of transact SQL. Convert the fields to compare ,into UPPERCASE and then  run the lookup.

The third way of doing it is by using “Character Map” Transformation , which will allow you to change the case of a string.

In the 2nd & 3rd case , one need not worry about collation .