We are running the SSIS from the machine it will be deployed, small company implementation so dont have the ability for a spread out environment. Is there some throttling that dtexecui does that everything else does not? Thinking that it handles certain errors with massaging actions where as the others are dummy runners and dont go "above and beyond the call of duty" to get the package run. How do i verify that a ODBC connection is System wide? I looked at the 32-bit ODBC connection list and 64-bit list and the ODBC connection is only listed in the 32-bit System DSN. Could that be an issue?Nothing but something

Arthur's on the right track here. The fact it "worked better" in 32-bit mode indicates you only have a 32-bit driver for Pervasive. The fact it still didn't work indicates you need to make sure that DSN is accessible by the account you created as a proxy. To confirm, make a proxy for your account, change the job to that proxy, and see if it runs. Talk to me now on

Based on what I see you have an issue with the ODBC connection. I am surprised you can run it using the GUI. Have you ever ran it successfully outside the development machine? Arthur My Blog

More questions at this stage: so you built your package on a 32 Bit machine, then deployed to a 64 bit box, is that a correct statement? If your connection is listed in the System DSN tab, then it is a system-wide setting, and it has to be this way in your scenario. Is it the case with you?Arthur My Blog

I have built a DTSX package to do some data trasnfer on a nightly basis. The source database is a Pervasive SQL and the destination is SQL 2008. Problem is that i can manually click on the package and it runs just fine. But i am unable to Schedule it as a System Task to run nightly or SQL Agent to run nightly. I have tried adding it to the Integration Services instance but still no dice. The package is not encrypted, so no need for the /Decrypt argument. I have tried using the solution where you create a non-user account on SQL and then associate it to a Proxy service but that still produced the same error result. As well, seems 'dtsexecui' is the only application that will run the package with no errors where as all the other attempts, including 'dtsexec' produce ODBCException errors, pretty consistent on the errors though. Executed as user: ***\***. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:14:51 PM Error: 2011-01-10 13:14:52.51 Code: 0xC0047062 Source: *** ADO NET Source [4966] 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.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) End Error Error: 2011-01-10 13:14:52.52 Code: 0xC0047017 Source:*** SSIS.Pipeline Description: component "ADO NET Source" (4966) failed validation and returned error code 0x80131937. End Error Error: 2011-01-10 13:14:52.52 Code: 0xC004700C Source: Xfer TL to TLD SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-01-10 13:14:52.52 Code: 0xC0024107 Source: *** Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:14:51 PM Finished: 1:14:52 PM Elapsed: 1.125 seconds. The package execution failed. The step failed. This is the one error that is pretty much returned through out all the errors across all the potential implementations that has not changed. Any that can shed some light into this i would greatly appreciate it. The ADO.Net Source is basically picking up on a ODBC System connection stored on the server.Nothing but something

When i changed the SQL Agent job to 32-bit it returned: Executed as user: ***\***. The step did not generate any output. The return value was unknown. The process exit code was -1073740940. The step failed. Is this a Success exit code? seems the code should be 0.Nothing but something

Well tried to access the same ODBC connection via code in a .net quick app and using a XSD framework Adapter/DataTable and came with the same error ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. Connection String: <add name="TimberlineConn" connectionString="Driver={Timeberline Data};Dsn=Timberline;uid=<userid>;pwd=<password>;dbq=<share>\TIMBERLINE\<folder>\;codepage=1252;dictionarymode=0;standardmode=0;maxcolsupport=1536;shortennames=1;databasetype=1" providerName="System.Data.Odbc" /> Any thoughts Nothing but something

I agree with Todd. But there is still a confused thing, as Arthur asked, did you execute the pacakge in different machines? so you built your package on a 32 Bit machine, then deployed to a 64 bit box, is that a correct statement? If so, please make sure the DSN is also existing in the production machine too. Thanks, Jin ChenJin Chen - MSFT

Chances are you compiled your .Net app as MSIL, which would then execute in an x64 process space, which wouldn't be able to see the 32-bit only DSN. Same problem. Talk to me now on

I agree with Todd. But there is still a confused thing, as Arthur asked, did you execute the pacakge in different machines? so you built your package on a 32 Bit machine, then deployed to a 64 bit box, is that a correct statement? If so, please make sure the DSN is also existing in the production machine too. Thanks, Jin ChenJin Chen - MSFT

I agree with Todd. But there is still a confused thing, as Arthur asked, did you execute the pacakge in different machines? so you built your package on a 32 Bit machine, then deployed to a 64 bit box, is that a correct statement? If so, please make sure the DSN is also existing in the production machine too. Thanks, Jin ChenJin Chen - MSFT

Did not correct the issue....even tried running dtexec with the argument '/x86' and still didnt work. Thinking that the whole DTSX package system is not wanting to be automated. Nothing but something

