Running an SSIS Package using dtexec

by

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.

More Related Contents:

  1. Export from SQL Server to Excel
  2. SSIS Excel Import Forcing Incorrect Column Type
  3. How do I pass value to a stored procedure parameter in OLE DB Source component?
  4. How to load an XML file into a database using an SSIS package?
  5. Why does my ODBC connection fail when running an SSIS load in Visual Studio but not when running the same package using Execute Package Utility
  6. SSIS reading LF as terminator when its set as CRLF
  7. Dynamically assign filename to excel connection string
  8. Importing excel files having variable headers
  9. Temporarily disable all foreign key constraints
  10. Convert from DateTime to INT
  11. What are the differences between Merge Join and Lookup transformations in SSIS?
  12. How to execute SSIS package when a file is arrived at folder
  13. To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher
  14. How to avoid SSIS FTP task from failing when there are no files to download?
  15. Updating image in VB and SQL Server not working [closed]
  16. Compare the values of column in SQL Server
  17. T-SQL stored procedure that accepts multiple Id values
  18. When/Why to use Cascading in SQL Server?
  19. SQL Update with row_number()
  20. Recursive sum in tree structure
  21. Getting execute permission to xp_cmdshell
  22. Why the SQL Server ignore the empty space at the end automatically?
  23. Compute MD5 hash of a UTF8 string
  24. Sort Bullets in Database
  25. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  26. How do I remove non-breaking spaces from a column in SQL server?
  27. Why is casting from float to varchar being rounded in SQL Server?
  28. SQL SERVER PIVOT table with joins and dynamic columns
  29. In SQL Server, how can I find everywhere a column is referenced?
  30. Convert float into varchar in SQL Server without scientific notation

Leave a Comment