Archive for the ‘SSIS 2008’ Category

Creating Excel Files (.xls) dynamically from SSIS

April 25, 2011 1 comment

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 14th 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 14th 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.

Continue reading..


SSIS Error : Resolve transformation errors in 64-bit version of SSIS in debug mode

October 8, 2010 Leave a comment

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.

Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component “Excel Source” (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package “Package.dtsx” finished: Failure.

In Debug mode it is really easy to overcome this by doing a simple change in settings.

Continue Reading …

Excel and SSIS 64-bit Connections

June 18, 2010 Leave a comment

I stumbled upon a new error yesterday. I developed a simple and straight forward SSIS package in my machine. It was meant to copy data from an Excel 2007 sheet to a table. It worked fine in my machine, but as soon as I moved it to  the server the package started failing. I had never come across this particular error before.

Source: ImportClientXLS Connection manager “Excel Connection Manager”
Description: 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.

then I realised I had a 32-bit machine and the server was 64-bit. And the dtexec used in 32 b was different from 64 b machines . The resolution to this error is simple. I just changed the command string.

My initial command string was

dtexec /f “D:\MigrationPackages\B2S\EPGSE.dtsx”

The  above string point to 64 b programfiles folder in 64 b machines and hence I changed it to point to 32 b programfiles folder.

“C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe” /f “D:\MigrationPackages\B2S\EPGSE.dtsx” /X86

and it worked perfectly fine and my day saved.;)

The post is also available at our website:

FAQ: what is the purpose of turning on the service”SQL Server Integration Services”?

February 11, 2010 Leave a comment

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.
Categories: Logging, SQL Server, SSIS, SSIS 2008

Copy all objects from one database to another in SSIS using “Transfer SQL Server Objects Task”

August 18, 2009 4 comments

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 Datato 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 you dataset using SSIS.

August 13, 2009 2 comments

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.

Logging using SQL provider in SSIS 2008 and SSIS 2005

August 4, 2009 2 comments

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.