Archive

Archive for the ‘DTC’ 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: http://geekepisodes.com/2010/06/12/excel-and-ssis-64-bit-connections/

Configure DTC on Windows Server 2003/ Windows XP

June 11, 2009 Leave a comment

For the transactions to run successfully across  servers , we need to enable DTC ( distributed transaction coordinator). For example, while running a ssis package with transactions running across servers we might encounter an error related to DTC. To avoid these errors we have to enable DTC on both the machines.

This post discusses about enabling DTC.

We can do this by Add/Remove Windows Components in Add/Remove Programs under the control panel.  When the Windows Components Wizard opens, select Application Server and click on details.  Select “Enable network DTC access” check box.

untitled

After that, finish running through the wizard and network DTC will be installed. We still need to configure DTC.  To do this, open the Component Services MMC from the Administrative Tools menu.

untitled1

Right click on “My computer” and select properties, then the MSDTC tab. In MSDTC tab, select the Security configuration button. check/Uncheck  the properties as required. click on OK button.

untitled2

Restart the DTC service on your machine for the changes to take effect. If you still encounter errors while running packages/queries , you may have to restart your machine once.

Categories: DTC, DTC, SQL Server, SSIS