Package xml config files used in child packages?

I have a package that executes 3 other packages in it. From the command line I pass in which config file to use (just contains db/seerver) does this setting get carried over into the child packages as well?

If you are using the Execute Package task to execute child packages it looks like you bumped into the current SSIS limitation. Take a look at the following posting:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2078371&SiteID=1

Regards,

Yitzhak

|||

Seems like that problem is to do with setting variables at the command line in child packages?

Im just curious if I use a config file that specifies a connection to the database to use, whats the best way to pass that connection on to the child packages?

|||

You can pass information through the parent package variable (PPV) configurations. However, PPVs are applied after all other configurations, so this can cause some problems. For example, if you are trying to pass the connect string to a database to use the SQL Server configurations, the PPV isn't applied until after the SQL Server configurations are already done.

Another way to accomplish this is to use an Execute Process task instead of the Execute Packge task. Then you can call DTEXEC, and pass along whatever connection strings you want to use.

Finally, you can use a Script Task to load the child package from the SSIS object model, set values in the package, and launch it from that.

Package xml config files used in child packages?

I have a package that executes 3 other packages in it. From the command line I pass in which config file to use (just contains db/seerver) does this setting get carried over into the child packages as well?

If you are using the Execute Package task to execute child packages it looks like you bumped into the current SSIS limitation. Take a look at the following posting:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2078371&SiteID=1

Regards,

Yitzhak

|||

Seems like that problem is to do with setting variables at the command line in child packages?

Im just curious if I use a config file that specifies a connection to the database to use, whats the best way to pass that connection on to the child packages?

|||

You can pass information through the parent package variable (PPV) configurations. However, PPVs are applied after all other configurations, so this can cause some problems. For example, if you are trying to pass the connect string to a database to use the SQL Server configurations, the PPV isn't applied until after the SQL Server configurations are already done.

Another way to accomplish this is to use an Execute Process task instead of the Execute Packge task. Then you can call DTEXEC, and pass along whatever connection strings you want to use.

Finally, you can use a Script Task to load the child package from the SSIS object model, set values in the package, and launch it from that.

Package not picking up latest Config File changes

Hi,

I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.

For example, if the source file is "C:\Files\source.txt" and this is changed to "C:\Files\source123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:\Files\source.txt".

Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.

Any ideas ?

Thanks, Richie.

Can you check if you file connection has any PropertyExpression that changes the ConnecftionString back to "C:\Files\source.txt"?

Thanks,
Ovidiu Burlacu

|||

Hi,

There aren't any expressions for the file connections. The way I have the package set up is like this:

STAGE_PACKAGE - Takes the data from a flat file and inserts it in to a staging table.

TRANS_PACKAGE - Takes the data from the staging table and in to the database.

PARENT_PACKAGE - First calls the STAGE_PACKAGE then the TRANS_PACKAGE. An XML config file has been created to capture the location of the file to pick up and the server name to connect to. These are then passed to the STAGE and TRANS packages via parent variables.

I've tried deleting the PARENT_PACKAGE and recreating it, but exactly the same happens.

Richie.

|||I ran into this problem with Parent Package Variables, and switched to Environment Variables instead. You might want to give this a try.|||

This is a known problem of parent package variables. Try to use other venues, like Enviroment variables or registry settings if possible.

Thanks,
Ovidiu Burlacu

|||

That is issue was already documented a while ago; so if somebody is affected by this, go there and vote:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

Rafael Salas

|||

Thanks for the replies.

As a work around to this, I decided to do away with the parent variables and set up a config file for the children. I thought that when the parent package is executed, it would call the first child which would then call the config file. But it didn't.

When I run the child package it picks up the config file, but when I run it from a parent package it doesn't pick it up. Is this the same issue as with the parent variables ?

Thanks,

Richie.

|||Anyone ?|||

I am not sure how your packages are set up; but If any of the configuration managers in children packages rely on the value to be received from the parent package to get configured; that simply will no work because the issue I mentioned in my previous post.

How are you running the packages? BIDS, SQL AGENT, DTEXEC?

Take a look to the execution progress to see if there is any warning about configuration no taking place.

Rafael Salas

Package not picking up latest Config File changes

Hi,

I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.

For example, if the source file is "C:\Files\source.txt" and this is changed to "C:\Files\source123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:\Files\source.txt".

Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.

Any ideas ?

Thanks, Richie.

Can you check if you file connection has any PropertyExpression that changes the ConnecftionString back to "C:\Files\source.txt"?

Thanks,
Ovidiu Burlacu

|||

Hi,

There aren't any expressions for the file connections. The way I have the package set up is like this:

STAGE_PACKAGE - Takes the data from a flat file and inserts it in to a staging table.