Did not correct the issue....even tried running dtexec with the argument '/x86' and still didnt work. Thinking that the whole DTSX package system is not wanting to be automated. Nothing but something

Did not correct the issue....even tried running dtexec with the argument '/x86' and still didnt work. Thinking that the whole DTSX package system is not wanting to be automated. Nothing but something

Sorry for the delay. Todd, In using the SBIS, how do i change it to not compile in MSIL, i am coming in after the fact of this being built and compiled and am unsure totally of the settings that were made during the original DTSX design. Jin, The package was built on the 64-bit server in SBIS and is being executed from the same server. The source server is 32-bit only (Timberline) and the destination is 64-bit. The 32-bit ODBC Admin shows the DSN...but the 64-bit does not. As i learned between the original post and now, Timberline is 32-bit only and as such does not support any kind of 64-bit drivers. Nothing but something

Sorry for the delay. Todd, In using the SBIS, how do i change it to not compile in MSIL, i am coming in after the fact of this being built and compiled and am unsure totally of the settings that were made during the original DTSX design. Jin, The package was built on the 64-bit server in SBIS and is being executed from the same server. The source server is 32-bit only (Timberline) and the destination is 64-bit. The 32-bit ODBC Admin shows the DSN...but the 64-bit does not. As i learned between the original post and now, Timberline is 32-bit only and as such does not support any kind of 64-bit drivers. Nothing but something

Sorry for the delay. Todd, In using the SBIS, how do i change it to not compile in MSIL, i am coming in after the fact of this being built and compiled and am unsure totally of the settings that were made during the original DTSX design. Jin, The package was built on the 64-bit server in SBIS and is being executed from the same server. The source server is 32-bit only (Timberline) and the destination is 64-bit. The 32-bit ODBC Admin shows the DSN...but the 64-bit does not. As i learned between the original post and now, Timberline is 32-bit only and as such does not support any kind of 64-bit drivers. Nothing but something

So if the problem persists: try running the SQL Agent job in the 32 bit mode. Does that correct the issue? Found you an image of the setting (in yellow): http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx Arthur My Blog

So if the problem persists: try running the SQL Agent job in the 32 bit mode. Does that correct the issue? Found you an image of the setting (in yellow): http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx Arthur My Blog

So if the problem persists: try running the SQL Agent job in the 32 bit mode. Does that correct the issue? Found you an image of the setting (in yellow): http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx Arthur My Blog

true....would 'dtexecui' be a good test for package viability? currently we can run the dtsx package manually but have to double click the package and click 'execute'. what is the 'dtexecui' app implying that other resource are not, like 'dtexec'? i will get that checklist and verify though Todd, thank you. This might help me debug later SSIS deployments. I personally think it is Sage Timberlines ODBC driver being a P-I-T-A.Nothing but something

true....would 'dtexecui' be a good test for package viability? currently we can run the dtsx package manually but have to double click the package and click 'execute'. what is the 'dtexecui' app implying that other resource are not, like 'dtexec'? i will get that checklist and verify though Todd, thank you. This might help me debug later SSIS deployments. I personally think it is Sage Timberlines ODBC driver being a P-I-T-A.Nothing but something

true....would 'dtexecui' be a good test for package viability? currently we can run the dtsx package manually but have to double click the package and click 'execute'. what is the 'dtexecui' app implying that other resource are not, like 'dtexec'? i will get that checklist and verify though Todd, thank you. This might help me debug later SSIS deployments. I personally think it is Sage Timberlines ODBC driver being a P-I-T-A.Nothing but something

Or the 'Timberline Data' ODBC driver doesnt want to play well with the SSIS system as a whole.Nothing but something

Or the 'Timberline Data' ODBC driver doesnt want to play well with the SSIS system as a whole.Nothing but something

Or the 'Timberline Data' ODBC driver doesnt want to play well with the SSIS system as a whole.Nothing but something

Do you have all the latest service packs and updates in the troubled environment?Arthur My Blog

Do you have all the latest service packs and updates in the troubled environment?Arthur My Blog

Do you have all the latest service packs and updates in the troubled environment?Arthur My Blog

not quite sure on that....as i am remotely building this package. Doing this for a client.Nothing but something

not quite sure on that....as i am remotely building this package. Doing this for a client.Nothing but something

not quite sure on that....as i am remotely building this package. Doing this for a client.Nothing but something

Sorry - I thought I saw that you executed this package from a .Net compiled app - not Agent. Disregard my comments - there's nothing to change in BIDS. Follow Arthur's latest advice - read that article and maybe this one too. Talk to me now on

Sorry - I thought I saw that you executed this package from a .Net compiled app - not Agent. Disregard my comments - there's nothing to change in BIDS. Follow Arthur's latest advice - read that article and maybe this one too. Talk to me now on

Sorry - I thought I saw that you executed this package from a .Net compiled app - not Agent. Disregard my comments - there's nothing to change in BIDS. Follow Arthur's latest advice - read that article and maybe this one too. Talk to me now on

