Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm. |||

Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.

I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.

Dan

|||No problem - I'm glad you were able to resolve it.