yong_wu
asked on
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONM... Process Exit Code 1. The step failed.
Hi,
I ran the job and to execute a SSIS dts package in sql 2005 server, I got the following error message:
Thanks for your help!
Date 1/25/2008 4:52:00 PM
Log Job History (Survey Returned Weekly)
Step ID 1
Server GSBHDB3P
Job Name Survey Returned Weekly
Step Name Survey Returned Weekly
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: GSB\sqladmin2000. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:52:00 PM Progress: 2008-01-25 16:52:00.74 Source: Copy Data from Results to Sent Task Validating: 0% complete End Progress Progress: 2008-01-25 16:52:00.74 Source: Copy Data from Results to Sent Task Validating: 33% complete End Progress Error: 2008-01-25 16:52:01.27 Code: 0xC0202009 Source: Survey Returned Weekly Connection manager "Connection 2" 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: 2008-01-25 16:52:01.27 Code: 0xC020801C Source: Copy Data from Results to Sent Task OLE DB Destination [49] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
I ran the job and to execute a SSIS dts package in sql 2005 server, I got the following error message:
Thanks for your help!
Date 1/25/2008 4:52:00 PM
Log Job History (Survey Returned Weekly)
Step ID 1
Server GSBHDB3P
Job Name Survey Returned Weekly
Step Name Survey Returned Weekly
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: GSB\sqladmin2000. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:52:00 PM Progress: 2008-01-25 16:52:00.74 Source: Copy Data from Results to Sent Task Validating: 0% complete End Progress Progress: 2008-01-25 16:52:00.74 Source: Copy Data from Results to Sent Task Validating: 33% complete End Progress Error: 2008-01-25 16:52:01.27 Code: 0xC0202009 Source: Survey Returned Weekly Connection manager "Connection 2" 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: 2008-01-25 16:52:01.27 Code: 0xC020801C Source: Copy Data from Results to Sent Task OLE DB Destination [49] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
ASKER
Yes, we're using 64 bit. And we designed SSIS on one machine and works on the machine, but when we moved to another machine and just changed the new connection server name, and have such error message, beisde the name of the server, everything else just remain the same.
We're exporting records to Excel file from SQL server database and GSB\sqladmin2000 has full access to it. Thanks very much for your help.
We're exporting records to Excel file from SQL server database and GSB\sqladmin2000 has full access to it. Thanks very much for your help.
So 'Survey Returned Weekly Connection manager' is a standard Excel connection?
Basically, 'class not registered' means something is not installed on the other machine.
One way to find this is to use FileMon (use google to find it) and see what file it is looking for and failing to find.
Another way is to go to the other machine, open up the SSI and see if there any more useful error messages.
Basically, 'class not registered' means something is not installed on the other machine.
One way to find this is to use FileMon (use google to find it) and see what file it is looking for and failing to find.
Another way is to go to the other machine, open up the SSI and see if there any more useful error messages.
ASKER
on the SSI, I executed package, the debug output has the following message:
SSIS package "Survey Returned Weekly.dtsx" starting.
Information: 0x4004300A at Copy Data from Results to Sent Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC020801C at Copy Data from Results to Sent Task, OLE DB Destination [49]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
Error: 0xC0047017 at Copy Data from Results to Sent Task, DTS.Pipeline: component "OLE DB Destination" (49) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Copy Data from Results to Sent Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Copy Data from Results to Sent Task: There were errors during task validation.
SSIS package "Survey Returned Weekly.dtsx" finished: Success.
SSIS package "Survey Returned Weekly.dtsx" starting.
Information: 0x4004300A at Copy Data from Results to Sent Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC020801C at Copy Data from Results to Sent Task, OLE DB Destination [49]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
Error: 0xC0047017 at Copy Data from Results to Sent Task, DTS.Pipeline: component "OLE DB Destination" (49) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Copy Data from Results to Sent Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Copy Data from Results to Sent Task: There were errors during task validation.
SSIS package "Survey Returned Weekly.dtsx" finished: Success.
Are you referring to an ODBC connection? If so, ensure that it is
-a system DSN
-has been created on the new machine
-has been created usnig the 64 bit ODBC manager
You may also need to set up a new 'connection manager' on the new machine. Can you see the connection manager listed at the bottom? Can you test it? Are you sure you've changed the server name rather then the connection manager name?
-a system DSN
-has been created on the new machine
-has been created usnig the 64 bit ODBC manager
You may also need to set up a new 'connection manager' on the new machine. Can you see the connection manager listed at the bottom? Can you test it? Are you sure you've changed the server name rather then the connection manager name?
ASKER
I copied the same SSIS (or DTS) package from the old Server to another test server with 32 bit, and it just works fine. The server is not working has 64 bit and has no drivers listing on the ODBC Administrator beside SQL Native Client. I believe that's the problem, but I tested the connections (Source and destination) in the package in successfully. But when I executed the package and came up with the error message show on above:
Executed as user: GSB\sqladmin2000. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:52:00 PM Progress: 2008-01-25 16:52:00.74 Source: Copy Data from Results to Sent Task Validating: 0% complete End Progress Progress: 2008-01-25 16:52:00.74 Source: Copy Data from Results to Sent Task Validating: 33% complete End Progress Error: 2008-01-25 16:52:01.27 Code: 0xC0202009 Source: Survey Returned Weekly Connection manager "Connection 2" 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: 2008-01-25 16:52:01.27 Code: 0xC020801C Source: Copy Data from Results to Sent Task OLE DB Destination [49] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
Executed as user: GSB\sqladmin2000. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:52:00 PM Progress: 2008-01-25 16:52:00.74 Source: Copy Data from Results to Sent Task Validating: 0% complete End Progress Progress: 2008-01-25 16:52:00.74 Source: Copy Data from Results to Sent Task Validating: 33% complete End Progress Error: 2008-01-25 16:52:01.27 Code: 0xC0202009 Source: Survey Returned Weekly Connection manager "Connection 2" 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: 2008-01-25 16:52:01.27 Code: 0xC020801C Source: Copy Data from Results to Sent Task OLE DB Destination [49] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
Does the working package use any system DSN's? If you double click your connection manager, what does the heading say it is? Is it an ODBC data provider? Does it use a 'System Data Source Name'?
If so, you need to recreate it in the 64 bit ODBC admin:
1. Go to the start menu, type start/run
2. in the box type
C:\WINDOWS\SysWOW64\odbcad
3. Make sure your DSN is listed in there (in system DSN). If it isn't, add it.
That path above is the the 64 bit ODBC admin, which writes to the 64 bit registry.
If so, you need to recreate it in the 64 bit ODBC admin:
1. Go to the start menu, type start/run
2. in the box type
C:\WINDOWS\SysWOW64\odbcad
3. Make sure your DSN is listed in there (in system DSN). If it isn't, add it.
That path above is the the 64 bit ODBC admin, which writes to the 64 bit registry.
ASKER
The head said:
Provider:Native OLE DB\Microsoft Jet 4.0 OLE DB Provider. And the Database file name is an Excel file with MS Excel format.
In addition, our packages do not use any system DSN's or 'System Data Source Name'
Provider:Native OLE DB\Microsoft Jet 4.0 OLE DB Provider. And the Database file name is an Excel file with MS Excel format.
In addition, our packages do not use any system DSN's or 'System Data Source Name'
ASKER
And the ConnectionManager Type - OLEDB
It sounds like there is no 64 bit Jet driver installed.
Many references on the web say that the driver does not exist.
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic38683.aspx
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=894038&SiteID=1
This ne has a very similar error:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494063&SiteID=1
The response is:
"The is no way to use Jet driver from a 64-bit SQL Server unfortunately. They did not port the driver to 64-bit. So this would only work from a 32-bit SQL Server. "
As a workaround, I see you have made this comment:
>> We're exporting records to Excel file from SQL server database and GSB\sqladmin2000 has full access to it. Thanks very much for your help.
Why not just get the data directly from SQL rather then exporting from SQL to Excel, then importing frm Excel into SQL.
Alternatively you could export to CSV and use the flat file connection manager on your current SSIS package.
Nick
Many references on the web say that the driver does not exist.
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic38683.aspx
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=894038&SiteID=1
This ne has a very similar error:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494063&SiteID=1
The response is:
"The is no way to use Jet driver from a 64-bit SQL Server unfortunately. They did not port the driver to 64-bit. So this would only work from a 32-bit SQL Server. "
As a workaround, I see you have made this comment:
>> We're exporting records to Excel file from SQL server database and GSB\sqladmin2000 has full access to it. Thanks very much for your help.
Why not just get the data directly from SQL rather then exporting from SQL to Excel, then importing frm Excel into SQL.
Alternatively you could export to CSV and use the flat file connection manager on your current SSIS package.
Nick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your information.
I used DTExecUI.exe to run my SSIS (or DTS) package with 32 bit, it works, but when I ran it , it came up with a GUI or Execute Package Utility menu, I need to click on the button Execute, and then processing. But I need to schedule it and run it automatically on midnight, directly to process without click the Execute button. Is some one can help me with that? Thanks very much.
I used DTExecUI.exe to run my SSIS (or DTS) package with 32 bit, it works, but when I ran it , it came up with a GUI or Execute Package Utility menu, I need to click on the button Execute, and then processing. But I need to schedule it and run it automatically on midnight, directly to process without click the Execute button. Is some one can help me with that? Thanks very much.
DTExecUI is the user interface version
DTExec is the non user interface version.
So use DTExec.EXE. Don't use DTExecUI.EXE.
The full path (from that wiki) is
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.
Note its DTEXEC, not DTEXECUI
If you can't find it let me know.
DTExec is the non user interface version.
So use DTExec.EXE. Don't use DTExecUI.EXE.
The full path (from that wiki) is
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.
Note its DTEXEC, not DTEXECUI
If you can't find it let me know.
1. The error is on the 'Survey Returned Weekly Connection manager' connection
2. The error is 'Class not registered'
'Class not registered' usually means you need to install some kind of library on your SQL Server.
I notice you are using 64 bit. Whatever library your 'survery returned' requires needs to be 64 bit.
I take it that you are developing your SSIS on one machine, but the SQL Server is another?
What kind of source are you importing records from? If its some kind of file (Access, Excel) then GSB\sqladmin2000 needs file access to it.