Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem with running SSIS packages Expand / Collapse
Author
Message
Posted Thursday, June 12, 2008 1:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 21, 2009 1:34 PM
Points: 13, Visits: 63
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!


Post #516217
Posted Friday, June 13, 2008 3:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
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
Post #516516
Posted Friday, June 13, 2008 7:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 21, 2009 1:34 PM
Points: 13, Visits: 63
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.
Post #516669
Posted Friday, June 13, 2008 7:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
-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
Post #516687
Posted Friday, June 13, 2008 7:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 21, 2009 1:34 PM
Points: 13, Visits: 63
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.
Post #516707
Posted Friday, June 13, 2008 8:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
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
Post #516728
Posted Friday, June 13, 2008 8:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 21, 2009 1:34 PM
Points: 13, Visits: 63
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!
Post #516754
Posted Tuesday, July 1, 2008 3:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 16, 2012 6:12 AM
Points: 5, Visits: 49
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
Post #526426
Posted Wednesday, July 2, 2008 6:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 21, 2009 1:34 PM
Points: 13, Visits: 63
Data Source=yourDatabasename.world;User ID=yourUserid;Password=yourPassword;Persist Security Info=True;Unicode=True;
Post #527260
Posted Friday, July 4, 2008 3:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 16, 2012 6:12 AM
Points: 5, Visits: 49
Thanks !
it works for me too
Post #528562
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse