Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONM... Process Exit Code 1. The step failed. : error, code, ssis, exit, process

May 17, 2008 10:12am pdt

1. chapmandew 1,124,487
2. angelIII 797,044
3. aneeshat... 383,966
4. ee_rlee 334,902
5. dtodd 258,729
6. mark_wills 251,810
7. ScottPle... 190,791
8. acperkins 173,198
9. ptjcb 115,237
10. brejk 104,465
11. EugeneZ 104,309
12. asvforce 103,642
13. bhess1 97,730
14. dportas 80,581
15. BrandonG... 80,437

1. angelIII 9,752,157
2. aneeshat... 4,441,515
3. acperkins 4,161,658
4. ScottPle... 4,004,843
5. arbert 2,245,195
6. Lowfatsp... 2,162,122
7. rafrancisco 1,460,132
8. imran_fast 1,450,838
9. EugeneZ 1,380,471
10. Hilaire 1,221,775
11. Nightman 1,136,404
12. chapmandew 1,124,487
13. Mr_Peerapol 1,087,815
14. ptjcb 1,048,696
15. nmcdermaid 1,035,158

01.28.2008 at 06:04AM PST, ID: 23116190

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

7.8

Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONM... Process Exit Code 1. The step failed.

Zones: MS SQL Server, MS SQL DTS, SQL Server 2005

Tags: error, code, ssis, exit, process

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.

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: yong_wu

Solution Provided By: nmcdermaid

Participating Experts: 1

Solution Grade: B

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

01.28.2008 at 03:25PM PST, ID: 20764008

Rank: Genius

nmcdermaid:

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.

01.29.2008 at 05:54AM PST, ID: 20767722

yong_wu:

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.

01.30.2008 at 12:37AM PST, ID: 20775502

Rank: Genius

nmcdermaid:

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.

01.30.2008 at 05:41AM PST, ID: 20776871

yong_wu:

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.

02.05.2008 at 01:21AM PST, ID: 20821641

Rank: Genius

nmcdermaid:

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?

02.12.2008 at 11:59AM PST, ID: 20878210

yong_wu:

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.

02.12.2008 at 03:27PM PST, ID: 20880345

Rank: Genius

nmcdermaid:

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.

02.13.2008 at 06:08AM PST, ID: 20884211

yong_wu:

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'

02.13.2008 at 06:23AM PST, ID: 20884322

yong_wu:

And the ConnectionManager Type - OLEDB

02.13.2008 at 08:14PM PST, ID: 20890856

Rank: Genius

nmcdermaid:

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

02.13.2008 at 08:59PM PST, ID: 20891020

Rank: Genius

nmcdermaid:

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
>>

Accepted Solution

02.15.2008 at 05:20AM PST, ID: 20901356

yong_wu:

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.

nmcdermaid

02.15.2008 at 03:30PM PST, ID: 20906995

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.

20080236-EE-VQP-29 / EE_QW_2_20070628