Post reply

Sage ODBC

  • November 15, 2016 at 3:42 am

    #318935

    Hi

    I have a 32 bit ODBC connection configured in my connection manager to a sage DSN.

    I am trying to load the data from sage into sql. When i create an ODBC data source the connection work fine. I can select tables and view data. However when i try to run the package i get an ODBC connection error.

    Anyone got any ideas or alternatives to ODBC?

    Thanks

  • November 15, 2016 at 4:59 am

    #1912446

    Have you tried running in 32-bit mode?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 15, 2016 at 8:53 am

    #1912561

    Hi

    it's a strange one.

    I have a 64 bit VM (Server 2012) with 32 bit Sage ODBC which isn't working in SSIS. I can see the data in preview but it won't run.

    I then set up the same installation on my local laptop and everything is fine. Possibly the VM?

    I'm testing now and will let you know how it goes.

    Thanks

  • November 15, 2016 at 9:05 am

    #1912571

    spin (11/15/2016)

    Hi

    it's a strange one.

    I have a 64 bit VM (Server 2012) with 32 bit Sage ODBC which isn't working in SSIS. I can see the data in preview but it won't run.

    I then set up the same installation on my local laptop and everything is fine. Possibly the VM?

    I'm testing now and will let you know how it goes.

    Thanks

    Are you aware that Visual Studio is a 32-bit client, but that when VS runs packages, it runs them using a 64-bit executable? In VS, go to Project / Properties / Debugging and set Run64BitRuntime to false to force 32-bit mode.

    Or, find a 64-bit Sage ODBC driver and configure another DSN, with the same name as the 32-bit version. Then your package should work regardless of how it is run.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 15, 2016 at 9:57 am

    #1912602

    that has saved me so much time. Thanks.

    😀

  • November 17, 2016 at 1:55 am

    #1912971

    Hi

    I've hit another wall. So i've now deployed the package to SQL 2012 (64 bit) but can't get it to run from management studio.

    I'm running it under the sa account but when i check the SSISDB All Executions report i'm getting...

    - There was an error trying to establish an ODBC connection

    - Data flow task error: The AquireConnection method call to the connection manager (My Sage DSN) failed with error code 0xC0014009.

    My DSN is a 32 bit.

    *****

    Just to expand on this i can't use a 64 bit driver as it gives me an architecture error with the application (sage)

    *****

    And another update: I can run the package using the DTEXECUI.EXE from command prompt

  • November 17, 2016 at 3:01 am

    #1912990

    aaarrggghhhh

    ok, so i have managed to get it to run.

    The package is deployed to SSISDB.

    If i manually run the package it fails.

    I discovered an option in Advanced tab which allows me to tick 32-bit runtime which, when run, SUCCEEDS!!!

    Then i create an agent job and create an SSIS step which looks at the SSIS catalog. I go into configuration, into advanced and make sure 32-bit is installed and.....IT FAILS!!!!!

    The error message is the same as the previous post.

  • November 17, 2016 at 5:54 am

    #1913029

    spin (11/17/2016)

    aaarrggghhhh

    ok, so i have managed to get it to run.

    The package is deployed to SSISDB.

    If i manually run the package it fails.

    I discovered an option in Advanced tab which allows me to tick 32-bit runtime which, when run, SUCCEEDS!!!

    Then i create an agent job and create an SSIS step which looks at the SSIS catalog. I go into configuration, into advanced and make sure 32-bit is installed and.....IT FAILS!!!!!

    The error message is the same as the previous post.

    This is a permissions problem, I would bet on that.

    You were right to find the 32-bit runtime check box. Make sure that this is checked.

    If you open the 'All Executions' (Overview) report for the package for one of the failed executions, you will see that there is a box at the top called 'Caller'. This gives you the name of the account which executed the package.

    You need to ensure that the account named here has rights to open the Sage connector.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 18, 2016 at 1:55 am

    #1913339

    Hi

    I've spoken to our outsourced IT support and they have come back with the following....

    "I believe the NT SERVICE\SQLSERVERAGENT is a local account and therefore you cannot give it permissions on another server.

    Will an AD account do? Do you need the password?"

    Is it possible to run the agent job under a different account? I've done some looking and seems like i need to set up a proxy?

    Thanks

    ***** PLEASE IGNORE ABOVE

    I have now managed to create a proxy and a security credential and changed the account in my agent job. SUCCESS!!!!!

    You just need an AD account which has the relevant security permission to the folder you running the SSIS package against. In my case the Sage data files.

    The link i followed is here :

    Thanks for all your help Phil. 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply