SSIS Package errors out in SQL Server Agent
I am trying to run a SSIS Package in SQL Server Agent (SQL Server 2008 ver 10.0.2775) on 64 bit machine (Windows 2003 R2 Enterprise Edition 64 Bit Edition with 16GB Ram). I can run the package without any issues when I use BIDS. I had to make sure that I move the BIDS location to C:\ProgramFilesx86\Microsoft Visual Studio 9.0\.. instead of the default C:\Program Files (x86)\.. because it could not connect to Oracle database. My SSMS.exe is located "D:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" My package is using configuration file and I have deployed the package using the manifest file. I am signing in as an administrator and have full rights. When I try to run the package through SQL Server Agent as a scheduled job using steps, I am getting the following error. The process could not be created for step 1 of job 0xFD940A6C8B2E1145A38E49F1D8ED3BCE (reason: %1 is not a valid Win32 application). The step failed. Please note that I did try to execute with and without setting the "Use 32 Bit run time " under Execution Options in the Job Step properties. I have built and deployed the packages with "Run64BitRunTime" set to True and False I do have quotation marks set in IMAGEPATH in the registry for HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT -- value is "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE" -i MSSQLSERVER My environment path variables are D:\oracle\product\10.2.0\client_2\bin;D:\oracle\product\10.2.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\WINDOWS\sysWOW64;C:\Program Files (x86)\IBM\Director\bin;C:\Program Files (x86)\Common Files\IBM\ICC\cimom\bin;D:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;D:\Program Files\Microsoft SQL Server\100\Tools\Binn\;D:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;D:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\;C:\WINDOWS\system32\WindowsPowerShell\v1.0 and I have tried with quotation marks around them as well. Security on package is DONTSAVESENSITIVE But when I created the job as CMDEXEC and executed the following cmd "D:\Program Files\Microsoft SQL Server (x86)\100\DTS\Binn\DTExec.exe" /f "D:\Program Files\Microsoft SQL Server (x86)\100\DTS\Packages\Ssis.InitialLoad\Ssis.InitialLoad.Configuration.dtsx" /conf "D:\Program Files\Microsoft SQL Server (x86)\100\DTS\Packages\Ssis.InitialLoad\AllState.Ssis.InitialLoad.Configuration.Config.dtsConfig" it errored out with the following error: Source: Ssis InitialLoad Configuration Connection manager "DSRC COMMON SCHEMA" Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider MSDAORA.1 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Progress: 2010-11-18 10:47:35.82 Source: SQL Process Error Audit Executing query "--ETL Proceess Audit Update ETLProcessAudit Set E...".: 100% complete End Progress Error: 2010-11-18 10:47:35.82 Code: 0xC00291EC Source: SQL Check Source Batch Flag Execute SQL Task Description: Failed to acquire connection "DSRC COMMON SCHEMA". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2010-11-18 10:47:35.82 Code: 0x80019002 Source: FLC Check Source System Batch Flag Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Warning: 2010-11-18 10:48:35.84 Code: 0x80019002 Source: AllState Ssis InitialLoad Configuration Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:47:35 AM Finished: 10:48:35 AM Elapsed: 60.625 seconds. Process Exit Code 1. The step failed. Can someone guide me in troubleshooting these errors? Thanks! Arpan
November 18th, 2010 11:55am

Arpan, Error message indicate that your package is not able to connect "DSRC COMMON SCHEMA". Please check this connection or create proxy account for your SQL server Agent. In your first case, please check the event viewer for error description. Let us know your observation.Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2010 12:45pm

"The requested OLE DB provider MSDAORA.1 is not registered -- perhaps no 64-bit provider is available." It seems you don't have the right drivers/connector to connect to you Oracle data source. Or you have 32 bit version of the drivers installed whereas the package expects 64-bit version. Try installing the correct version of the Oracle connector for SSIS (Microsoft offers it for download here). Or try running the job in 32-bit mode. Hope this helps. Cheers!! Muqadder.
November 18th, 2010 2:03pm

"The requested OLE DB provider MSDAORA.1 is not registered -- perhaps no 64-bit provider is available." It seems you don't have the right drivers/connector to connect to you Oracle data source. Or you have 32 bit version of the drivers installed whereas the package expects 64-bit version. Try installing the correct version of the Oracle connector for SSIS (Microsoft offers it for download here). Or try running the job in 32-bit mode. Hope this helps. Cheers!! Muqadder. You need to install the Oracle connector on the box that is running the package (Server). You might have it on your local desktop and it will work fine when you run it from your machin. but when you deploy it onto the server you need to make sure that you have installed the connector onto the box that runs the package. Ben mansouriBusiness Intelligence Architect
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2010 2:53pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics