June 23, 2010 at 2:10 pm
I am new to SSIS but here is the situation:
Another employee has created a package and saved out to the server, she then created a job to run this package.
I can't run the job it errors out with the following:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
06/23/2010 13:44:59,DSI Import_STACEY,Error,0,SQL2,DSI Import_STACEY,(Job outcome),,The job failed.
The Job was invoked by User TRIDENT\slmacumber. The last step to run was step 1 (Run DSI Card Aging Import Package).
The job was requested to start at step 1 (Run DSI Card Aging Import Package).,00:00:05,0,0,,,,0
06/23/2010 13:44:59,DSI Import_STACEY,Error,1,SQL2,DSI Import_STACEY,
Run DSI Card Aging Import Package,,
Executed as user: TRIDENT\MSSQL2005SVC. ...00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:44:59 PM
Error: 2010-06-23 13:45:03.85 Code: 0xC0202009 Source: Package1 Connection manager "SourceConnectionExcel"
Description: SSIS
Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available.
Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154
Description: "Class not registered". End Error
Error: 2010-06-23 13:45:03.87 Code: 0xC020801C
Source: Data Flow Task Source - 'Card Aging$' [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2...
The package execution fa... The step failed.,00:00:05,0,0,,,,0
But I can go to the folder on the server and execute the package and it runs.
Why can she execute the job and I can't?
Thanks!
June 23, 2010 at 3:41 pm
It looks like it uses Excel and there are no 64-bit Excel drivers, run it as 32-bit using DTExec or check the box in SQL 2008..
CEWII
June 23, 2010 at 3:48 pm
It is using Excel; how do I use DTExec and I have SQL 2005.
How is she able to run it with no problems and I do?
Thanks.
June 24, 2010 at 6:07 am
What Elliot W was trying to say is that you should run the package in your job in 32-bit mode.
Start the package with the following command:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "myPackage" /SERVER "myServer" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
(cmdExec step).
I thought there was also an option in the SSIS package step to select 32-bit runtime environment, but I can't seem to find it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 24, 2010 at 9:12 am
da-zero (6/24/2010)
What Elliot W was trying to say is that you should run the package in your job in 32-bit mode.Start the package with the following command:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "myPackage" /SERVER "myServer" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
(cmdExec step).
I thought there was also an option in the SSIS package step to select 32-bit runtime environment, but I can't seem to find it.
I don't have a folder Program Files (x86), I have just Program Files.
So I did
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\\SQL2\Import\DSI Card Aging Import.dtsx" /SERVER sql2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
And now I get the error 0xC0014049. Description: Cannot find folder "\\SQL2\Import\DSI Card Aging Import.dtsx"
June 25, 2010 at 12:18 am
Right, sorry. In my current environment we have 32-bit and 64-bit co-exisiting and so we have a program files (x86) for 32-bit and a regular program files for the 64-bit on the server.
Also, the command line I gave you expects the package to be located on the MSDB folder of the SQL Server. I don't know if this is true in your situation?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 25, 2010 at 7:34 am
da-zero (6/25/2010)
Right, sorry. In my current environment we have 32-bit and 64-bit co-exisiting and so we have a program files (x86) for 32-bit and a regular program files for the 64-bit on the server.Also, the command line I gave you expects the package to be located on the MSDB folder of the SQL Server. I don't know if this is true in your situation?
Sorry new to this but where/what is the MSDB folder and does the package need to be in this
particular folder because this package resides in a folder on the SQL Server; that's also why I was wondering how the other employee was able to run it and I got the error and thought maybe it was a permission issue.
Thanks!
June 28, 2010 at 2:18 pm
da-zero (6/24/2010)
I thought there was also an option in the SSIS package step to select 32-bit runtime environment, but I can't seem to find it.
I don't believe that checkbox exists in SQL 2005, but it was added in SQL 2008, I have seen it there..
CEWII
June 30, 2010 at 2:54 pm
da-zero (6/24/2010)
What Elliot W was trying to say is that you should run the package in your job in 32-bit mode.Start the package with the following command:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "myPackage" /SERVER "myServer" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
(cmdExec step).
I thought there was also an option in the SSIS package step to select 32-bit runtime environment, but I can't seem to fin it.
Well I should have copied - "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" - exactly instead I seen that I didn't have 'Program Files (x86)' on my computer but that's not what mattered so I copied exactly and ran JUST FINE!!!
:w00t: Thanks!!!!!
January 28, 2014 at 1:00 pm
Hi,
I am running into this same problem, i changed the job step to point at :
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
have the job running as Operating system(CmdExec)
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /Job NAme /SERVER ServerNeme /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
and still receive the error:
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.". End Error Error: 2014-01-28 14:37:56.18 Code: 0xC020801C Source: Data Flow Task 1 Source - onlcsch$ [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.
This package does run succesfully when right clicked from Integration Services and 'Run Package' selected
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply