none
The AcquireConnection method call failed with error code 0xC0202009.

    Question

  • 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)
    Messages
    Error 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
    Wednesday, December 07, 2005 4:29 PM

Answers

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

    Thursday, December 14, 2006 7:03 PM

All replies

  • 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
    • Proposed as answer by SQL-Prov Monday, September 13, 2010 2:35 PM
    Wednesday, December 07, 2005 11:08 PM
  • 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
    Wednesday, December 07, 2005 11:25 PM
  • Well the error indicates that the destination can not acquire the connection to the destination connection (in this case I would guess an excel file).  Does the file exist at the location specified?  If you switch to another destination does the package work?  Is jet installed (correctly) on the PC you are running the package on?

    Thanks,
    Matt
    Wednesday, December 07, 2005 11:45 PM
  • 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.

    Wednesday, December 07, 2005 11:56 PM
  • 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
    Thursday, December 08, 2005 3:51 PM
  • 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
    Monday, December 12, 2005 3:01 PM
  • 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.

    Wednesday, December 21, 2005 10:59 PM
  • 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.
    Saturday, January 21, 2006 2:03 PM
  • 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 completed
    Finished, 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 

    Monday, February 06, 2006 6:41 PM
  • 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 the Transaction- 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.

     

    Regards
    Rakesh

     ------------------------------------------------------------------------------------

    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.

     

    Monday, April 10, 2006 6:11 AM
  • I have the same problem.
    Monday, April 24, 2006 6:34 AM
  • I have the same problem toooooooo..............i have couple of  dataflow tasks inside the sequence container, one dataflow task is secuessflly executed and second one is failing  and 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????

     

    Tuesday, April 25, 2006 6:29 PM
  • 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.
    Tuesday, May 02, 2006 6:13 AM
  • 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

    Wednesday, May 10, 2006 7:00 PM
  • 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 Utility
    Version 9.00.1399.06 for 32-bit
    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started:  11:20:09
    Progress: 2006-07-26 11:20:11.58
       Source: DFT - Export Bad Pending Request
       Validating: 0% complete
    End Progress
    Progress: 2006-07-26 11:20:11.60
    ....
    End Progress
    DTExec: The package execution returned DTSER_SUCCESS (0).
    Started:  11:20:09
    Finished: 11:20:16
    Elapsed:  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:

    Message
    Executed 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.

    Wednesday, July 26, 2006 9:39 AM
  • 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'
    Thursday, August 31, 2006 11:47 AM
  • 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

    Friday, September 01, 2006 3:08 AM
  • 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

     

     

    Tuesday, September 05, 2006 7:43 PM
  • 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

    Friday, September 08, 2006 1:04 AM
  • where is the excel file you're trying to work with, on the same server?
    Friday, September 15, 2006 12:22 AM
  • The answer to your 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.

    Saturday, September 16, 2006 3:23 AM
  • 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

    Tuesday, September 19, 2006 4:10 PM
  • 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).
    Tuesday, September 19, 2006 4:20 PM
  • 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

    Monday, October 02, 2006 6:01 PM
  • 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 will give you the acquireconnection method error. 

    Larry

    Monday, October 02, 2006 7:59 PM
  • 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.

    Wednesday, October 04, 2006 4:00 PM
  • 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.
    Tuesday, October 10, 2006 9:26 AM
  • 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.

    • Proposed as answer by HKouts Tuesday, March 16, 2010 4:19 PM
    Friday, October 13, 2006 10:52 PM
  • 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

    Wednesday, December 13, 2006 4:11 PM
  • 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.
    Wednesday, December 13, 2006 11:01 PM
  • 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"

    Thursday, December 14, 2006 2:39 PM
  • 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.

    Thursday, December 14, 2006 7:03 PM
  • 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

    Friday, December 15, 2006 9:15 AM
  • 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!

    Friday, December 15, 2006 5:49 PM
  • 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.

    Thursday, January 25, 2007 10:02 PM
  • 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.

    Wednesday, February 07, 2007 10:54 PM
  • Here is a link to the Microsoft article doing what 4468mt suggests (along with other resolution possibilities):

    http://support.microsoft.com/kb/918760

    Friday, February 09, 2007 8:27 PM
  • 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).

    Wednesday, February 21, 2007 5:55 PM
  • Changing the Transaction Option property from Required to Supported worked for me
    Thursday, March 01, 2007 11:14 AM
  • In my case, I always save my ETLs to the server with "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.
    Monday, April 09, 2007 2:09 PM
  • “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.

    Wednesday, May 23, 2007 10:48 AM
  • 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 schedule a Job, it comes up with the error..

     

    serBig Smileiagnostic,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 sql Agent with 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.

     

     

     


     

    Wednesday, July 25, 2007 3:44 PM
  •  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.
    Wednesday, July 25, 2007 3:48 PM
  • 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.

    Wednesday, October 17, 2007 7:59 PM
  • 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 log the 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

    Tuesday, December 18, 2007 9:41 PM
  • 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, I just wish it works.

    The workarounds I have found so far are actually not workable in a real life situation.

    Since everything hard and soft is becoming 64 bits by default these days, it is expected than more and more people will scream for such basic compatibility.

    Wednesday, December 19, 2007 5:22 AM
  • Apparently as a "feature", when using config files, passwords are never stored. Us lamo's that can't figure out a simple config file (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 still pulled 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 authentication and 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 very shoddily (think I mispelled the made up word) documented feature.

    Wednesday, December 19, 2007 8:32 AM
  • Hi,

    I´m 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
    Monday, January 07, 2008 4:35 PM
  •  

    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 worked just fine in 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!
    Thursday, January 17, 2008 4:07 PM
  • 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.

    Thursday, January 17, 2008 5:44 PM
  • 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.

     

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

    Friday, January 18, 2008 12:49 PM
  •  

    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 ssis show not 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 Sad ). 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.

    Friday, January 18, 2008 3:17 PM
  • 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

     

    Tuesday, January 22, 2008 6:08 PM
  • Paulino,

     

    I'm having the exact error scenario.

     

    Did you get a resolution to this?

     

    Dean

    Wednesday, February 06, 2008 10:03 PM
  • I am having the same or very similar error as Kali Charan Tripathi.

     

    The data source that I am connected to is an external and authenticates via SQL 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

    Friday, February 22, 2008 7:26 PM
  •  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 and authenticates via SQL 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.

    Friday, February 22, 2008 7:54 PM
  • 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!

    Wednesday, April 02, 2008 12:13 AM
  •  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

    Wednesday, November 05, 2008 11:06 PM
  • 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!
    Thursday, May 14, 2009 3:10 PM
  • 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
    • Proposed as answer by Allain Bill Monday, November 16, 2009 1:18 PM
    Thursday, July 16, 2009 7:27 PM
  • Hello all

    Try this one

    i just had the same problem and this one solved the problem, dont know if it helps you

    http://support.microsoft.com/kb/945977

    Allain

    Monday, November 16, 2009 1:19 PM
  • 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.
    Thursday, December 24, 2009 10:06 PM
  • 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

    Wednesday, May 12, 2010 6:28 PM
  • 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

    Thursday, June 24, 2010 11:27 PM
  • Thanks HeavyMetal........ It worked for me....
    Monday, July 19, 2010 11:24 AM
  • thanks Panos! this worked for me  :)
    Wednesday, January 12, 2011 6:30 PM
  • 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.


    Monday, May 30, 2011 6:29 AM
  • 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.

    Wednesday, July 06, 2011 3:31 PM
  • 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!
    Thursday, March 01, 2012 12:19 AM
  • I am getting this on a 64 bit box. How do I run my package in the WoW?

    Thanks!
    Mike

    Friday, March 09, 2012 8:53 PM
  • Hi,

    we got the same problem with some SSIS packages on SQL 2005.

    We have created proxy authorized to execute packages and CmdExec, we linked it to sql credential and then we have added the user (linked to the credentials) to the msdb role db_dtsoperator .

    Now the packages work again without any problems.

    Bye,

    Simone

    Tuesday, May 14, 2013 10:28 AM