I would recheck the following: You're running your package in 32-bit mode. See my link for details on how to do that. You have the 32-bit SSIS tools installed. This is not the default for 64-bit SQL Server installations. The DSN is available on the system that's executing the package. The proxy used in the Agent step has permissions to see the DSN, and use it. If you're still receiving the "DSN not found" error - that's what it actually is. A combination of the above is causing that DSN to be not visible, or not accessible. SSIS doesn't react especially oddly with ODBC providers, in my experience. Talk to me now on

I would recheck the following: You're running your package in 32-bit mode. See my link for details on how to do that. You have the 32-bit SSIS tools installed. This is not the default for 64-bit SQL Server installations. The DSN is available on the system that's executing the package. The proxy used in the Agent step has permissions to see the DSN, and use it. If you're still receiving the "DSN not found" error - that's what it actually is. A combination of the above is causing that DSN to be not visible, or not accessible. SSIS doesn't react especially oddly with ODBC providers, in my experience. Talk to me now on

I would recheck the following: You're running your package in 32-bit mode. See my link for details on how to do that. You have the 32-bit SSIS tools installed. This is not the default for 64-bit SQL Server installations. The DSN is available on the system that's executing the package. The proxy used in the Agent step has permissions to see the DSN, and use it. If you're still receiving the "DSN not found" error - that's what it actually is. A combination of the above is causing that DSN to be not visible, or not accessible. SSIS doesn't react especially oddly with ODBC providers, in my experience. Talk to me now on

You need to understand where the package is being run when you use the various methods to run it. Whenever you use DTEXEC from the command line, DTEXECUI from the command line, right-click a package in SSMS and "Execute" - you're not running the package on the server the package is saved/stored on. You're running the package on the machine that the command prompt is on, or that SSMS is executing on. So - part of your troubleshooting may be misleading you if you are successfully executing the package "from SSMS" on your own computer, but can't from SQL Agent. Talk to me now on

You need to understand where the package is being run when you use the various methods to run it. Whenever you use DTEXEC from the command line, DTEXECUI from the command line, right-click a package in SSMS and "Execute" - you're not running the package on the server the package is saved/stored on. You're running the package on the machine that the command prompt is on, or that SSMS is executing on. So - part of your troubleshooting may be misleading you if you are successfully executing the package "from SSMS" on your own computer, but can't from SQL Agent. Talk to me now on

You need to understand where the package is being run when you use the various methods to run it. Whenever you use DTEXEC from the command line, DTEXECUI from the command line, right-click a package in SSMS and "Execute" - you're not running the package on the server the package is saved/stored on. You're running the package on the machine that the command prompt is on, or that SSMS is executing on. So - part of your troubleshooting may be misleading you if you are successfully executing the package "from SSMS" on your own computer, but can't from SQL Agent. Talk to me now on

DTExecUI is almost entirely identical to DTExec - it's not doing any magic hand-waving or "extra effort" to get the package to run. They're both "dumb" and only do what they're instructed. Are you still having the original problem of using the ODBC driver? Or is this a new error? Talk to me now on

Yeah cant get ADO.Net to produce a consistent connection to the ODBC DSN database. I am able to connect to the database via .net ODBC connector but the SSIS ADO.Net connector isnt very reliable. The source version (Sage's Timberline - Pervasive) is 32-bit and the destination version (MS-SQL 2008 64-bit) just arent jiving. We have currently back-burner'ed the project but it will come back up eventually this year and i need to be able to resolve it.Nothing but something

Yes....if i run it through dtexec it fails but when i run it through UI, it works fine. There must be some kind of exception handling going on through UI that doesnt occur with dtexec. The pattern that i notice is that dtexec is very dumb and does only as its instructed where as dtexecUI will do everything it can to get the package to run.Nothing but something

Sorry for the long delay in response.....I only develop on the server. I rarely develop SSIS anywhere else but on the SQL Server.Nothing but something

Do you mean the issue still exists? E.g. you can run it using 'dtsexecui' but not DTExec?Arthur My Blog

Is this discussion still live? I am building our Data WArehouse at the University. I have a request for a new system to be incorporated. The system has been specd and costed in Pervasive 11. They want to charge £5k more for a SQL server implementation. Does SSIS have ODBC drivers for Pervasive 11 or is the problem above still current? Thanks

Is this discussion still live? I am building our Data WArehouse at the University. I have a request for a new system to be incorporated. The system has been specd and costed in Pervasive 11. They want to charge £5k more for a SQL server implementation. Does SSIS have ODBC drivers for Pervasive 11 or is the problem above still current? Thanks

I don't believe that the OP's issue is related to Pervasive at all. It has to do with his (lack of) experience with the SSIS runtime environment. Microsoft doesn't provide ODBC drivers for Pervasive. Pervasive does. Talk to me now on