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

by

Making some assumptions here, but I’m going to assume that this is a 32 vs 64 bit issue. To verify, try these two commands from a command prompt (Windows Key, R, cmd.exe or Start, Run, cmd.exe)

"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx
"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx

The first will run your package in 32 bit mode whilst the second runs it in 64 bit mode. This is going to matter as your drivers and any DSNs you’ve created are going to only be visible in the 32/64 bit world.

Fixing SSDT

Once you’ve identified which one you need, probably 32 bit version, you’d need to ensure your project is using the appropriate run-time. Right click on your project and select Properties and then navigate to the Debugging tab under the Configuration Properties.

Debugging Tab, Run64BitRuntime

After inverting the Run64BitRuntime value, I assume your package will work from within SSDT.

Fixing SQL Agent

You will need to edit the existing SQL Agent job to change the bittedness of the job step. This will be under the Configuration tab and then under the Advanced tab. Check/Uncheck the 32-bit runtime.

agent 32bit tab

Lies and deception

Observant folks may see that the dtexec offers a /X86 option. Don’t believe it. The only way to get the correct bit-ness is to explicitly call the correct dtexec.exe The documentation even says as much but nobody 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.

More Related Contents:

  1. How do I pass value to a stored procedure parameter in OLE DB Source component?
  2. SSIS reading LF as terminator when its set as CRLF
  3. Dynamically assign filename to excel connection string
  4. Importing excel files having variable headers
  5. Import Excel spreadsheet columns into SQL Server database
  6. convert Excel Date Serial Number to Regular Date
  7. SSIS Excel Import Forcing Incorrect Column Type
  8. How to load an XML file into a database using an SSIS package?
  9. Automate Version number Retrieval from .Dtsx files
  10. Query a database based on result of query from another database
  11. Running an SSIS Package using dtexec
  12. SSIS Source Format Implicit Conversion for Datetime
  13. How to Map Input and Output Columns dynamically in SSIS?
  14. Reverse engineering SSIS package using C#
  15. Temporarily disable all foreign key constraints
  16. Convert from DateTime to INT
  17. What are the differences between Merge Join and Lookup transformations in SSIS?
  18. How to execute SSIS package when a file is arrived at folder
  19. To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher
  20. How to avoid SSIS FTP task from failing when there are no files to download?
  21. Updating image in VB and SQL Server not working [closed]
  22. Compare the values of column in SQL Server
  23. T-SQL stored procedure that accepts multiple Id values
  24. SQL Update with row_number()
  25. Recursive sum in tree structure
  26. Compute MD5 hash of a UTF8 string
  27. Sort Bullets in Database
  28. How do I remove non-breaking spaces from a column in SQL server?
  29. Why is casting from float to varchar being rounded in SQL Server?
  30. How does SQL Server decide format for implicit datetime conversion?

Leave a Comment