Fixing SSIS Deployment Issues

SQL Server Integration Service (SSIS) is a platform for building enterprise level data integration and transformations solutions in Microsoft SQL Server. For example updating a SQL Database from an external source such as XML Source, Excel source and there are many more.

There are enough articles available on internet regarding SSIS package development and deployment, but hard to find exact solution regarding SSIS issues.

The purpose of this article is to provide solutions regarding few SSIS deployment issues in SQL Server 2005.

Issue 1:-

Error Message:-

The SQL Job fails with following Error:-
“The command line parameters are invalid. The step failed.”……

Description:-

This error occurred due to SQL Server 2005 Agent Bug. When you deployed your DTSX package as a SQL Server job and define DataSources [Ex:- DB Connection, Excel Connection] in SQL Job property window, it will automatically generate a Command Line string. [You can view it by Job Properties: - Edit "Step":- "General":- Select "Command line" Window]
When you try to run the SQL Job, the generated Command Line string cannot be recognized due to Command line String validation failure.

Solutions:-

1) The slashes should be backslashes and the quotes need to be escaped (i.e. \").

2) Use a Config file for your DTSX package. You need to parameterize all DataSource Connections and define them in a Config file. So you don’t need to edit “DataSources“under SQL Job Properties. This will avoid generating unnecessary Command Line String under SQL Job. [Recommended]

This Bug has been fixed in MS SQL Server 2008.


Issue 2 (In 64 Bit Machine):-

Error Message:-

The SQL Job fails with following Error:-
- Pre-execute (Error)
Messages
Error 0xc0202009: {F1B3B35C-FAE3-48F6-A169-4E4D8D99F9B6}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "DestinationConnectionExcel" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)……………………………………………

Description:-

The above error occurred due to limitation of 64 Bit environment since some .NET Framework data providers and some native OLE DB providers may not be available in 64-bit versions. When you try to execute DTSX Package as “SQL Server Integration Services Package” type in SQL Job window, by default it will try to use DTExec.exe available in 64 Bit environment. So it gets failed if your DTSX package contains any OLE DB/etc connections which are not available in 64 Bit environment.

Solution:-

Change SSIS package the setting to False from
Visual Studio Solution->Properties->Debug->64Bit .

Additionally you need to run you’re your DTSX package by using the 32 Bit DTExec.exe executable by default available in "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" path.

In order run it as a SQL Job you need to schedule it as an Operating System Command by changing the SQL Job Properties-> Steps -> Edit Etep-> Change the Type as “Operation System (CmdExec) ”

And the Command should be similar to following sample Command:-

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "C:\ImportClientInfoPackage.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /CONFIGFILE "C:\ImportClientInfo.dtsConfig"