Running an SSIS Package using dtexec

问题:

I'm running an SSIS package using dtexec. The package runs fine in BIDS on my system. When I create an SQL server agent job to run the package on a schedule. The package running step is scheduled as a T-SQL task, not an SSIS package one). The job reports no error, but it's not even creating the output excel file @ my desired destination on the server.

Furthermore, when I separately run the command in command shell, it's returning me the errors shown below. Intermittently, it'll also return errors on the FileSystem Task that I use to copy files, saying that either the source or destination doesn't exist!! When the same variable values work for me in BIDS, why is the SQL job failing?

Started:  7:33:27 PM
Error: 2012-10-26 19:33:27.60
   Code: 0xC0016016
   Source:
   Description: Failed to decrypt protected XML node "DTS:Password" with error 0
x8009000B "Key not valid for use in specified state.". You may not be authorized
to access this information. This error occurs when there is a cryptographic err
or. Verify that the correct key is available.
End Error
Error: 2012-10-26 19:33:27.78
   Code: 0xC00F9304
   Source: GICSReport Connection manager "Excel Connection Manager"
   Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Conne
ction Manager is not supported in the 64-bit version of SSIS, as no OLE DB provi
der is available.
End Error
Error: 2012-10-26 19:33:27.78
   Code: 0xC020801C
   Source: Data Flow Task Excel Destination [22]
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
ER.  The AcquireConnection method call to the connection manager "Excel Connecti
on Manager" failed with error code 0xC00F9304.  There may be error messages post
ed before this with more information on why the AcquireConnection method call fa
iled.
End Error
Error: 2012-10-26 19:33:27.78
   Code: 0xC0047017
   Source: Data Flow Task SSIS.Pipeline
   Description: component "Excel Destination" (22) failed validation and returne
d error code 0xC020801C.
End Error
Error: 2012-10-26 19:33:27.78
   Code: 0xC004700C
   Source: Data Flow Task SSIS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2012-10-26 19:33:27.79
   Code: 0xC0024107
   Source: Data Flow Task
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  7:33:27 PM
Finished: 7:33:27 PM
Elapsed:  0.343 seconds

Pls help! :) .... Should i be adding all the variables, connection managers, and everything to my config file? currently i've only added some ppty values of the variables and connection managers, but no combo seems to work effectively.

回答1:

The first error I would address is "The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available."

The out of the box Excel drivers only exist in the 32 bit address space. BIDS/SSDT is a 32 bit application so Excel source and destinations work just fine. However, when you run them from the commandline/SQL Agent, then you need to explicitly use the 32 bit version of the DTEXEC program.

Step 1, will be to ensure you can run the package from the command line on the server the agent executes on as yourself. Assuming your SQL Server is installed in the customary location, you probably have one of the following DTEXEC.exe available to you

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe
c:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\DTExec.exe
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTExec.exe

You will want to use the (x86) version. Future readers, if you happen to be on a 32 version of Windows (Windows 2003, maybe), the first 3 will be the only options available to you. As Vivek's error message has indicated, he is executing an SSIS package in 64 bit mode.

dtexec provides a command-line switch /X86 to allow you to seamlessly use the same executable for both 32 and 64 bit operations. LIES! The documentation does call that out but who reads documentation?

This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt.

So, you will need to run your package by providing the explicit path

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe /file C:\folder\GICSReport.dtsx

I see "Failed to decrypt protected XML node" in your output and you also state you are using configuration files so you can most likely change your PackageProtectionLevel from the default EncryptSensitiveWithUserKey to DontSaveSensitive. That feature exists to prevent accidental exposure of sensitive data (passwords) but since you are already handling that with config files, that should not be an issue. ... That might actually be an error from one of the other package protection levels now that I think about it.

At any rate, try running from the 32 bit executable first. If that doesn't work try changing the package protection level as indicated. If either of those make the package run as expected, then attempt to run the same command from the SQL Agent.

If it all works, mark this as the answer. If not, please update the ticket with the current error being generated and we'll ask for more information.

回答2:

Here is the solution if you are trying to run a SSIS package with dtexec from transact SQL that contains an excel import.

1. Create a batch file that contains the following code.

   cd C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\ DTEXEC.exe /DE "password" /F "C:\mySSISfolder\package.dtsx"

2. Create a shortcut pointing at your batch file, set the properties\advanced on the shortcut to run as an administrator.

3. from your transact sql procedure run the following command

   exec xp_cmdshell 'C:\myfolder\runssis_sc.lnk'