The AcquireConnection method call failed with error code 0xC0202009.
I've seen the previous threads on this (although maybe not all of them). However, i don't think I'm getting the error for the same reason. The full error I'm getting is:- Pre-execute (Error)MessagesError 0xc0202009: {F1B3B35C-FAE3-48F6-A169-4E4D8D99F9B6}: An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error". (SQL Server Import and Export Wizard) Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "DestinationConnectionExcel" failed with error code 0xC0202009. (SQL Server Import and Export Wizard) Error 0xc004701a: Data Flow Task: component "Destination 64 - production_effectivities" (7042) failed the pre-execute phase and returned error code 0xC020801C. (SQL Server Import and Export Wizard) The entire package is running on one machine. The data source is SQL Server 2005 and the destination (this happens with both of them) is Excel or Access. Either way I cannot get the package which the wizard generated to run at all. This error occurs after the first table is exported. I'm running on WinXP SP2 with 2005 Developer and ALL components installed except analysis services.Anyone else have this problem or know the solution?Jeff
December 7th, 2005 7:29pm

Jeff,I noticed that your destination name is Destination 64. Is there a chance that you are running this package an a 64 bit box and not in the WoW. If so, then that is your problem. Jet is not supported on 64 bits (at least Excel and Access) so you would have to run your package in the WoW so you would get the 32 bit Jet.Thanks,Matt
Free Windows Admin Tool Kit Click here and download it now
December 8th, 2005 2:08am

Nope. Unfortunately it's a straight 32 bit not 64 bit. In fact, it's on a laptop that's a couple of years old. SSIS is the only thing I can't get to really run right. But if it's reporting 64 bits (I didn't know what the destination meant) then that's even more bizarre.So, when is that first service pack coming out? Can I provide you more information? Screenshots, etc?Jeff
December 8th, 2005 2:25am

Yes, the file exists. I created it myself and I selected it in the wizard so there's no way that could be the problem. As for JET, I installed Office 2003 doing a custom install with everything installed on the machine. This error occurs for both Excel and Access. I also have Visual Studio installed on the machine. And the file is local (in fact it is sitting on the desktop but I've also tried with the file in the root of the c drive), so I've tried various destinations.
Free Windows Admin Tool Kit Click here and download it now
December 8th, 2005 2:56am

Actually, what I meant by other destinations are ones not associated with Jet. Have you tried a flat file destination for example. If that works then the problem is most likely a Jet problem not an SSIS problem and you might try reinstalling jet or downloading the latest Jet and installing that.Thanks,Matt
December 8th, 2005 6:51pm

So, using a flat file as the output destination worked. But I went to try to re-install MDAC and in particular the JET engine. No file that I downloaded from MS allowed me to re-install it. Do you have any suggestions on how to do this?The MDAC downloads (up to and including MDAC 8sp1) don't include the JET engine and there is only one JET engine update. However, I'm running XPsp2 and every time I try the install it says a newer version is on the machine and won't let me upgrade or repair. Any thoughts?Thanks.Jeff
Free Windows Admin Tool Kit Click here and download it now
December 12th, 2005 6:01pm

I too have the same problems....all of a sudden my SSIS package is going nuts and I haven't done anything. Sometimes it works somtimes it just decides onto to communicate to my database that is on the same box. It has nothing to do with Jet...I agree. I agree that I too am about to rip my hair out until MS gets a damn service pack out ASAP for this sh## My package was working beautifully for 2 days straight, what happened to SQL Server? I am doing stuff through terminal services on an MS virtual server...shouldn't affect anything but just thought I'd throw that in.
December 22nd, 2005 1:59am

When I am using the Import/Export Wizard on the same machine, it seemed OK. However, when building the SSIS package, I encounter the same problem.
Free Windows Admin Tool Kit Click here and download it now
January 21st, 2006 5:03pm

I am getting the same error. The Import Wizard works fine, but SSIS fails. I am running on W2003, x64 bit SP1, SQL 2005 Enterprise Edition. Excel file was created with Excel 2000 SP3.Package was saved without sensative data, and is being run using Windows Authentication. SQL Server is being run under a Local User Account used during the install process. This local account also has been granted Full Control over the Excel file. Windows account being used to run both the Import Wizard and SSIS is a Local Admin. I thought I tested the package in Sept CTP. I recreated in RTM code. Is this possibly a bug? Thanks, Greg E Below is the text of the error log. [DTS.Pipeline] Information: Validation phase is beginning. Progress: Validating - 0 percent complete[Source - Sheet2$ [1]] Error: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009. [DTS.Pipeline] Error: component "Source - Sheet2$" (1) failed validation and returned error code 0xC020801C. Progress: Validating - 50 percent complete[DTS.Pipeline] Error: One or more component failed validation. Error: There were errors during task validation. Validation is completedFinished, 12:06:53 PM, Elapsed time: 00:00:01.922[Connection manager "SourceConnectionExcel"] Error: 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". Finished, 12:06:53 PM, Elapsed time: 00:00:28.953
February 6th, 2006 9:41pm

