Error: Server Agent cant find dts file

Im trying to schedule an integration sevice package with no anvile. I have tried both running it in Agent under Integration services and operation system (CmdExec) and still wont work. Under CmdExec, the command is /FILE "C:\Documents and Settings\ebuah\My Documents\Visual Studio 2005\Projects\Swamp_Solution\FIRSTTIME_DOWNLOAD\SSIS_ACT.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF. In the advance tab, I checked outfile to a log.txt file so as to read the log if there was any errors. The account running the job is the same account that sql server agent services is running which would only mean I really dont need any proxy account to run the job. When i run the job under CmdExec, i get the error

Message
Executed as user: CYPRESS\Administrator. The process could not be created for step 1 of job 0x529C255F20199B40A83858B1CCC875F2 (reason: The system cannot find the file specified). The step failed.

when I run the same package under integeration system, the error I get in the logg file is

<record>
<event>OnError</event>
<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>
<computer>SPIDERMAN</computer>
<operator>CYPRESS\Administrator</operator>
<source>ACT TABLE 250</source>
<sourceid>{99b92f4c-686d-47a6-b136-4d1ec698c837}</sourceid>
<executionid>{CD95BB1A-7FE4-468B-904D-C02B3FEBF1B0}</executionid>
<starttime>4/17/2006 1:20:47 PM</starttime>
<endtime>4/17/2006 1:20:47 PM</endtime>
<datacode>-1073450910</datacode>
<databytes>0x</databytes>
</record>

If you ask me, I dont know whats going on but I am very fustrated. Have been working on this for 4 days now. I really really need help.

The error is right there. "Data source name not found".

I would guess that either you haven't deployed the ODBC DSN on that machine or the account you are running it as doesn't have permission to see it.

-Jamie

|||I am running on a 64x machine on which i installed the 64x version of SSIS. The odbc driver in installed in the wow folder (32 bit). My packages are set up to execute in 32bit so they can see the odbc driver. Do you think that under server agent, its not able to see the 32bit odbc driver. (by the way, the account i running under is an admin account, has rights to everything)

Also, in the process of debugging, I tried a few things to eliminate causing senarios. I created a simple parkage that doesnt use the odbc drive. It does a staight pull from one sql server table to another using ole connection . When i run that under Sever Agent with Integration services, it runs smoothly. For the same simple package, when i run under CmdExec, I get the error: The process could not be created for step 1 of job 0xF791BD51E4709E48AD83FB56038ECF95 (reason: The system cannot find the file specified). The step failed. So, my question is what is wrong. It runs fine under integeration services but errors out under CmdExec.

From my first test, I made an assumption that only running under intergration service in Sever Agent will work since its passed the test. Based on that, I changed my simple package to include an odbc source connection (pulling from a remote DB/4 FC server with 32bit DB/4 odbc driver installed in wow on 64bit). When I run the package in Agent under integration services, I got an error: The package execution failed. The step failed. Luckly, a detailed error information by the package log indicated

<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>

which was my intial error. So Jamie, I think you are right that it has something to do with the DNS. Since the odbc drive is registered in the 32bit DNS, Server Agent is not able to see it.

If you can help me answer my trial error as to how a package could runs fine under intergeration services but breakes under CmdExec (does the command sting have to be different) and also if there is a way to get around this driver issue. Thanks.|||I tried a 32bit install of sql server on a 32bit machine and the job run just fine so the problem is that Sever Agent on 64bit machine can only see the 64x DNS and not 32 but in SSIS, the IDE give you the option to run in 64bit debugging mode or not, and when you choose not to run in 64 mode, it able to see the 32bit DNS. That was why it was able to run in SSIS just fine but not in Server Agent. In summary, make sure your driver has a 64bit version before developing packages to be run in Server Agent on a 64bit machine.|||I am having the same problem. It looks like when I run the SSIS package through the SQL Server Management Studio or through Visual Studio, the package honors the 64 bit runtime option. But, when I run it through the SQL Server Agent, it doesn't. I did some checking, and it looks like the package file doesn't even contain that flag. Is there some way to alter the command line in the job step to force the package to run in 32 bit mode?|||You could just use the CmdExec job step and specify the 32 bit dtexec program. This has other benefits, in that you have job step logging for CmdExec steps, unlike the SSIS subssytem. This can be very usefull in troubleshooting, e.g. Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)|||Thanks. That seems to be working.