Archive

Archive for the ‘Logging’ 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..

.

Advertisements

SSIS – Skipping rows and columns in Excel

October 7, 2010 Leave a comment

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.

  By specifying a range, I was able to ignore all the titles and data spread all over the sheet . The configuration is simple and is as shown in the screenshot below.

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: http://geekepisodes.com/2010/06/12/excel-and-ssis-64-bit-connections/

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

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.

Begin

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 .

cols

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

Dataset

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.