I am getting the same error. My Destination and source both are on SQL Server 2005 on the same box. I am using SQL- Code for Source and and table as destination. When the package runnes under Transaction- TransactionOption-Supported then the packages excuted fine but it fails when i change theTransaction- TransactionOption to Required. It fails with following error code. [OLE DB Destination [22]] Error: The AcquireConnection method call to the connection manager "FMFCLSQADB01.DWH_Rakesh" failed with error code 0xC0202009. [DTS.Pipeline] Error: component "OLE DB Destination" (22) failed the pre-execute phase and returned error code 0xC020801C. [Connection manager "FMFCLSQADB01.DWH_Rakesh"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.". [Connection manager "FMFCLSQADB01.DWH_Rakesh"] Error: An OLE DB error has occurred. Error code: 0x8004D00A. When i change the source SQL query to Table or View then it works fine. Thanks for any suggestion. RegardsRakesh ------------------------------------------------------------------------------------ Now, my issues related to SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction."... are fixed.This is one of the very common errors that comes up in SSIS (SQL Server Integration service) I have faced this issue for at-least two occasions. Both of them have one common setting: TransactionOption was set to Required. In first case, I had multiple dataflow tasks but there were no sequence given. There were no error if I ran the package with TransactionOption=Supported. But when I ran the package in TransactionOption= Required, it got failed. To fix the issue I have changed my package and put the entire dataflow task one after another (connected thru Constraint). In another case, where I was doing data transfer from one server to another, I had to configure the MSDTC.
Free Windows Admin Tool Kit Click here and download it now
April 10th, 2006 9:11am

I have the same problem.
April 24th, 2006 9:34am

I have the same problem toooooooo..............i have couple of dataflow tasks insidethe sequence container, one dataflow task is secuessflly executed and second one is failingand giving fallowing errormessage : [IDFP_Client2 1 1 [189]] Error: The AcquireConnection method call to the connection manager "ServerName.DatabaseName" failed with error code 0xC0202009. when i chage the sequence container transaction property from Required to Supported, it is working fine. In my case source is sql server2000 and destination is sql server2005. Any body have solution for this????
Free Windows Admin Tool Kit Click here and download it now
April 25th, 2006 9:29pm

I am also having the same error, but it only developed when I put the dataflow task inside a Foreach Loop container. It makes no difference when setting the connection string expression to either of the following:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ @[User::varFileName]or@[User::varFileName]I have used the @[User::varFileName] with a flat file connection successfully.
May 2nd, 2006 9:13am

I am also having a similar problem. I am trying to move data from SQL Server 2000 to SQL Server 2005. I am using OLEDB connections for SQL 2000 and SQL Server Connections for SQL Server 2005. The SSIS package is running local on the SQL Server 2005 machine. The error I keep getting is: Error: 0xC0202009 at Insert data into machine_result, BT08 Machine Result [1]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". I do not have any linked servers and I do not have any OLE DB providers named "BULK". Any ideas? Wayne E. Pfeffer
Free Windows Admin Tool Kit Click here and download it now
May 10th, 2006 10:00pm

I also having the same problem but the strange thing is when I run it on the server in a dos box, with it's propper proxy account than it runs fine, no errors. open dos box (Cmd) with run as Domain\idxxxxxx and execute the command : C:\WINDOWS>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe"/SQL "\ESS\DbEucEapproval - ExportBadPendingRequest" /SERVER shad /MAXCONCURREN" -1 " /CHECKPOINTING OFF Microsoft (R) SQL Server Execute Package UtilityVersion 9.00.1399.06 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:20:09Progress: 2006-07-26 11:20:11.58 Source: DFT - Export Bad Pending Request Validating: 0% completeEnd ProgressProgress: 2006-07-26 11:20:11.60....End ProgressDTExec: The package execution returned DTSER_SUCCESS (0).Started: 11:20:09Finished: 11:20:16Elapsed: 6.141 seconds When I run it in an sql job in an operating system step with the same command as above: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\ESS\DbEucEapproval - ExportBadPendingRequest" /SERVER shad /MAXCONCURRENT " -1 " /CHECKPOINTING OFF than it failes: MessageExecuted as user: Domain\idxxxxxx. ... 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:20:36 Progress: 2006-07-26 11:20:37.69 Source: DFT - Export Bad Pending Request Validating: 0% complete End Progress Progress: 2006-07-26 11:20:37.71 Source: DFT - Export Bad Pending Request Validating: 33% complete End Progress Progress: 2006-07-26 11:20:37.71 Source: DFT - Export Bad Pending Request Validating: 66% complete End Progress Error: 2006-07-26 11:20:37.71 Code: 0xC0202009 Source: DbEucEapproval - Export Bad Pending Request Connection manager "Microsoft Excel 97-2000 REQ" Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error". End Error Error: 2006-07-26 11:20:37.71 Code: 0xC020801C Source: DFT - Export Bad Pending Reque... Process Exit Code 1. The step failed. The server is an X64 AMD Opteron system.
July 26th, 2006 12:39pm

I'm having the same problem, trying to run locally. The bizarre thing is that if I preview the daat from the excel source component, it opens the grid and displays the correct data - so the file exists, and I have access. Yet when I run, I have the error. this only started after I changed from doing 'table or view' to 'SQL command'
Free Windows Admin Tool Kit Click here and download it now
August 31st, 2006 2:47pm

I am also getting the same error. Here problem is cross server, When i use the same server as source and destination the Transaction property is working. But we have 2 servers, we should use two servers, So if i use the transaction option for using 2 servers, its saying the above error. I have checked the DTC's Log on, its using NTAUTHORITY\Network Services. And i have added the same as user in both servers. Still the error stays... Thanks for any suggestions. -Swarna
September 1st, 2006 6:08am

I have had this error but in my case writing to excel. it failed to get a connection (with a valid sharing violation), I had the file open. but after closing the file and retrying I got the error. until I restarted the Bi designer at which point it was fine Im assuming this must be related to some connection pooling issue Colin
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2006 10:43pm

I know this is an old post but it was so active and I was trying to hunt for an answer while trying to solve a similar problem. So essentially, when you enable transactions inside of SSIS, you'll need to have the Microsoft Distributed Transaction Coordinator started on any machine that is participating in the transaction. So, if you have Oracle on Server 2 and SQL Server on Server 1, where the package is running, you'll need to make sure DTC is started on Server 1 and Server 2. Even if it's started, Windows 2003 may not be broadcasting. You may have to go to Add / Remove programs to enable the network. You can use DTCPing.exe to test the connectivity between all the boxes. Brian Knight
September 8th, 2006 4:04am

The answer toyour problems which I found out myself may be this: You must ensure that the user that you setup to run the SSIS job, also has access to that Excel file. Go to the properties of that Excel file, and ensure that user has been added and has proper rights, I'd assume just read rights to that Excel file. I thought it was weird but yes, the user that the SSIS package is run by needs access to that excel doc also. So check which user is setup to run the SSIS package in the properties of the package then check your excel file. Let me know if this helped.
Free Windows Admin Tool Kit Click here and download it now
September 16th, 2006 6:23am

i am just trying to import data from a table to another table i have clicked on enable identity insert . dont know where is the problem in ssis or in me kindly help Messages Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.(SQL Server Import and Export Wizard) Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'SiteUrlId', table 'AllSites.dbo.AffliateSiteURL'; column does not allow nulls. INSERT fails.".(SQL Server Import and Export Wizard) Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.(SQL Server Import and Export Wizard) Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - AffliateSiteURL" (22) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.(SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.(SQL Server Import and Export Wizard) at last i have deleted identity column but again Messages Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".(SQL Server Import and Export Wizard) Error 0xc020901c: Data Flow Task: There was an error with input column ""Address"" (46) on input "Destination Input" (35). The column status returned was: "The value could not be converted because of a potential loss of data.".(SQL Server Import and Export Wizard) Error 0xc0209029: Data Flow Task: The "input "Destination Input" (35)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (35)" specifies failure on error. An error occurred on the specified object of the specified component.(SQL Server Import and Export Wizard) Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - AffliateSiteURL" (22) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.(SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.(SQL Server Import and Export Wizard) is this ssis utiltiy is dam rubbish or what is this ? who has suggested them to invent this .... this is a dark spot at sql server 2005
September 19th, 2006 7:10pm

I suspect that the first error message is a valid error message. You mention:' i have clicked on enable identity insert .' So you are expecting all the rows in your source table to provide you with correct Identity values. The erro message implies that your poulate SQL retrieved a NULL value for that column, and quite correctly failed. Is it possible that the source table has a NULL in the column you are mapping to your target table?Do you need to retain the identity values from the source table?Regarding the 2nd error:'"The value could not be converted because of a potential loss of data.".' What size and data-type is the data type you are selecting, and the target destination. I would read that error as saying that they are different daat types, or perhaps that your source data type is longer than the target. You may want otake the input into a DATA CONVERSION task to convert your source data type to the target data type.I do agree that the error messages are often not great, and sometimes completely useless (at least to someone as in-experienced as me).
Free Windows Admin Tool Kit Click here and download it now
September 19th, 2006 7:20pm

I got the same error when my SSIS was trying to Export Data in the Data Flow to a remote SQL 2000 db. QUICK FIX --> Set Data Flow property TransactionOption to Not Supported.However - I would like to figure out if it is possible to make transaction work in my Environment. As suggested in a prvious post I did this on local & remote server:1. Click Start, click Control Panel, click Administrative Tools, and then click Component Services. Expand Component Services, and then expand Computers. 2. Right-click My Computer, and then click Properties. 3. Click the MSDTC tab, and then click Security Configuration. 4. Make sure that the following check boxes are selected: Network DTC Access Allow Remote Clients Allow Remote Administration Allow Inbound Allow Outbound No Authentication Required 5. Make sure that the DTC Logon Account is set to NT AUTHORITY\NetworkService. 6. Add MSDTC.exe as an exception in Windows Firewall. Still when I tried to use DTCPing.exe - it still wasnt able to connect - -->RPC pinging exception-->1753(There are no more endpoints available from the endpoint mapper.)RPC test failed
October 2nd, 2006 9:01pm

I was having this problem working with the excel example on books online to loop thru a directory and get all the *.xls files to write them to a single destination. in the for each loop container, the retrieve file name needs to be name only. The default of fully qualified willgive you the acquireconnection method error. Larry
Free Windows Admin Tool Kit Click here and download it now
October 2nd, 2006 10:59pm

Try executing the SSIS using "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" (specify the full path to the X86 version of DTExec where ever it was installed) instead of just DTExec.
October 4th, 2006 7:00pm

Will Microsoft be releasing a fix for this problem anytime soon, because it can become quit troublesome if you make extensive use of excel reports.
Free Windows Admin Tool Kit Click here and download it now
October 10th, 2006 12:26pm

This is a 64 bit related problem. I resolved this by changing the setting to False from Visual Studio Solution->Properties->Debug->64Bit . Additionally you need to run your dts package by the 32 bit dtexec.exe executable not the 64bit. There are 2 versions available. To do this, you create an operating system step instead of the usual SSIS type. In Type you enter "type operating system" followed by c:\...\dtexec.exe <the prepopulated default>. You are basically forcing the system to use 32bit. That worked fine for me. There is an article at microsoft knowledge base that explains more, search SSIS 64 bit etc. Panos.
October 14th, 2006 1:52am

I have the same problem too.. OnError: The AcquireConnection method call to the connection manager "OracleDBase.Schema" failed with error code 0xC0202009. My package takes input from Oracle table and inserts into SQL Server 2005 Server. I can run my SSIS package in my desktop and works fine. I can run my package from the SQL server using "Execute Package Utility" from the Management Studio and it works fine! But, (unfortunately there is a but!) when I schedule a job (in the Server Agent) to execute it...it fails! I've tried changing the Data Flow TransactionOption property to Not Supported and still does not work. Please Help! Paulino
Free Windows Admin Tool Kit Click here and download it now
December 13th, 2006 7:11pm

Keith: I've added this to the command line in my job scheduled in my Server Agent : "c:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\SSIS_my_Package" /SERVER "MY_SERVER". It failed with this message: "The command line parameters are invalid"
December 14th, 2006 5:39pm

Paulino PP:The command should look like this: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "C:\Documents and Settings\Keith\My Documents\Visual Studio 2005\Projects\Test SSIS\Test SSIS\bin\test_ssis.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI This works if the package source is "File system". If you right click on the dtsx file and select open it should open with the Execute Package Utility, click on the last item on the left "Command Line" and you will see the full command line paramters. If you have saved your SSIS package to the server try saving it to a file system location and following this procedure. Make sure that Run64BitRuntime is set to False before you build and save the package.
Free Windows Admin Tool Kit Click here and download it now
December 14th, 2006 10:03pm

by the way, command line can invoke SQL stored packages as well. I find it easiest to 'double-clikc' the dtsx - this should invoke the DTExecUI. Using this UI, you can build the command line you need, and then instead of executing it, you can copy the command line, and edit that. The command line generated by the UI doesn't include the DTEXEC - you would need to add the "C:\Program Files....." Alternatively, you could BOL the syntax for DTEXEC to see the SQL stored package command line execution sysntax, but I like the DTEXECUI way, because it can create a nice example.... I should not I have had to edit logging sometimes with the DTEXECUI generated commands. iirc the syntax for DTEXEC calling SQL stored backages replaces the /FILE with /SQL, amongst oter things
December 15th, 2006 12:15pm

Keith: Sorry to disappoint you but I got the same error "The command line parameters are invalid". I've followed all your instructions and it just does not like it. When I take it off it does runs, but when I add the "c:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe", then it gives me the The command line parameters are invalid". Get this: I tested my job on my testing Server (also SQL2005) and it worked just fine! Amazing! I've validated all the settings from the servers and they look alike. Except for one thing...(embarrassing to say) the Client from Oracle was not installed in the server where it was failing. After installed, it worked just fine. So, problem solved! No more Error!
Free Windows Admin Tool Kit Click here and download it now
December 15th, 2006 8:49pm

Problem Solved! I had the same problem connecting to an Oracle database via ODBC. Changing the "Package Protection Level" setting from "Encrypt sensitive date with user key" to "Rely on server storage and roles for access control" solved the issue in my environment. This setting is available in SMS when importing the SSIS package into the server. At first glance this setting appears to be disabled due to the grey text box.
January 26th, 2007 1:02am

I found out a while back, that the package was failing because someone changed the NTFS permissions on an actual folder that the package was using. We had to add back in the correct NTFS accounts in order to write or whatever I was doing to whatever folder that I coded my SSIS package to use when moving or creating files to that folder...so it was bombing out not because of SQL Server, but because of something the code was trying to use! maybe this will help you.
Free Windows Admin Tool Kit Click here and download it now
February 8th, 2007 1:54am

Here is a link to the Microsoft article doing what 4468mt suggests (along with other resolution possibilities): http://support.microsoft.com/kb/918760
February 9th, 2007 11:27pm

Everytime this happened to me is because in one of the steps, don't know where, but the designer / deplyment wizard / who knows removes the password value from a connection. No matter what driver I am using, be it ODBC for AS400 tasks, JET with blank password, SQL server 2005, MySQL it always gets removed. the Connection string values can be PWD, Password it does not matter. It knows and removes it. Check you connection strings everytime you modify the package/ deploy. And good luck with the configuration overrides. I still can figure out the order they are apllied and have nothing but problems. So I just hard code the connection stings into the disigner. (All sorts of variable issues on global namespace and deployment).
Free Windows Admin Tool Kit Click here and download it now
February 21st, 2007 8:55pm

Changing the Transaction Option property from Required to Supported worked for me
March 1st, 2007 2:14pm

In my case, I always save my ETLs to the serverwith "Rely on server storage and roles for access controlRely on server storage and roles for access control", so that was not the issue for me.
Free Windows Admin Tool Kit Click here and download it now
April 9th, 2007 5:09pm

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction."... This is one of the very common errors that comes up in SSIS (SQL Server Integration service) I have faced this issue for at-least two occasions. Both of them have one common setting: TransactionOption was set to Required. In first case, I had multiple dataflow tasks but there were no sequence given. There were no error if I ran the package with TransactionOption=Supported. But when I ran the package in TransactionOption= Required, it got failed. To fix the issue I have changed my package and put the entire dataflow task one after another (connected thru Constraint). In another case, where I was doing data transfer from one server to another, I had to configure the MSDTC.
May 23rd, 2007 1:48pm

Hi Keith, Windows 2003 x 64 - SQL Sever 2005 - SQL server Agent - Jobs. I have SSIS package which is working fine when I execute it outside the SQL Agent. If I schedulea Job, it comes up with the error.. seriagnostic,server name, server name\admin,Rates.mdb,{C7F803A6-6056-4D5A-A3F0-4B5AE732ECFE},{74AFD7EE-FEE1-4A83-B8EF-F3DEF0E28C5E},25/07/2007 11:25:18,25/07/2007 11:25:18,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The external request has completed.OnError,server name,server name\admin,Move data to Staging Table Data Flow Task,{96D76958-FB62-4BF0-A0AA-AA191446607F},{74AFD7EE-FEE1-4A83-B8EF-F3DEF0E28C5E},25/07/2007 11:25:18,25/07/2007 11:25:18,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Rates.mdb" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Half the way it is processing without error, when connecting to .mdb file it is coming up with this error. I have set Proxy in sqlAgentwith full permission to required directories and files. Set 64bit to False. I tried using below command in both Command Line in package and also in SQL Server Agent. It is failed. "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "C:\Documents and Settings\Keith\My Documents\Visual Studio 2005\Projects\Test SSIS\Test SSIS\bin\test_ssis.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI Appreciate your suggestion.
Free Windows Admin Tool Kit Click here and download it now
July 25th, 2007 6:44pm

Sastri wrote: I tried using below command in both Command Line in package and also in SQL Server Agent. It is failed. "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "C:\Documents and Settings\Keith\My Documents\Visual Studio 2005\Projects\Test SSIS\Test SSIS\bin\test_ssis.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI Appreciate your suggestion. Any error messages? That path won't work for you as it's specific to Keith's environment.
July 25th, 2007 6:48pm

Hi, I've solved the problem by setting generating a Config File for the Packag. (select the package in the solution explorer go to SSIS and then select the configuration). So you add a configuration file to the package for the package (wich you can select when you schedule a job in de SQL Management Server). In this configuration for Oracle!! you only select the username + password property. Then close the package in Visual Studio. Go to the generated config file. fill in the password in the config file. Then youre problems (with an Oracle connection) should be solved. Be very selective with config files, it does not always work ok.... Greetz, Joey.
Free Windows Admin Tool Kit Click here and download it now
October 17th, 2007 10:59pm

I had the same problem, i suspect that the problem was about credentials because, my SQL Server 2005 was executing the Job for an SSIS with the System User (SQLSERVERAGENT) but when i execute manually the same job it has no problems, also without problems in the development environment, when i created a logthe only difference i saw was the user that execute that package and the errors in the conexion adquisition. The solution for me was to create a proxy and assign it to the job step, that allows to the package to be executed with the same user that in the development environment, in this way i havent to change anything in my package. In order to understand was really usefull for me read this page (http://www.mydatabasesupport.com/forums/sqlserver-dts/183309-run-ssis-package-sql-server-2005-error.html) where Charles Wang explain the problem and also this ones How to: Create a Proxy (SQL Server Management Studio) http://msdn2.microsoft.com/en-us/library/ms190698.aspx How to: Configure a User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio) http://msdn2.microsoft.com/en-us/library/ms187901.aspx Greetings Richard
December 19th, 2007 12:41am

Side comment; When will we have a 64 bits version of SQL supporting the same stuff than the 32 bits, i.e. JET and Oracle oledb. I do not care if it is some kind of emulation mode, Ijust wish it works. The workarounds I have found so far are actually not workable in a real life situation. Since everything hard and softis becoming 64 bits by default these days, it is expected than more and more people will scream for such basic compatibility.
Free Windows Admin Tool Kit Click here and download it now
December 19th, 2007 8:22am

Apparently as a "feature", when using config files, passwords are never stored. Us lamo's that can't figure out a simple configfile (everyones useful help when I was asking) finally realize this after many attempts and hours scouring the net.If you want your config file to have connection info, you must open it up and edit it manually. Then the passwords will stick. If you will be deploying on other servers, make sure you know what the file encryption and password junk does. I turned mine to off, and don't store connection info in the package or sensitive info I think they call it; the passwords are stillpulled from config file). Then you can use the config file for debugging and testing, and then production using a different config file. If this method does not suit you for multiple deployments, there is always inverse config settings or whatever name they use. Basically, you save the settings in an environmental variable and the registry. As for elevating the SQL agent priviledges, that works with the SSPI authenticationand SQL server, but is frowned on (lowest possible priv). Still won't help with DB2 or MySQL though. This only compensates for the package not storing your SQL credentials....as a veryshoddily (think I mispelled the made up word) documented feature.
December 19th, 2007 11:32am

Hi,Im new at this and found this thread quite helpful, It may be a silly answer and many may have solved this but I had 1 way to solve this. After creating new users etc etc etc, I found the solution to this, which was basically getting into SSIS, right click over the project name (once opened) inside the solution explorer, properties, then click on the debugging option in the configuration properties and in the debug options you have to change the run64bitRuntime value from True to False.Hope this works for you,Best regards,Fede
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2008 7:35pm

Ah, thank you, this helped me. No one told me about that little "feature", but youre post certainly helpd me. Since im not developing the package in the same network as the "production" server i had to be able to set my connectioins in the config file. And the package workedjust finein debug but as soon as i deplyed it to the server I got the same error code as in previously posts. When i also selected the passwords to be set in the config file, it all worked fine. Thanks again!
January 17th, 2008 7:07pm

Good, it really is an amazing quazi-free (especially when compared to other commercial products) when you get the hang of the quirks and features.
Free Windows Admin Tool Kit Click here and download it now
January 17th, 2008 8:44pm

Hi, When i am scheduling my SSIS Packages on Sql Server Agent i am facinf this problem.It is running fine when loading data from local to local server but unable to load data from other server with the of Sql Server Agent service. Apart from this if i am executing SSIS packges separately then it is coming fine. MessageExecuted as user: EBMSDBH\SYSTEM. ...xecute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:29:55 AM Error: 2008-01-18 05:30:26.72 Code: 0xC0202009 Source: Load_PointFact Connection manager "ebmsapp.facilities.unc.edu.HostedControls" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'FAC\EBMSDBH$'.". End Error Error: 2008-01-18 05:30:26.72 Code: 0xC020801C Source: BuilidingOrFacilityDim EBMSAPP(OLTP) [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ebmsapp.facilities.unc.edu.HostedControls" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the Acquire. The step failed. Please help regarding this. Thank You Kali Charan Tripathi
January 18th, 2008 3:49pm

One error in the exception dump says a primary key or unique index violation (record exists alread) Code Block Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Another says the login is bad. Code Block End Error Error: 2008-01-18 05:30:26.72 Code: 0xC020801C Source: BuilidingOrFacilityDim EBMSAPP(OLTP) [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. the system does seem to throw back random errors and unhelpful exceptions, but check your logins first, then see if the records are already in your destination table/DB. Set up a redirect on error for your ssis destination to an excel sheet or text file, or better yet check for and handle duplicates in your load (clean up data to be loaded) and check the destination for records using with that key already (slowly changing dimmension, or a join of you source table to the destination in sql, and performing some action to handle duplicates (delete / update exisitng, upsert all [insert or update]). Even if you are using a slowly changing dimmension, I have found that when you try to load 2 of the same record from the source table, it still blows up because the first record is inserted to the destination, then the second record comes along for an insert as well and causes the error. It seems to only check the destionation table for the record, not all of your data to be loaded as well. Even though all of the samples for ssisshownot needing an intermediate step (Extract -> load -> transform -> load) (samples : Extract -> Transform -> Load) I still find it very useful, and it speeds up my loads tremendously rather than having all the records sit in memory (still 32 bit ). If you load them in a staging table, you can then join the staging table to the destination to check for duplicates, or changes in data before loading them. If your source data is already in SQL server you might be able to join from the transactional system to the destination already.
Free Windows Admin Tool Kit Click here and download it now
January 18th, 2008 6:17pm

Hi Kali, It looks like the user your agnt job is running as may not have permissions to log into this server. Can you verify this and try again? -David
January 22nd, 2008 9:08pm

Paulino, I'm having the exact error scenario. Did you get a resolution to this? Dean
Free Windows Admin Tool Kit Click here and download it now
February 7th, 2008 1:03am

I am having the same or very similar error as Kali Charan Tripathi. The data source that I am connected to is an external andauthenticates viaSQL authentication. Therefore it is not going to match what our Sql server's services are running as. Maybe I am missing something? I was wondering whether my issue was because of the password that is required for the connection and/or how it is being stored (if at all.) Are you saying a config file containing the connection information may be the better way to go? Thanks for any help. Jennifer
February 22nd, 2008 10:26pm

Jennifer Booth wrote: I am having the same or very similar error as Kali Charan Tripathi. The data source that I am connected to is an external andauthenticates viaSQL authentication. Therefore it is not going to match what our Sql server's services are running as. Maybe I am missing something? I was wondering whether my issue was because of the password that is required for the connection and/or how it is being stored (if at all.) Are you saying a config file containing the connection information may be the better way to go? Thanks for any help. Jennifer Yes, storing the connectionstring in a package configuration (file or database) is the correct solution to this problem. Configurations are the primary SSIS feature used to enable package portability, which is the real underlying issue here.
Free Windows Admin Tool Kit Click here and download it now
February 22nd, 2008 10:54pm

Thanks akeiii. That did the trick for me too. Thahks for the tip on the Execute Package Utility too. You can just copy that code in there. Appreciate it!
April 2nd, 2008 3:13am

Rakesh Mishra wrote: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction."... This is one of the very common errors that comes up in SSIS (SQL Server Integration service) I have faced this issue for at-least two occasions. Both of them have one common setting: TransactionOption was set to Required. In first case, I had multiple dataflow tasks but there were no sequence given. There were no error if I ran the package with TransactionOption=Supported. But when I ran the package in TransactionOption= Required, it got failed. To fix the issue I have changed my package and put the entire dataflow task one after another (connected thru Constraint). In another case, where I was doing data transfer from one server to another, I had to configure the MSDTC. Rakesh, I'm getting the 0xC0200900 running from one server in one domain to a destination server in another forest. I'm using a dtsx file running from a Scheduled Task; execution account is a member of my domain and has approp permissions on the target db. This package works most of the time but fails intermittently with a Could Not Acquire Connection error. What exactly did you do to configure the MSDTC? thanks
Free Windows Admin Tool Kit Click here and download it now
November 6th, 2008 2:06am

Ok I am at my wits end! I am getting a class not registered error for this same problem...I have a stored proc that executes the cmdline syntax for executing a SSIS package passing 2 variables and writing/reading an excel file.I have changed the package to reflect it not to run in 64bit.I can run it manually and it works fine...but when I run the stored proc it fails! AAAAAAAAAAHHHHHHHHHHHHH!!!!!!!HELP ANYONE PLEASE!
May 14th, 2009 6:10pm

Hi All,I am getting the same error, but the problem that i have to face is different. I am just executing a dataflow control with source and destination to transfer the data. In DataFlow I have given one OLEDB source with MS Access 2000 as a database connected to it. The database is located on MAP Network drive. The destination is my local SQL server. All the connectiosns works fine and when i preview the data set from OLEDB source it display me the data result as welll. But when i execute the package it gives me pre-Exeuction error saying this as follows:[OLE DB Source [7952]] Error: The AcquireConnection method call to the connection manager "IT_AA" failed with error code 0xC0202009. [DTS.Pipeline] Error: component "OLE DB Source" (7952) failed the pre-execute phase and returned error code 0xC020801C. Can anyone please help me with this..It will be highly appreciated...Thanks,Faisal
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2009 10:27pm

Hello allTry this onei just had the same problem and this one solved the problem, dont know if it helps you http://support.microsoft.com/kb/945977Allain
November 16th, 2009 4:19pm

I was getting the same error in debug mode. I solved it by opening the connection, re-entering the password for the user and clicking OK. Everytime you open the connection manager, it clears the password, so hit cancel if you dont want it to forget your password.
Free Windows Admin Tool Kit Click here and download it now
December 25th, 2009 1:06am

If anyone's running into this problem pulling data from a read-only Log Shipping target database, here's the fix that worked for me... I was getting errors pulling data with SSIS from a database that was a Tran Log Shipping target (set for read-only access)... The same package ran fine against the database on the sending side of the log shipping setup though... as soon as I disabled log shipping and ran "restore database xxxx with recovery" making the old target db RW, the package started working against it. I'm not sure if it was just the disabling of the log shipping or both that and the restore steps that fixed it but anyway, it's working. YMMV. - Martin
May 12th, 2010 9:28pm

I had that same issue. It happened when I specified to use XML configuration file to configure the connection manager items. I could not access the data from the Excel file any more. In order to solve it, I went into the code (right click on package, and View Code) and I removed from my excel connection string the "InitialCatalog=;" string. After that everything went back to normal again. HeavyMetal
Free Windows Admin Tool Kit Click here and download it now
June 25th, 2010 2:27am

thanks Panos! this worked for me :)
January 12th, 2011 9:30pm

Thanks .. but for SQL SERVER 2008 R2 it should look like as "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /FILE "C:\Documents and Settings\Keith\My Documents\Visual Studio 2005\Projects\Test SSIS\Test SSIS\bin\test_ssis.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI You can copy aboce command and put it in notepad and save it as .bat and you can schedule that .bat file in windows scheduler. that will execute your 64 bit pkg.
Free Windows Admin Tool Kit Click here and download it now
May 30th, 2011 9:29am

I was having issues connecting to Oracle on pre execute phase giving the following error Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "xxx.xxx.xxx" failed with error code 0xC0202009. There may be error messages posted before this I found that setting the "DelayValidation" option to True for the data flow task solved my issues in VS. (Never have the issue on Agent job!!) As a side note to get SSIS pacakges running from Cmd I had to create a new proxy with elevated permissions to allow offline execution.
July 6th, 2011 6:31pm

Hi, I've solved the problem by setting generating a Config File for the Packag. (select the package in the solution explorer go to SSIS and then select the configuration). So you add a configuration file to the package for the package (wich you can select when you schedule a job in de SQL Management Server). In this configuration for Oracle!! you only select the username + password property. Then close the package in Visual Studio. Go to the generated config file. fill in the password in the config file. Then youre problems (with an Oracle connection) should be solved. Be very selective with config files, it does not always work ok.... Greetz, Joey. Thank you so much! This fixed it for me, I have been struggling for days trying to get the package configuration file to work. Removing the connectionString wasn't an obvious step. Now, to move on to the next headache!
Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2012 7:30pm

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

Other recent topics Other recent topics