[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13751
  • Last Modified:

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_CANNOTACQUIRECONNECTIONFROMCONNECTIONM...  Process Exit Code 1.  The step failed.
0
yong_wu
Asked:
yong_wu
  • 7
  • 6
1 Solution
 
nmcdermaidCommented:
Boiling down the error output:

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.





0
 
yong_wuAuthor Commented:
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.
0
 
nmcdermaidCommented:
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.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
yong_wuAuthor Commented:
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_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Connection 2" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
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.
0
 
nmcdermaidCommented:
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?
0
 
yong_wuAuthor Commented:
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_CANNOTACQUIRECONNECTIONFROMCONNECTIONM...  Process Exit Code 1.  The step failed.
0
 
nmcdermaidCommented:
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\odbcad32.exe
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.
0
 
yong_wuAuthor Commented:
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'
0
 
yong_wuAuthor Commented:
And the ConnectionManager Type - OLEDB
0
 
nmcdermaidCommented:
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
0
 
nmcdermaidCommented:
Actually the bottom of the last post has the easiest workaround for you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494063&SiteID=1

>>
You can force the SQL Server Agent to execute the 32-bit version of DTExec by setting up the job as a CmdExec task instead of an SSIS package call, as detailed here:
http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html?diff=y
>>
0
 
yong_wuAuthor Commented:
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.
0
 
nmcdermaidCommented:
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.exe

Note its DTEXEC, not DTEXECUI


If you can't find it let me know.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now