Problem with running SSIS packages

  • I've just about run out of ideas here, hope someone can help.

    I've created a bunch of SSIS packages and am able to run them just fine within my SSIS project. They are encrypted using EncryptAllWithPassword and stored in the msdb.

    Within the packages I'm connecting to both an Oracle db and a SQL Server 2005 db.

    When I run a package from either a SQL Server Agent Job or from the command prompt, I get errors. The following errors are the ones I get from the command prompt:

    Error: 2008-06-12 14:46:11.81

    Code: 0xC0047062

    Source: Data Flow Task DataReader Source [1528]

    Description: System.Data.OracleClient.OracleException: ORA-01005: null password given; logon denied

    at System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle, Int32 rc)

    at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity,Boolean unicode, Boolean omitOracleConnectionName)

    at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)

    at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)

    at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

    at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

    at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

    at System.Data.OracleClient.OracleConnection.Open()

    at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(StringassemblyQualifiedName, String connStr, Object transaction)

    at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)

    at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)

    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

    End Error

    Error: 2008-06-12 14:46:11.82

    Code: 0xC0047017

    Source: Data Flow Task DTS.Pipeline

    Description: component "DataReader Source" (1528) failed validation and returned error code 0x80131938.

    End Error

    Progress: 2008-06-12 14:46:11.82

    Source: Data Flow Task

    Validating: 75% complete

    End Progress

    Error: 2008-06-12 14:46:11.82

    Code: 0xC004700C

    Source: Data Flow Task DTS.Pipeline

    Description: One or more component failed validation.

    End Error

    Error: 2008-06-12 14:46:11.82

    Code: 0xC0024107

    Source: Data Flow Task

    Description: There were errors during task validation.

    End Error

    DTExec: The package execution returned DTSER_FAILURE (1).

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

    This is the error I get when i run it from the SQL Server Agent Job:

    0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS: Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-06-12 13:36:22.93 Code: 0xC0016016 Source: Description: Failed to ... The package execution fa... The step failed.

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

    I'm specifying a config file when I call the package from the command prompt, here is what I am doing:

    dtexec /sq "mypackagename" /ConfigFile "C:\myconfigfile.dtsConfig"

    The config file specifies the package password, and the connection string, username and password for each of the db connections in the connection manager.

    I need to be able to eventually execute these from within a stored proc, but right now I can't even execute them from anywhere except within the project! 🙁

  • hey Nat,

    Welcome to the joys of SSIS...

    I had the same error thrown in my agent yesterday, and I was adament that there was something wrong with the way in which I implemented a custom component, but it turned out that I saved the package as encryptsensitivewithuserkey (once more, thank you Michael, you certainly saved my bacon with your response).

    I would suggest that you use SSIS configurations, which means that you dont need to save sensitive data in the package.

    Good luck

    ~PD

  • Could you please elaborate on what you did to fix this error? I've tried using Don't Save Sensitive, but then I can't even run my SSIS package through my project; I get the same Oracle errors saying I passed a null password.

    I've tried all the Protection Levels, and I've found I can't use the Server Storage protection level; I get errors when I try to save the package after selecting that, even though I am saving the packages to msdb. I'm currently only able to run my package through my project when I use EncryptAllWithPassword, however I'm getting the same errors as I mentioned before, when I run the package through a command line or using an Agent Job.

  • -use package configurations which have your connection strings present. Make sure its the FULL connection string (i.e. username and password are included)

    *Note: SSIS will not save sensitive info, you will have to open the configuration and add this manually.

    Change the package to not save sensitive

    ~PD

  • Please bear with me; I'm new to all of this.

    I've been using the Package Configuration Wizard to create my config file; how do I create one manually? Can you please give me an example of what to put in my config file to make this work?

    I can't seem to post XML in here, otherwise I'd post my config info here.

  • Not a sweat, hopefully we can get you to your end destination....

    Okey, you posted a blank...

    Ideally, you want to have a configuration for your connection string, and your connection string only.

    For example, I have a SQL connection string in my configuration which looks something like this:

    Data Source=YourServerName;Initial Catalog=YourDBName;User ID=YourUserID;password=YourPassword;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False

    Not sure what the Oracle connection string will look like, but it should be similar...

    Now, when you save this configuration, the userid and password (sensitive) WILL NOT be saved. You will have to add these by hand.

    Trust this helps

    ~PD

  • OK, so I removed everything else except the ConnectionString part from the config file for the Oracle connections, and now it works both via command line and in my Agent Job (yay!)

    Thanks for all your help and your patience! 🙂 Have a great weekend!

  • Hi,

    I'm facing the same problem but i'm unsuccessful.

    Is it possible for you to publish your Oracle connection string used in your configuration file ?

    Thanks,

    Dave

  • Data Source=yourDatabasename.world;User ID=yourUserid;Password=yourPassword;Persist Security Info=True;Unicode=True;

  • Thanks !

    it works for me too

  • Hi all,

    I have a same problem with oracle connections in configration file.

    I did the same which you did.

    I remove everthing from xml configaration file except ConnectionString

    it worked fine for me.

    thank you so much friend.

  • THANK YOU pduplessis. I REALLY APPRECIATED. I spent the whole day on this package. Thanks again!:w00t::w00t::w00t:

  • Here is a silly question where do I set for sensitive data? I have scoured Visual Studio and not found it.

  • Really It is very useful and I was facing the same issue . Now it is working fine to me.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply