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

  • 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


    • Edited by shuchi Tuesday, February 18, 2014 11:41 AM
    Tuesday, February 18, 2014 10:32 AM

Answers

  • 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 | |+91-9742-354-384 |

    • Proposed as answer by Mike Yin Monday, February 24, 2014 4:17 PM
    • Marked as answer by shuchi Friday, February 28, 2014 3:03 PM
    Tuesday, February 18, 2014 10:48 AM
  • 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


    • Edited by Mike Yin Monday, February 24, 2014 4:23 PM add content
    • Marked as answer by shuchi Friday, February 28, 2014 3:02 PM
    Monday, February 24, 2014 4:20 PM

All replies

  • 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 | |+91-9742-354-384 |

    • Proposed as answer by Mike Yin Monday, February 24, 2014 4:17 PM
    • Marked as answer by shuchi Friday, February 28, 2014 3:03 PM
    Tuesday, February 18, 2014 10:48 AM
  • Start off by ensuring that your ODBC connection is defined as a "System DSN".

    Tuesday, February 18, 2014 12:32 PM
  • 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

    Wednesday, February 19, 2014 11:28 AM
  • Rasmus thanks for the suggestion i am using a System DSN.


    shuchi

    Wednesday, February 19, 2014 11:29 AM
  • 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 | |+91-9742-354-384 |

    • Marked as answer by shuchi Friday, February 28, 2014 3:02 PM
    • Unmarked as answer by shuchi Friday, February 28, 2014 3:02 PM
    Thursday, February 20, 2014 5:26 AM
  • 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


    • Edited by Mike Yin Monday, February 24, 2014 4:23 PM add content
    • Marked as answer by shuchi Friday, February 28, 2014 3:02 PM
    Monday, February 24, 2014 4:20 PM
  • 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

    Wednesday, February 26, 2014 10:24 AM