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 Wont Open

I have just created a package to import data from excel files, and perform some simple sql statements. I saved the package, and when I try to re-open it, I get the following error:

"Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied."

And after I hit th "OK" button, I get the next error msg:

"The selected package cannot be opened. The DTS Designer has been closed"

Is my package completely lost? Can I open it elsewhere, or by changing some properties somewhere? Also, is there something I can do to avoid this in the future?! I hope all my work isn't lost ...

Any help would be greatly appreciated!

BrianClose Enterprise Manager, reopen it, and see if you can read the DTS package. You can still ping the server can't you?|||Thanks for the quick response.

I closed and re-opened enterprise manager, to no avail. I'm working with my localhost and there is no problem pinging. Everything else seems fine, just can't open this package. Any other thoughts?|||It sounds like it got corrupted somehow. I've never actually had this happen. Been doing DTS packages a long time. If you had saved it previously, right-click on the package and look at versions. See if you can look at any of the previous versions.|||I have no idea what's going on, craziness I say. I only had one version when I checked. I've gone through creating these packages all different ways. They save no problem, but I can't re-open them. Any other thoughts? Thanks!sql

Package with File System Task doesn't work without sensitive data with user keys

This problem is a bit weird but I'm just wondering if anybody else experienced this.

I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.

Just wondering if anybody else experienced this problem and of course if there's a way to solve it.

Thanks.Could the security permissions be different on your packages that are failing to be moved from those that are succeeding?|||No, they're all set to EncryptSensitiveWithUserKey.

However, all of the packages involved have their sensitive information set to obtaining them from Indirect Configuration Files. The only way around that I found so far was to put in a dummy connection object, set up an XML file package configuration for that dummy, and deploy it that way.

I'm suspecting that the user key isn't generated at all when all the packages get their configs from Indirect Configuration Files. For some reason, DontSaveSensitive doesn't work either. My original package insists on the presence of a key. Might have something to do with file read/write/modify permissions that come with the key?

I still want to do away with the dummy though.

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 is getting Parent variables too late?...

Hi,

I am sending variables from package to package by configuration parent packages in each package...
I usually set logging to text files in a certain path that should be received from the parent variable... the strange thing is that if i let the variable in the child package with an incorrect path, my package just through an error even if i am passing the variable from the parent with a correct value...

So... my question is... does my package starts execution without any synch over the parent packages? When are they set? How can i be sure to start my logging with the right parent variable settings?

You can try for example to create a package... add a new variable like "A" and "B" set its value to a default string like "I DONT WANT THIS", add an Execute SQL Task that will record the values of "A" variable to a temporary table...

Then use dtexecui.exe to execute the package and set variable "A" to "THIS IS WHAT I NEED" you will see that in the table will appear one record with the "I DONT WANT THIS" value...

I can't understand this...

Also... if i have 100 variables configured to get values from the parent... is the execution synch or asynch?

Best Regards,

Parent variable configuration is applied to child package at runtime after all other package configuration types are applied and loggers initialized. This is a design decision we have made. To workaround this, you have to use other form of package configuration like XML, SQL Server, etc.|||

So, tell me... even if thats the case... why my parent variables are not set when a certain Execute SQL Task gets actually executed, inserting incorrect values into the table? This is not a very good procedure... How can we make sure to be using the right values in the child package, based on the parent values?

Regards,

|||This should have work. Did you see any error/warning messages about package failed to apply any parent variable configuration?|||

Nop, no errors and no warnings... This is defenitly odd..

Regards

|||

I have had problems with this as well...

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

Package is getting Parent variables too late?...

Hi,

I am sending variables from package to package by configuration parent packages in each package...
I usually set logging to text files in a certain path that should be received from the parent variable... the strange thing is that if i let the variable in the child package with an incorrect path, my package just through an error even if i am passing the variable from the parent with a correct value...

So... my question is... does my package starts execution without any synch over the parent packages? When are they set? How can i be sure to start my logging with the right parent variable settings?

You can try for example to create a package... add a new variable like "A" and "B" set its value to a default string like "I DONT WANT THIS", add an Execute SQL Task that will record the values of "A" variable to a temporary table...

Then use dtexecui.exe to execute the package and set variable "A" to "THIS IS WHAT I NEED" you will see that in the table will appear one record with the "I DONT WANT THIS" value...

I can't understand this...

Also... if i have 100 variables configured to get values from the parent... is the execution synch or asynch?

Best Regards,

Parent variable configuration is applied to child package at runtime after all other package configuration types are applied and loggers initialized. This is a design decision we have made. To workaround this, you have to use other form of package configuration like XML, SQL Server, etc.|||

So, tell me... even if thats the case... why my parent variables are not set when a certain Execute SQL Task gets actually executed, inserting incorrect values into the table? This is not a very good procedure... How can we make sure to be using the right values in the child package, based on the parent values?

Regards,

|||This should have work. Did you see any error/warning messages about package failed to apply any parent variable configuration?|||

Nop, no errors and no warnings... This is defenitly odd..

Regards

|||

I have had problems with this as well...

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

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 Fail connections on moving to server

I am working with the ssis packages.I am working in the development enviroment.Once i place them on the server all my connection to text files fails.I dont want to sit and Keep changing with respect to server again and again.

What is the best way to set connection like for example:in my development it can be on e:\data folder

But on server F:\Data

With me changing again and again, can this be set within the package in the development environment.

Please le me know.

Use a package configuration to populate a variable with the location of the files. Then use an expression on the connection manager to combine the file location variable with the hard-coded filenames.
|||

Would you please explain in little more details

Thanks in advance

|||If you aren't familiar with Package Configurations, you should read the topic in Books Online. You can use them to set the connection string on your flat file connection managers, as Jay suggests.

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

Ownership issue problems..... (I think)

I created a database on my laptop, and because I was hoping to keep
the data, I tried detaching it and copying the files to my server and
attaching the database to the server.

I quickly noticed that some of my SPs didn't have rights that I was
expecting them to have. One SP executes a line of dynamic SQL that
updates a record and I get errors saying it doesn't have SELECT
permission or UPDATE permission.

So... I removed the database from the server and scripted the DB.

I logged in to Query Analyzer as 'sa' and ran the create scripts.
The DB lists 'sa' as the owner, and everything in the database lists
'dbo' as the owner, but running those SPs still gives me the same
errors.

Any clues? What can I try to further diagnose this problem?Dynamic SQL breaks the ownership chain. Consequently, users need
permissions on the objects directly referenced in the dynamic SQL statement.

You might peruse http://www.sommarskog.se/dynamic_sql.html for other dynamic
SQL Considerations.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Chris" <cmay@.walshgroup.com> wrote in message
news:44f6e099.0401222324.352e9d5@.posting.google.co m...
> I created a database on my laptop, and because I was hoping to keep
> the data, I tried detaching it and copying the files to my server and
> attaching the database to the server.
> I quickly noticed that some of my SPs didn't have rights that I was
> expecting them to have. One SP executes a line of dynamic SQL that
> updates a record and I get errors saying it doesn't have SELECT
> permission or UPDATE permission.
> So... I removed the database from the server and scripted the DB.
> I logged in to Query Analyzer as 'sa' and ran the create scripts.
> The DB lists 'sa' as the owner, and everything in the database lists
> 'dbo' as the owner, but running those SPs still gives me the same
> errors.
> Any clues? What can I try to further diagnose this problem?|||Thanks Dan!

This really helps.