TRANS_PACKAGE - Takes the data from the staging table and in to the database.

PARENT_PACKAGE - First calls the STAGE_PACKAGE then the TRANS_PACKAGE. An XML config file has been created to capture the location of the file to pick up and the server name to connect to. These are then passed to the STAGE and TRANS packages via parent variables.

I've tried deleting the PARENT_PACKAGE and recreating it, but exactly the same happens.

Richie.

|||I ran into this problem with Parent Package Variables, and switched to Environment Variables instead. You might want to give this a try.|||

This is a known problem of parent package variables. Try to use other venues, like Enviroment variables or registry settings if possible.

Thanks,
Ovidiu Burlacu

|||

That is issue was already documented a while ago; so if somebody is affected by this, go there and vote:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

Rafael Salas

|||

Thanks for the replies.

As a work around to this, I decided to do away with the parent variables and set up a config file for the children. I thought that when the parent package is executed, it would call the first child which would then call the config file. But it didn't.

When I run the child package it picks up the config file, but when I run it from a parent package it doesn't pick it up. Is this the same issue as with the parent variables ?

Thanks,

Richie.

|||Anyone ?|||

I am not sure how your packages are set up; but If any of the configuration managers in children packages rely on the value to be received from the parent package to get configured; that simply will no work because the issue I mentioned in my previous post.

How are you running the packages? BIDS, SQL AGENT, DTEXEC?

Take a look to the execution progress to see if there is any warning about configuration no taking place.

Rafael Salas

Package loses configuration files!

I created a project with multiple packages. I created a global config file that all packages reference. I also created a config file for each package that contains the package specific settings. I checked it all into TFS. I went to a different machine and checked it all out. Upon loading a package into the IDE I got the following 3 warnings.

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "MT_LSE_PROD_StageLoad.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "Environment.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Any ideas? And yes, the files *ARE* there! I tried building the project and it builds. I also tried doing a deployment build and that too built. I am so confused!!!

Are you using SQL2005/Express SP1? As I know there used to be a known issue with SP1: if you use configuration file to deploy SSIS package, the referenced file names in the configuraiton file will be change to lowercase. Since XML file is case-sensitive, the files can't be found if the name does not exactly match. Not sure whether there is any hotfix available now.|||We are using SQL 2005 Enterprise. I believe it is SP1 as well. Will see if naming everything in lower case only fixes it up. Thanks.sql

Package fails to execute as a scheduled job

I've managed to get the basics coded into my package. Nothing fancy, it is all quite literally embedded inside the package. No config files, parameters, variables, etc. I've imported the package into SSIS. I can right click on the package and select Run Package. The package fires off, validates, runs through the entire process, and completes successfully. However, when I set up the package to run as a scheduled task, it fails almost immediately with the following output.

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 3:28:07 AM
Error: 2007-04-25 03:28:07.41
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSroperty" 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: 2007-04-25 03:28:11.36
Code: 0xC001602A
Source: Package Connection manager "FTP Connection Manager"
Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed
.
End Error
Error: 2007-04-25 03:28:11.36
Code: 0xC002918F
Source: Card10 FTP Task
Description: Unable to connect to FTP server using "FTP Connection Manager".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:28:07 AM
Finished: 3:28:11 AM
Elapsed: 4.297 seconds

Help.......

Hi Michael,

There was a discussion of a similar error message here recently. What is the ProtectionLevel property setting for the package?

Andy

|||

There is also a KB article where they describe 4 diffrent methods for schedulling the package:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step:

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

Package Configurations - Using One config file for Multiple Packages

Hi All,

I am working on a project currently where we have many SSIS packages and we want to minimize the number of config files to be used. What I was thinking was using one master config file which will have Server/Database info which will be used by all the packages. Now the thing is there are multiple packages which are being executed from within a master package and I was wondering if this will be an issue. Is there anything else that I will have to keep in mind? Any help is appreciated.

Thanks

If my memory serves correctly then you will get lots of warnings about references in your config file to properties that don't exist in your package. But it should still execute OK.

-Jamie

|||Thanks Jamie

Package configuration help !

hi,

I have a package that executes nicely.
When I enable "Package Configuration", create an XML config file and only select the Connection Managers (2 file connection managers and 1 data source), I get the following error (when executing the package in debug mode from designer):
Information: 0x40016041 at MyPackage: The package is attempting to configure from the XML file "C:\src\myconfig.dtsConfig".
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Data Flow Task - Extract my_file_src, DTS.Pipeline: Validation phase is beginning
Error: 0xC0202009 at MyPackage, Connection manager "MyConnMgr": An OLE DB error has occurred. Error code: 0x80040E4D
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'."
Error: 0xC020801C at Data Flow Task - Extract my_file_src, Lookup - column1 from table1 [6012]: The AcquireConnection method call to the connection manager "MyConnMgr" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task - Extract my_file_src, DTS.Pipeline: component "Lookup - column1 from table1" (6012) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task - Extract my_file_src, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task - Extract my_file_src: There were errors during task validation.
SSIS package "MyPackage.dtsx" finished: Failure.
The program '[2136] MyPackage.dtsx: DTS' has exited with code 0 (0x0).
I guess I am making some basic mistake.
Somebody please throw some light.

Thanks in advance.
NiteshYou might open up the package configuration file (C:\src\myconfig.dtsConfig"), and see if it has an entry for username "sa" in it -- the error you posted suggests that it has an incorrect password for that account.
|||Also, Note that the configuration message is simply an informational message that let's you know that the package is getting configured. It's not an error or warning.
Look at the open package and verify that the password is correct for the connections.
K

Package config errors - VS_NEEDSNEWMETADATA

Hi everyone,
I am having issues with using package configurations when just chaging the ServerName and InitialCatalog vars of a Connection Manager. I have DelayValidation = True on the Data Flow Task that is erroring out, but I am still recieving a VS_NEEDSNEWMETADATA error before the package is executed. The 2 boxes have identical tables I'm trying to access, although 1 is 2005 and the other is 2000. Any thoughts? I thought that package configs were supposed to be quick and painless?
Thanks,
Adrian
NeedsNewMetadata means that the component is detecting a difference between the table you initialized it with and the one you are running against, which is not surprising if you are changing SQL Server versions. Configurations are supposed to be quick and painless if used in a quick and painless way. For example you wouldn't expect them to work if you switched between SQL Server and DB/2 for the database (at least I hope not . Well 2000 and 2005 are quite different and this difference is manifesting itself by the component determining it needs a metadata refresh.

HTH,
Matt|||I would have that impression if using a non-Microsoft db product. However since the datatypes were the same and the connection manager type(oledb) worked to connect to both I assumed there would be no problems. But we know what they say about someone who assumes huh? If that is to be expected then I suppose there is nothing that can be done.
Thanks,
Adrian
|||Can you verify that the column names are identical as well as their types (and sizes)? In general we don't cause this type of failure based on the database back end but the component is determining that there is some difference between the metadata if has for the table and the metadata that the table being attached to is presenting to is. All I meant was that when switching to different databases it is easier for the mismatch to occur. I am not certain that was clear when rereading my post.

Thanks,
Matt|||I did verify the datatypes and sizes. I accepted 2005's table defaults in all other areas, so maybe something has changed there that I need to check. I will post back if those are identical and there are still problems.
Thanks,
Adrian
|||The column names are also very important as that is how we determine a metadata match. So if the names change then you will 100% of the time get this problem even if the types and sizes are identical.

Thanks,
Matt|||Are the column name matches case-sensitive? If so, there's my problem.
|||Yes, the check is case-sensitive. This is so we don't miss catching a bad case when a database is case-sensitive for column names.

Thanks,
Matt|||

Although I have seen this thread at a very later date, It helped me a lot . I was facing issues with VS_NEEDSNEWMETADATA error and the reason was because of case sensitive column names.

Thanks a lot

|||

We worked around this by setting the source to a SQL command rather than a Table/View. Do that in the Source Editor "Data Access Mode" box. Then type in your query... Select Column1 from Table1. No matter the case of Column1 on the back end, it woudl succeeds in our case.

This workaround keeps validation from being so strict on the column names.

Since our back end was case-insensitive SQL, our back end can handle the mixed cases of columns, so your SQL Command can succeed against the data source irrespective of column case, and so SSIS is not aware that the back end column metadata doesn't match the case of the SSIS package column labels.

Package config errors - VS_NEEDSNEWMETADATA

Hi everyone,
I am having issues with using package configurations when just chaging the ServerName and InitialCatalog vars of a Connection Manager. I have DelayValidation = True on the Data Flow Task that is erroring out, but I am still recieving a VS_NEEDSNEWMETADATA error before the package is executed. The 2 boxes have identical tables I'm trying to access, although 1 is 2005 and the other is 2000. Any thoughts? I thought that package configs were supposed to be quick and painless?
Thanks,
Adrian
NeedsNewMetadata means that the component is detecting a difference between the table you initialized it with and the one you are running against, which is not surprising if you are changing SQL Server versions. Configurations are supposed to be quick and painless if used in a quick and painless way. For example you wouldn't expect them to work if you switched between SQL Server and DB/2 for the database (at least I hope not . Well 2000 and 2005 are quite different and this difference is manifesting itself by the component determining it needs a metadata refresh.

HTH,
Matt|||I would have that impression if using a non-Microsoft db product. However since the datatypes were the same and the connection manager type(oledb) worked to connect to both I assumed there would be no problems. But we know what they say about someone who assumes huh? If that is to be expected then I suppose there is nothing that can be done.
Thanks,
Adrian
|||Can you verify that the column names are identical as well as their types (and sizes)? In general we don't cause this type of failure based on the database back end but the component is determining that there is some difference between the metadata if has for the table and the metadata that the table being attached to is presenting to is. All I meant was that when switching to different databases it is easier for the mismatch to occur. I am not certain that was clear when rereading my post.

Thanks,
Matt|||I did verify the datatypes and sizes. I accepted 2005's table defaults in all other areas, so maybe something has changed there that I need to check. I will post back if those are identical and there are still problems.
Thanks,
Adrian
|||The column names are also very important as that is how we determine a metadata match. So if the names change then you will 100% of the time get this problem even if the types and sizes are identical.

Thanks,
Matt|||Are the column name matches case-sensitive? If so, there's my problem.
|||Yes, the check is case-sensitive. This is so we don't miss catching a bad case when a database is case-sensitive for column names.

Thanks,
Matt|||

Although I have seen this thread at a very later date, It helped me a lot . I was facing issues with VS_NEEDSNEWMETADATA error and the reason was because of case sensitive column names.

Thanks a lot

|||

We worked around this by setting the source to a SQL command rather than a Table/View. Do that in the Source Editor "Data Access Mode" box. Then type in your query... Select Column1 from Table1. No matter the case of Column1 on the back end, it woudl succeeds in our case.

This workaround keeps validation from being so strict on the column names.

Since our back end was case-insensitive SQL, our back end can handle the mixed cases of columns, so your SQL Command can succeed against the data source irrespective of column case, and so SSIS is not aware that the back end column metadata doesn't match the case of the SSIS package column labels.

Package config errors - VS_NEEDSNEWMETADATA

Hi everyone,
I am having issues with using package configurations when just chaging the ServerName and InitialCatalog vars of a Connection Manager. I have DelayValidation = True on the Data Flow Task that is erroring out, but I am still recieving a VS_NEEDSNEWMETADATA error before the package is executed. The 2 boxes have identical tables I'm trying to access, although 1 is 2005 and the other is 2000. Any thoughts? I thought that package configs were supposed to be quick and painless?
Thanks,
Adrian
NeedsNewMetadata means that the component is detecting a difference between the table you initialized it with and the one you are running against, which is not surprising if you are changing SQL Server versions. Configurations are supposed to be quick and painless if used in a quick and painless way. For example you wouldn't expect them to work if you switched between SQL Server and DB/2 for the database (at least I hope not . Well 2000 and 2005 are quite different and this difference is manifesting itself by the component determining it needs a metadata refresh.

HTH,
Matt|||I would have that impression if using a non-Microsoft db product. However since the datatypes were the same and the connection manager type(oledb) worked to connect to both I assumed there would be no problems. But we know what they say about someone who assumes huh? If that is to be expected then I suppose there is nothing that can be done.
Thanks,
Adrian
|||Can you verify that the column names are identical as well as their types (and sizes)? In general we don't cause this type of failure based on the database back end but the component is determining that there is some difference between the metadata if has for the table and the metadata that the table being attached to is presenting to is. All I meant was that when switching to different databases it is easier for the mismatch to occur. I am not certain that was clear when rereading my post.

Thanks,
Matt|||I did verify the datatypes and sizes. I accepted 2005's table defaults in all other areas, so maybe something has changed there that I need to check. I will post back if those are identical and there are still problems.
Thanks,
Adrian
|||The column names are also very important as that is how we determine a metadata match. So if the names change then you will 100% of the time get this problem even if the types and sizes are identical.

Thanks,
Matt|||Are the column name matches case-sensitive? If so, there's my problem.
|||Yes, the check is case-sensitive. This is so we don't miss catching a bad case when a database is case-sensitive for column names.

Thanks,
Matt|||

Although I have seen this thread at a very later date, It helped me a lot . I was facing issues with VS_NEEDSNEWMETADATA error and the reason was because of case sensitive column names.

Thanks a lot

|||

We worked around this by setting the source to a SQL command rather than a Table/View. Do that in the Source Editor "Data Access Mode" box. Then type in your query... Select Column1 from Table1. No matter the case of Column1 on the back end, it woudl succeeds in our case.

This workaround keeps validation from being so strict on the column names.

Since our back end was case-insensitive SQL, our back end can handle the mixed cases of columns, so your SQL Command can succeed against the data source irrespective of column case, and so SSIS is not aware that the back end column metadata doesn't match the case of the SSIS package column labels.