Sql Server 2012 Job on 64 Bit Environment fails to find 32 Bit ODBC connection when executing SSIS 32 Bit package .

Question

Hi All,

My SSIS 2012 (64bit, Windows 64 bit ) package runs fine from BIDS . It is a SSIS Import / Export package that pics data from old Mainframes Server into Sql Server . I am using a 32 Bit ODBC to connect to Mainframes .

Howeever when i try to run the same package as a Sql Server Agent JOB it fails with message :

Data Flow Task 1:Error: ADO NET Source has failed to acquire the connection {010532E9-BC30-42BD-8A7E-D9E2E7412989} with the following error message: "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

I have read the various blogs on this and have already to deploy the package after setting "Run64BitRunTime" = "False".

I changed the Sql Server Agen Job Advance Setting Property to "32-bit machine" . Job Step Type is "Sql Server Integration Services Package" It runs as "Sql Server Agent Account"

Still no success .

Any pointers ?

Thanks

---

shuchi

Answer 1

You need to create a Proxy account in SQL Server Agent Job. Then you need to run this package using this Proxy account instead of what you are using.

How to Create a Proxy Account

---

Please use Mark as Answer; if my reply solved your problem. Use Vote As Helpful if a post was useful. |WWW.FACEBOOK.COM/SONNIE.HIM | |[phone] |

Answer 2

Start off by ensuring that your ODBC connection is defined as a "System DSN".

Answer 3

Thanks for the answers .

Himanshu , i was trying to implement your suggestion however i encountered another problem with Sql Server SSIS 2012 . After assigning the proxy when i executed the job i was givin the error message :

"Started:  11:23:32  Failed to execute IS server package because of error 0x80131904. Server: RGDAT125V\DEV, Package path: \SSISDB\DB2TOSQL\MigrationPackage\Datamart.dtsx, Environment reference Id: NULL.  Description: Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.  Source: .Net SqlClient Data Provider  Started:  11:23:32  Finished: 11:23:32  Elapsed:  0.094 seconds.  The package execution failed.  The step failed."

Microsoft has released a service pack to fix this issue as stated in KB http://support.microsoft.com/kb/2720171

My Infra team will be applying the service pack and i will post you the results .

---

shuchi

Answer 4

Rasmus thanks for the suggestion i am using a System DSN.

---

shuchi

Answer 5

Sure shuchi, Once the patch has been applied, check the solution. If it works. KUDOS.

---

Please use Mark as Answer; if my reply solved your problem. Use Vote As Helpful if a post was useful. |WWW.FACEBOOK.COM/SONNIE.HIM | |[phone] |

Answer 6

Hi Shuchi,

Please also make sure the proxy account you use to run the SQL Server Agent job has permissions on the folder where the ODBC driver is installed as well as the folder where the package is placed or installed.

Besides, also check whether there are incorrect configurations in the MsDTSSrvr.ini.xml fiel. You can verify the configurations referring to the following document:
Configuring the Integration Services (SSIS) Service

Regards,

---

Mike Yin
TechNet Community Support

Answer 7

Hi Mike

I guess you predicted the error that i will get after configuring Service Account .

After i created service account assigned it Read and Write access to my package folder. In job Step  I created a c type Command and specified command parameters to use 32 bit DTExe and gave package path. 

I am not sure how to check or modify  MsDtsSrvr.ini.xml

The error i get is :

Executed as user: COMAG\CMGSVCD01. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  09:51:50  Error: 2014-02-26 09:51:50.94     Code: 0xC0011007     Source: {667C7B7E-C20A-455D-9B85-F9E2840630C5}      Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.  End Error  Error: 2014-02-26 09:51:50.94     Code: 0xC0011002     Source: {667C7B7E-C20A-455D-9B85-F9E2840630C5}      Description: Failed to open package file "E:\Migration\obj\Development\Datamart.dtsx" due to error 0x80070003 "The system cannot find the path specified.".  This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format.  End Error  Could not load package "E:\Migration\obj\Development\Datamart.dtsx" because of error 0xC0011002.  Description: Failed to open package file "E:\Migration\obj\Development\Datamart.dtsx" due to error 0x80070003 "The system cannot find the path specified.".  This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format.  Source: {667C7B7E-C20A-455D-9B85-F9E2840630C5}  Started:  09:51:50  Finished: 09:51:50  Elapsed:  0.016 seconds.  Process Exit Code 4.  The step failed.

---

shuchi