The article you linked too is really good also, thanks.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<X5aQb.20884$q4.11340@.newsread3.news.atl.earthlink. net>...
> Dynamic SQL breaks the ownership chain. Consequently, users need
> permissions on the objects directly referenced in the dynamic SQL statement.
> You might peruse http://www.sommarskog.se/dynamic_sql.html for other dynamic
> SQL Considerations.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Chris" <cmay@.walshgroup.com> wrote in message
> news:44f6e099.0401222324.352e9d5@.posting.google.co m...
> > I created a database on my laptop, and because I was hoping to keep
> > the data, I tried detaching it and copying the files to my server and
> > attaching the database to the server.
> > I quickly noticed that some of my SPs didn't have rights that I was
> > expecting them to have. One SP executes a line of dynamic SQL that
> > updates a record and I get errors saying it doesn't have SELECT
> > permission or UPDATE permission.
> > So... I removed the database from the server and scripted the DB.
> > I logged in to Query Analyzer as 'sa' and ran the create scripts.
> > The DB lists 'sa' as the owner, and everything in the database lists
> > 'dbo' as the owner, but running those SPs still gives me the same
> > errors.
> > Any clues? What can I try to further diagnose this problem?

Overwrite data file

Is there any way I can overwrite only one data file of the database with a
newer one? Both the files (old and new) belong to the same database but the
newer one has some additional data. Additional data was added in-house and
the same needs to be merged with the database at the client's site. Client's
database in the meatime has been collecting real time data which is stored on
a different file. Any help is appreciated. Thanks.No, SQL Server cannot guarantee a consistent database if it allowed such operation. And even if you
would try to force this, you will end up with a corrupt database. Just think of the correlation
between the data in the data pages and the entries in the transaction log.
So you need to look as some other means to do this, like providing the client with that data in a
file and BULK INSERT that data or similar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:2B90AFBB-FEF7-430A-9D39-0CB787D2E1D3@.microsoft.com...
> Is there any way I can overwrite only one data file of the database with a
> newer one? Both the files (old and new) belong to the same database but the
> newer one has some additional data. Additional data was added in-house and
> the same needs to be merged with the database at the client's site. Client's
> database in the meatime has been collecting real time data which is stored on
> a different file. Any help is appreciated. Thanks.

Overwrite data file

Is there any way I can overwrite only one data file of the database with a
newer one? Both the files (old and new) belong to the same database but the
newer one has some additional data. Additional data was added in-house and
the same needs to be merged with the database at the client's site. Client's
database in the meatime has been collecting real time data which is stored o
n
a different file. Any help is appreciated. Thanks.No, SQL Server cannot guarantee a consistent database if it allowed such ope
ration. And even if you
would try to force this, you will end up with a corrupt database. Just think
of the correlation
between the data in the data pages and the entries in the transaction log.
So you need to look as some other means to do this, like providing the clien
t with that data in a
file and BULK INSERT that data or similar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:2B90AFBB-FEF7-430A-9D39-0CB787D2E1D3@.microsoft.com...
> Is there any way I can overwrite only one data file of the database with a
> newer one? Both the files (old and new) belong to the same database but th
e
> newer one has some additional data. Additional data was added in-house and
> the same needs to be merged with the database at the client's site. Client
's
> database in the meatime has been collecting real time data which is stored
on
> a different file. Any help is appreciated. Thanks.

Overwrite data file

Is there any way I can overwrite only one data file of the database with a
newer one? Both the files (old and new) belong to the same database but the
newer one has some additional data. Additional data was added in-house and
the same needs to be merged with the database at the client's site. Client's
database in the meatime has been collecting real time data which is stored on
a different file. Any help is appreciated. Thanks.
No, SQL Server cannot guarantee a consistent database if it allowed such operation. And even if you
would try to force this, you will end up with a corrupt database. Just think of the correlation
between the data in the data pages and the entries in the transaction log.
So you need to look as some other means to do this, like providing the client with that data in a
file and BULK INSERT that data or similar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:2B90AFBB-FEF7-430A-9D39-0CB787D2E1D3@.microsoft.com...
> Is there any way I can overwrite only one data file of the database with a
> newer one? Both the files (old and new) belong to the same database but the
> newer one has some additional data. Additional data was added in-house and
> the same needs to be merged with the database at the client's site. Client's
> database in the meatime has been collecting real time data which is stored on
> a different file. Any help is appreciated. Thanks.