January 29, 2009 at 12:59 pm
I have a package that I have run successfully from the machine that houses my SQL Server. When I set up a job and try to schedule it, I consistently get this error:
Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGr... The package execution fa... The step failed.
I am running it against a Progress database, and like I said, I can run it in VS2005 with no problem. Do I need to migrate my connection managers some way? Is this error telling me there is a problem with the package source? I've tried both SQL Server and File System and I keep getting rhe same issue.
I just tried changing it so that it had a log and I got this:
rights reserved. Started: 2:57:46 PM Error: 2009-01-29 14:57:46.96 Code: 0xC020801E Source: AcadianProgressImport Log provider "{D2147687-4B1F-4CEC-99FD-3C0EF398B9C3}" Description: The connection manager "Acadian.SYSPROGRESS" is an incorrect type. The type required is "FILE". The type available to the component is "ADO.NET:System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089". End Error Error: 2009-01-29 14:57:47.29 Code: 0xC0047062 Source: Move New Plan Table to DW Acadian Plan Table ODBC [133] Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCo... The package execution fa... The step failed.
Color me frustrated as hell right now. Thanks for any help/advice.
January 29, 2009 at 1:56 pm
When you run it in BIDs it runs in the context of your permissions and when you schedule it as a job it runs in the context of the Agent permissions. And most third party related such operations require your connection to progress must be admin permissions and you need to create a proxy admin account to use to run the package. So either make the relevant changes or run the package manually.
Kind regards,
Gift Peddie
January 30, 2009 at 12:08 pm
So I need to set it up to run with the Agent's credentials in BIDs? I'm not sure I understand how to do that.
January 30, 2009 at 12:22 pm
The how is covered below by Microsoft what is not covered is the change of permissions required in third party RDBMS because I helped a user using IBMs DB2 and we found the user permissions in DB2 also needs to be admin. That change was added in SQL Server 2005.
http://support.microsoft.com/kb/912911
Kind regards,
Gift Peddie
January 30, 2009 at 12:32 pm
By the way, I don't have to run it as a SQL Agent job if there is another way of doing it. I just want to run this package every night at 3am.
If there's an easier way of doing it, please let me know.
What I don't understand (and please forgive my ignorance - I'm a development/dba/web team of 1, me)
is why I need a separate machine for SSIS, SSAS, and SSRS. I figured I would use the SSIS machine to run the scheduled packages.
January 30, 2009 at 1:17 pm
Here is the thread with the DB2 AS400 some minor correction it is the permission used to create the package that needs to be admin and no I don't know another way because the other use xp_cmdshell which also require admin permissions.
Kind regards,
Gift Peddie
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply