"login failed for user" message when executing a copy of a working package

I notice when I copy an SSIS package 'A' to a new package 'B', the new package 'B' will generate a "login failed for user" message in the data flow components. To copy I "save copy of Package 'A' as."

Some config info:

Package ProtectionLevel = EncryptSensitiveWithPassword

Connections are Data Sources

Connection strings with password are stored using PackageConfigurations to an SQLServer table. I've verified Package 'A' is in fact using the config table (e.g. it is not using a password or user stored in the package)

Data connectios are all SQLServer Native OLE DB Client

The account is an SQLServer account (not integrated security)

The original Package 'A' works flawlessly and I get success when I test the connections in Package 'B'.

But executing package 'B' I get: The error message I get is: [Connection manager "MyConnection"] Error: 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 'dwuser'.".

Does anyone know why this would occur and/or how to work around it? I saw another thread where a potential workaround is to create a new data flow task and copy all the data flow components to that task. That won't work well for us because the data flow is moderately complex and when you copy and paste it, SSIS completely re-orders the layout.

This is a typical data warehouse ETL setup where there is a master package that executes child packages (e.g. 'A', and 'B' mentioned above) that each perform the ETL for a specific dimension or fact table.

Thanks for any help,

Lee Cascio

Some more info and revelation.

Both child packages will run fine if I execute them independently (outside the parent package that calls them). The problem seems to occur when I try to execute them from a parent package. The OLE Db Connections will then fail on validation on both packages with the "login failed" message listed above in addition to the following: [MyTable [1]] Error: The AcquireConnection method call to the connection manager "MyDataSourceConnection" failed with error code 0xC0202009.

An interesting observation is if I disable Package 'B' in the parent package, Package 'A' will execute without flaw with the parent package. If I disable Package 'A' and enable Package 'B', Package 'B' will still have the problem. Another note, the parent package also uses the data source connections.

It appears we have some kind of problem with multiple packages using the same data source. Does anyone know if there are any limitations to this or if it requires special transaction settings?

Thanks,

Lee Cascio

|||I resolved this issue by switching to XML configurations instead of SQL Server table configurations. I'm still not sure of the exact cause but would have been a problem with credentials getting to the SQL Server Table for the configurations (which include connection strings).

<NEWBIE>Move database from case sensitive to case insensitive servers

I know there are several ways to copy information from one machine to anothe
r, and from my research in the archives every approach has its adherents. I'
d just like a better idea of which is the best method for my particular case
.
There is a database residing on a remote server that has been set up as case
sensitive. I need to move the database to my own departmental server, which
is case insensitive (and the app vendor prefers this). Which is the best me
thod to move the system, wh
ere best means first, no errors and second, little effort on my part.
Right now I'm leaning toward detaching the database on the other machine and
copying its mdf and ldf files to the local machine and reattaching it. Will
the case sensitivity cause problems?
Thanks,
J"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1C256B86-16BD-4933-B081-264AC510F814@.microsoft.com...
> I know there are several ways to copy information from one machine to
another, and from my research in the archives every approach has its
adherents. I'd just like a better idea of which is the best method for my
particular case.
> There is a database residing on a remote server that has been set up as
case sensitive. I need to move the database to my own departmental server,
which is case insensitive (and the app vendor prefers this). Which is the
best method to move the system, where best means first, no errors and
second, little effort on my part.
> Right now I'm leaning toward detaching the database on the other machine
and copying its mdf and ldf files to the local machine and reattaching it.
Will the case sensitivity cause problems?
>
Are you running SQL Server 2000? Then that should work for you. Another
option, create the database on the target with the collation you need, then
run a RESTORE. For more information, see BOL "SQL Server Collation
Fundamentals".
Steve|||Do you want the database to be case sensitive or case insensitive on the new
server?
Watch out having different collations for you system databases vs. your
application database(s). If the app wasn't tested for this, you might get
error messages from the queries it submits (collation conflict messages).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1C256B86-16BD-4933-B081-264AC510F814@.microsoft.com...
> I know there are several ways to copy information from one machine to
another, and from my research in the archives every approach has its
adherents. I'd just like a better idea of which is the best method for my
particular case.
> There is a database residing on a remote server that has been set up as
case sensitive. I need to move the database to my own departmental server,
which is case insensitive (and the app vendor prefers this). Which is the
best method to move the system, where best means first, no errors and
second, little effort on my part.
> Right now I'm leaning toward detaching the database on the other machine
and copying its mdf and ldf files to the local machine and reattaching it.
Will the case sensitivity cause problems?
> Thanks,
> J

<NEWBIE>Move database from case sensitive to case insensitive servers

I know there are several ways to copy information from one machine to another, and from my research in the archives every approach has its adherents. I'd just like a better idea of which is the best method for my particular case.
There is a database residing on a remote server that has been set up as case sensitive. I need to move the database to my own departmental server, which is case insensitive (and the app vendor prefers this). Which is the best method to move the system, where best means first, no errors and second, little effort on my part.
Right now I'm leaning toward detaching the database on the other machine and copying its mdf and ldf files to the local machine and reattaching it. Will the case sensitivity cause problems
Thanks
J"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1C256B86-16BD-4933-B081-264AC510F814@.microsoft.com...
> I know there are several ways to copy information from one machine to
another, and from my research in the archives every approach has its
adherents. I'd just like a better idea of which is the best method for my
particular case.
> There is a database residing on a remote server that has been set up as
case sensitive. I need to move the database to my own departmental server,
which is case insensitive (and the app vendor prefers this). Which is the
best method to move the system, where best means first, no errors and
second, little effort on my part.
> Right now I'm leaning toward detaching the database on the other machine
and copying its mdf and ldf files to the local machine and reattaching it.
Will the case sensitivity cause problems?
>
Are you running SQL Server 2000? Then that should work for you. Another
option, create the database on the target with the collation you need, then
run a RESTORE. For more information, see BOL "SQL Server Collation
Fundamentals".
Steve|||Do you want the database to be case sensitive or case insensitive on the new
server?
Watch out having different collations for you system databases vs. your
application database(s). If the app wasn't tested for this, you might get
error messages from the queries it submits (collation conflict messages).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1C256B86-16BD-4933-B081-264AC510F814@.microsoft.com...
> I know there are several ways to copy information from one machine to
another, and from my research in the archives every approach has its
adherents. I'd just like a better idea of which is the best method for my
particular case.
> There is a database residing on a remote server that has been set up as
case sensitive. I need to move the database to my own departmental server,
which is case insensitive (and the app vendor prefers this). Which is the
best method to move the system, where best means first, no errors and
second, little effort on my part.
> Right now I'm leaning toward detaching the database on the other machine
and copying its mdf and ldf files to the local machine and reattaching it.
Will the case sensitivity cause problems?
> Thanks,
> J