Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ssis 2012 package Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2012 10:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 28, 2012 10:57 AM
Points: 4, Visits: 21
I have successfully deployed my first package to sql 2012. The package runs fine in visual studio but when deployed it gets an error message saying there was an error trying to establish an odbc connection to the database. Anybody encounter this and does anybody have a solution?
Post #1400298
Posted Wednesday, December 26, 2012 10:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:47 PM
Points: 1,163, Visits: 1,187
Jon Jaroska (12/26/2012)
I have successfully deployed my first package to sql 2012. The package runs fine in visual studio but when deployed it gets an error message saying there was an error trying to establish an odbc connection to the database. Anybody encounter this and does anybody have a solution?

I've not done much yet with SSIS 2012, but here are questions that immediately come to mind: Are you running Visual Studio on your development machine? Do you have a DSN setup to the DB on your development machine? Is this same DSN setup on the server where you deployed the SSIS package? Does the account that the SSIS package run on the server have permissions to the database?

I hope this helps,
Rob
Post #1400463
Posted Thursday, December 27, 2012 7:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 28, 2012 10:57 AM
Points: 4, Visits: 21
Visual studio is installed on the server where sql server resides. The DSN is setup on the machine and running the package in debug mode works. I have to assume that permissions are correct because it runs this way.
Post #1400620
Posted Thursday, December 27, 2012 7:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:47 PM
Points: 1,163, Visits: 1,187
Jon Jaroska (12/27/2012)
Visual studio is installed on the server where sql server resides. The DSN is setup on the machine and running the package in debug mode works. I have to assume that permissions are correct because it runs this way.

Well no luck there. Hmm -- any reason you're using ODBC rather than an OLE DB provider? What is the data source you're connecting to?

Rob
Post #1400622
Posted Thursday, December 27, 2012 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 28, 2012 10:57 AM
Points: 4, Visits: 21
Its PostgreSQL
Post #1400636
Posted Thursday, December 27, 2012 11:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:47 PM
Points: 1,163, Visits: 1,187
There does appear to be OLEDB providers for PostgreSQL ( http://www.postgresql.org/about/news/1153/ ), but that doesn't answer your immediate question. Sorry I couldn't be more help.

Rob
Post #1400721
Posted Friday, December 28, 2012 2:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
Jon Jaroska (12/26/2012)
I have successfully deployed my first package to sql 2012. The package runs fine in visual studio but when deployed it gets an error message saying there was an error trying to establish an odbc connection to the database. Anybody encounter this and does anybody have a solution?


Did the package run as a SQL Agent job?

Can you post the full text of the error message?

As you have VS installed on the server, can you confirm that you can run the package using BIDS on the server? If so, I would be looking at possible permissions issues first.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1400834
Posted Friday, December 28, 2012 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 28, 2012 10:57 AM
Points: 4, Visits: 21
The package does not run in the sql agent, it fails. Also the package runs perfect in BIDS. Here is the full text of the error:

There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.

The AcquireConnection method call to the connection manager glxy_epace.epace_read failed with error code 0xC0014009.
Post #1400939
Posted Friday, December 28, 2012 9:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
Jon Jaroska (12/28/2012)
The package does not run in the sql agent, it fails. Also the package runs perfect in BIDS. Here is the full text of the error:

There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.

The AcquireConnection method call to the connection manager glxy_epace.epace_read failed with error code 0xC0014009.


OK - but I asked whether the package ran in BIDS on the server.

Can you try logging in as the user under which the SQL Agent service is running and then executing the package in BIDS again (on the server)?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1400951
Posted Monday, December 31, 2012 6:01 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 178, Visits: 565
The problem could be that you don't have 64 bit drivers installed. Packages run in BIDS as a 32 bit process (last time I used it), which can only 'see' the 32 bit hive of the registry (containing DSN's)

Then if you run the package in 64 bit (which is generally the default under SQL Agent), the process only see the 64 bit hive of thre registry, which does not contain your DSN (or driver).

So try running the package in 32 but mode. Here's a link that may help.

http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx

None of the clues you have given actually indicate this issue but it's the first thing I thought of when I saw your error message.


If you could post more information about your configuration it would help everyone.
Post #1401534
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse