none
SSIS error, code 0x8004005 when running package on certain machines

    Question

  • I've developed a SSIS package that takes some data as an XML file, processes it and inserts it into our DB. When the package is being debugged (i.e. running in BIDS), it works fine from my local machine. I can also compile the package to a .dtsx file and run it via command line by executing DTExec.exe. I am running SQL Server 2005. When I move it to a test server, also running SQL Server 2005, and Integration services, I cannot execute the package via command line. I keep receiving this error

     

    Error: 2011-08-16 17:50:39.11
     Code: 0xC0202009
     Source: ProcessSubmissionData Connection manager "Master"
     Description: An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] 
    Data source name not found and no default driver specified". End Error

     

    I've searched the web and tried every solution I can find, to no avail. Is there anyone who can help with this? If it helps, this is the connection string that the connection manager is using

    Data Source=xxxx;User ID=xxxx;Password=xxxx;Initial Catalog=database;Provider=SQLOLEDB;

     


    I plan to live forever. So far so good.

    Tuesday, August 16, 2011 9:57 PM

Answers

  • If DelayValidation causes your package to function, then there's something in your package that's dynamically changing the connection manager or availability of the DSN/database.

    What do I mean by that?  First, I'll describe DelayValidation.  By default, SSIS is very "strict".  Data types don't implicitly convert to each other, and SSIS wants you to get everything perfect as early as possible so that you have fewer problems at runtime.  One of the ways it does that is by running a "check" when you open the package - a "validation" check.  You could think of it as compiling, attempting to find errors in the "code".  Most times that's just fine.  But sometimes it's not.  For example, your package may create a temporary table, import a text file into it, perform some activity, then drop the table.  So before your package is executed, the table doesn't exist.  But the "middle" of your package is operating on that table... that doesn't exist!  SSIS will throw validation errors in this case, because your package is "broken".  That's why the DelayValidation property exists.  When set to true, SSIS skips the "check out the package when you open it" step, and waits until just before it's about to use whatever object it's about to use.

    So, what seems to be the case is that when SSIS first opens your package, the DSN referred to in the package does not exist.  But later on in package execution, either the connection manager gets changed to refer to something that does exist... or the DSN gets configured to be valid.  This can happen through Property Expressions or Package Configurations, or any number of Execute Process Tasks or outside events triggered somehow by the package.

    Does that make sense?

    So - it's not at all a bad solution to find out that DelayValidation works for you.  But if I were you, I'd want to figure out why.  What resource is reconfigured, or what properties are changed dynamically?


    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Eileen Zhao Monday, August 22, 2011 10:07 AM
    • Marked as answer by Eileen Zhao Thursday, September 01, 2011 7:56 AM
    Thursday, August 18, 2011 4:40 PM
    Moderator

All replies

  • Are other packages running successfully on your test server?  If so, are those packages also connecting to SQL Server via OLE DB?

     

    Did you have a look at this...

    Error message when an SSIS package runs that is scheduled to run as a SQL Server Agent job: "An OLE DB error has occurred. Error code: 0x80004005"

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


    -Rich
    Wednesday, August 17, 2011 1:17 AM
  • This is our first SSIS package. We have no other packages unfortunately. I just looked at the article: my package is running from the file system via DTExec.exe and not as a SQL Server Agent job.
    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 1:34 PM
  • When deployed your package, did you leave the protection level at the default "user key" (EncryptSensitiveWithUserKey)? If so this is your issue.

    In BIDS change it to say EncryptSensitiveWithPassword and provide a password that you will use in DTExec to run your package.

    E.g. DTExec ... /Decrypt password


    Arthur My Blog
    By: TwitterButtons.com

    Wednesday, August 17, 2011 1:43 PM
    Moderator
  • I actually have it set to DontSaveSensitive. I will change it to include a password if that'll help.

     

    Also, I just found out that my server has BIDS installed. I opened the package in BIDS on that server, and it runs perfectly. I immediately attempted to run the same package that I just opened in BIDS via command-line and it failed with the same error. To be clear: the failing command-line is the same command-line being used on my workstation, where the package executes successfully


    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 1:53 PM
  • DoNotSaveSensitive requires you to pass along the credentials to access database sources. So it is your issue.

    Do use the "encrypt sensitive with password" option, or provide the password (not recommended).


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, August 17, 2011 1:57 PM
    Moderator
  • Okay I will definitely be trying this one out then. You may have just ended 4 days of despair. Just out of curiosity, why does it work on my workstation?
    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 2:19 PM
  • I changed it to EncryptSensitiveWithPassowrd, specified the password and passed it along as /Decrypt password and it still gives me the same error. I am calling DTExec.exe via an app that I've written so passing credentials is not a problem and can be hardcoded. By that do you mean passing a database connection string?
    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 2:24 PM
  • Because it can connect e.g. when you have provided the credentials sure it connects using EncryptWithuserkey, in BIDS it is a debug run, this is done by design, and when run using DTExec it is no longer a debug run because the package now is self-aware of the security considerations, e.g. and expects you to provide the credentials like it was explained above.

    See the MSDN article that describes the security model of SSIS packages: http://msdn.microsoft.com/en-us/library/ms141747.aspx


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, August 17, 2011 2:26 PM
    Moderator
  • No you supposed to provide the password at design time when using EncryptSensitiveWithPassowrd, and then just the password at run time e.g.

    dtexec /F MyPackage.dtsx /De mypwd


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, August 17, 2011 2:28 PM
    Moderator
  • Alright perfect... I just did that actually and I'm still getting the same error. At first I forgot to supply the password via command-line, and I got an error about that. When I supplied it (via /Decrypt xxxxx), I still get the error.
    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 2:29 PM
  • Try running this package on your machine, see if works
    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, August 17, 2011 2:50 PM
    Moderator
  • I did. It does.
    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 2:56 PM
  • Awesome, getting closer, then it must be the driver issue. Compare the drivers between the two.
    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, August 17, 2011 2:59 PM
    Moderator
  • It's always worked on my workstation. This link is a screenshot of the drivers from my workstation and the server. The XP-Style window is the server, and the Aero window is my workstation http://bit.ly/ogO9Ce

     


    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 3:06 PM
  • In prod, do you have both SSIS 2008 and SSIS 2005 installed?

    Inspect your PATH (environmental variable on the server, not your machine) and see if the path to SSIS 2008 preceding the one to 2005, I guess you deploy a 2005 format package?


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, August 17, 2011 3:44 PM
    Moderator
  • I think the ProtectionLevel is a red herring.

    Can you please describe your two environments a little more fully?  What OS and bitness are your desktop and server?  What version numbers, bitnesses and editions are your SQL installations?

    The error message states that it can't find the DSN.  I'm assuming you have the same DSN created on the server.  But bitness (32- or 64-bit) can typically cause problems here.


    Todd McDermid's Blog Talk to me now on

    Wednesday, August 17, 2011 3:51 PM
    Moderator
  • This server only has SQL Server 2005 installed.
    I plan to live forever. So far so good.
    • Marked as answer by BLeg Wednesday, August 17, 2011 3:52 PM
    • Unmarked as answer by BLeg Wednesday, August 17, 2011 3:52 PM
    Wednesday, August 17, 2011 3:52 PM
  • I turned off DelayValidation in the package and it executes properly on the server. What's the purpose of validating the package before run-time? Is keeping this option turned off a viable solution?
    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 3:54 PM
  • Todd:

    My workstation is a Windows 7 Enterprise 64-bit machine with both SQL Server 2005 64-bit and 2008 R2 64-bit installed. The server is Windows Server 2003 R2 Standard 32-bit with only SQL Server 2005 32-bit installed. The 64BitRuntime switch is disabled in the package, and both machines are using the 2005 DTExec file. Sorry, I didn't see your question until now or I would have answered sooner


    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 5:24 PM
  • Do you have the DSN set up on the server?

    Are you developing the packages in BIDS 2005 or in BIDS 2008?


    Todd McDermid's Blog Talk to me now on
    Wednesday, August 17, 2011 5:41 PM
    Moderator
  • I'm not sure what you mean by the DSN.

    BIDS 2005.


    I plan to live forever. So far so good.
    Wednesday, August 17, 2011 6:04 PM
  • I think Todd is more on the right track than I am... nevertheless, DSN (Data Source Name - http://mcaf.ee/p01f3) is an entry in ODBC Applet (typically the System one) that is basically a connection holder (you gave me the screenshots for).

    In short, Todd is asking if you have the same/needed DSN set in ODBC (if you use it to connect to your database through it)?


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, August 17, 2011 6:39 PM
    Moderator
  • That's correct - check the "Set up data sources (ODBC)" applet in Control Panel.  Your error refers to the DSN not being found or the driver not being specified.  In every case I can remember, it turned out the DSN wasn't specified.  You get different errors when drivers aren't there.
    Todd McDermid's Blog Talk to me now on
    Wednesday, August 17, 2011 8:13 PM
    Moderator
  • Thanks for the replies...

    I checked to see which DSNs are specified on both machines, (this link)  and neither of them have anything under System DSN. Also, in my package, there are 3 connections, each to a different database, and the error is only complaining about one of them. As I mentioned above, I turned on "DelayValidation" and the package now works on both my workstation and the server in question. I guess I don't understand how the package will work on my machine with no DSN specified and with DelayValidation set to False when the connection I'm passing as a parameter is the same as the default in the connection manager in BIDS, but the exact same scenario won't work on my server.

    Don't get me wrong, I'm pleased to now better understand the error, but unfortunately if the DelayValidation setting allows the package to perform properly on the server, it's a more viable option than trying to rely on changing system settings on customer's servers. Just for kicks, I will add a DSN to my server to try it out and let you know how it works.

    Thanks again.


    I plan to live forever. So far so good.
    Thursday, August 18, 2011 12:57 PM
  • If DelayValidation causes your package to function, then there's something in your package that's dynamically changing the connection manager or availability of the DSN/database.

    What do I mean by that?  First, I'll describe DelayValidation.  By default, SSIS is very "strict".  Data types don't implicitly convert to each other, and SSIS wants you to get everything perfect as early as possible so that you have fewer problems at runtime.  One of the ways it does that is by running a "check" when you open the package - a "validation" check.  You could think of it as compiling, attempting to find errors in the "code".  Most times that's just fine.  But sometimes it's not.  For example, your package may create a temporary table, import a text file into it, perform some activity, then drop the table.  So before your package is executed, the table doesn't exist.  But the "middle" of your package is operating on that table... that doesn't exist!  SSIS will throw validation errors in this case, because your package is "broken".  That's why the DelayValidation property exists.  When set to true, SSIS skips the "check out the package when you open it" step, and waits until just before it's about to use whatever object it's about to use.

    So, what seems to be the case is that when SSIS first opens your package, the DSN referred to in the package does not exist.  But later on in package execution, either the connection manager gets changed to refer to something that does exist... or the DSN gets configured to be valid.  This can happen through Property Expressions or Package Configurations, or any number of Execute Process Tasks or outside events triggered somehow by the package.

    Does that make sense?

    So - it's not at all a bad solution to find out that DelayValidation works for you.  But if I were you, I'd want to figure out why.  What resource is reconfigured, or what properties are changed dynamically?


    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Eileen Zhao Monday, August 22, 2011 10:07 AM
    • Marked as answer by Eileen Zhao Thursday, September 01, 2011 7:56 AM
    Thursday, August 18, 2011 4:40 PM
    Moderator