Losing Config File Between Child Packages And *$##@!- Passwords!
Feb 5, 2007
I have a parent package that uses Execute Package Task against three child packages. They are all setup to use the same configuration file, and the packages are all located in the same folder. Purpose of the configuation is to use the same connection strings for each child package.
I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.
Also, the password-removal issue is killing me in development. Everytime I touch the config. file, it removes the password from the connection string. I have "protection level" set to "don't save sensitive" - I'd appreciate help with this too.
My packages are now in the filesystem. I have given the parent package a password in the properties window, saved the package and moved it to the run location.
I am using the SQL Job Agent to schedule the execution. My step command line reads:
dtexec /FILE "C:Pathparentpackage.dtsx" /De mypass /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Do the the 20 or so child and grandchild packages require passwords to run this way?
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?
I would like to use one configuration file for multiple SSIS packages. How I want to do this is to save the connection string of my production server with the initial database field excluded and set that per package file. I am able to read in the configuration file into 2 different packages without issue but I cannot see how to extract the connection string into a ODBC Database Connection object in order to set the initial database. Is there some way I am not thinking of to do this, either through the designer or programatically?
Thanks for any help you all can provide! I am so n00b to SSIS.
I have several packages that all use the same DTSConfig file to map the OLEDB connections. This works fine in the development environment, however when i run a rebuild with Deployment set to true i get the error
Error 75 System.ApplicationException: Could not copy file "C:Dev_TFSTowergateTowergate.BdwTowergate.Bdw.Ssis.TransformPackagePath.dtsConfig" to the deployment utility output directory "C:Dev_TFSTowergateTowergate.BdwTowergate.Bdw.Ssis.TransforminDeployment". ---> System.IO.IOException: The file 'C:Dev_TFSTowergateTowergate.BdwTowergate.Bdw.Ssis.TransforminDeploymentPackagePath.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) --- End of inner exception stack trace --- at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow)
The DTSConfig file is included in the project under Misc in the development studio as all developers need to have access to the same config file.
The net result is that the MSI deployment package doesnt get built so we cannot do an automated delivery of the data.
Is there a work round or a setting that i can implement to remove the error?
I've read that a single config file can be used across multiple packages, but am unsure if it applies to what I'm trying to achieve. As an example, I have 2 packages, PackageA and PackageB. Each package has a connection manager defined - ManagerA and ManagerB and I want a single package configuration file to contain the connection string for each manager.
I can define the package configuration of PackageA with no problems. I choose XML file, define the filename and add the connection string to the config.
I then open PackageB Package Configuration and point it to the existing config file, IS tells me the file already exists and allows me to choose the option to "Reuse Existing". I then supply a configuration name and the wizard finishes, without letting me expose any properties. At this point, the configuration file still contains config details from PackageA.
If I then choose to Edit the package configuration in PackageB, I get a warning saying the config values in the config file (relating to PackageA) cannot be found, and the wizard continues. The subsequent PackageB properties which I add overwrite the existing PackageA properties.
So it would appear that what I'm trying to do cannot be done, and I need a separate configuration file for each package?
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.
This seems like a no brainer, but it's driving me nuts. I want one XML file for the entire solution. There are multiple packages in the solution which have different Connections in the conntion Managers. The packages share some connection names, and some are unique to the package.
Example:
LoadData.dtsx would have a source database connection named (SourceDB_OLEDB) and a oledb connection (DataWarehouse_OLEDB).
LoadDataMart.dtsx would use the same name for the (DataWarehouse_OLEDB) connection and have another oledb connection (DataMart_OLEDB)
I want one XML config file that has all the connection strings, but the problem is that the LoadDataMart.dtsx will throw an error:
Error 1 Error loading LoadDataMart.dtsx: The connection "SourceDB_OLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found. c:ssisLoadDataMart.dtsx 1 1
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
Anybody find a sound approach to being able to store DataSource connection information in a configuration and be able to access a Data Source that requires a password for login?
I have an SQL Server where only a group of sysadmins have access to install DTSX packages. Those DTSX packages are developed by another team that does not have access to the production SQL Server. They use their own SQL Server.
In order to make it as simple as possible to install these packages by the sysadmins, I suggested the use of configuration files. The files are associated with the job that executes the package and all that has to be done to install the package is copy it to the file system or import it into the SQL Server. Developers use their configuration file, sysadmins user theirs. Nothing new here.
The problem is that some of the packages have to access some old systems and we cannot use integrated authentication. We have to use SQL authentication and therefore specify a user account and password in the connection string. If this is stored in the configuration file, it is available in clear text! If I store the configuration in the package itself using ProtectSensitiveWithPassword protection level, the sysadmins will have to edit every DTSX package to reset the connections to the production environment (the developers always send them with their development configurations) and I don't want that. If I store it in a SQL Server database, it seems the sysadmins also have to edit the package to point the package configuration to the correct database and set the configuration filter.
Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)
Is there any other way to do this? Am I doing something wrong?
I have a parent package which calls a number of child packages. Occasionally, I have one child package which starts up and appears to finish but the parent package never gets notification that the child has finished. If I view this in Management Studio's in Integration Services, I can view the packages that are running and only the parent package is running. Because it never gets control back, the next child package will never get started and the parent package will continue to run and never finish.
I searched for an answer for this, and found a few good threads, but none of them seem to be doing quite the same thing I'm trying to do.
I have two packages, parent and child. Parent does basic file manipulation -- encrypting/decrypting, moving from server to server, backing up to archive, pulling/pushing to external server via FTP, etc. It is completely dumb to what child does. Child is the guts of the data work -- the ETL package.
The goal is to have this one parent package be used by several ETL child packages. But not only can parent use different children (depending on which config is used when executing parent), but child can also handle different tasks, depending on a configuration it uses.
Let me break it down further.
Three packages:
ParentPackage - Used by all jobs ChildPackage1 - Used for processing orders ChildPackage2 - Used for processing inventory
Four configurations (thus four jobs):
Job1Configuration - Used for processing orders for ClientA Job2Configuration - Used for processing orders for ClientB Job3Configuration - Used for processing inventory for ClientA Job4Configuration - Used for processing inventory for ClientB
Job1Configuration is set up to tell ParentPackage to use ChildPackage1, and to provide ChildPackage1 w/ various data for ClientA -- and so forth.
To my understanding, there is no passthrough of the config data from parent to child. A child package doesn't "inherit" or otherwise receive the information from the parent package, unless explicit variables are set up. This won't work for us, because ParentPackage is dumb of any data that the child packages may need. In other words, we don't want to set up ParentPacakge with every possible variable that every child package may need.
Also, I'm not aware of a way of setting up a job to provide a child package directly w/ a package configuration.
Is there any way to do what I'm trying to accomplish?
If I didn't explain something clearly, let me know and I will try to clarify.
I was hoping someone might be able to clarify how configuartions work with child packages. My process has 3 levels of packages. The main packages called two child packages each of which calls more child packages.
I'm using Configurations to pass variables from parent packages to child packages. But each level of packages contains Data Flow Source/Destination connections. I was planning on having only 1 XML Configuration file in the main package that would allow the data connections for the main package to be configured for the target enviornment. When the job is scheduled I will be able to point a configuration file for use with the main packages. But will that configuration file be applied to all child packages that will run? All of the Connections Managers have the same name so I'd like to make the change in one file.
If not, am I forced to define a parent/child variable to pass the Connection String from the parent to each of the child packages? Use an expression to set the Connection String dynamically? This seems like a lot of extra work, so I'd really like to see if there is a way to do it with an XML configuration file. Even if I have to definte the same config file for each of my 20+ packages.
I have a fairly simple SSIS project that has nested parent-child packages. I am trying to find the best way to manage the connections strings so as to make the package portable across machines and environments. Currently there is one "master" package which calls 6 child packages. 1 of these child package calls 3 child packages of its own.
For the database connections, I've settled on creating a standardized .dtsConfig file for each server/login. This is a relatively small number (intially 8) that I don't expect to grow much.
I've taken a different approach for the file-system connections used by Execute Package components that call the child packages. For each package that has child packages, I store all the connection strings (paths) to the child packages in a single .dtsConfig file. This works well for the top-level "master" package where I can pass in the .dtsConfig file (that has the paths to the child packages) as a run-time option in the Execute Package Utility.
However, this approach seems to fall apart for the 2nd generation package that in turn call 3rd generation packages because I don't know how to get the .dtsConfig file (with the 3rd generation .dtsx package paths) path to this downstream dtsx package.
Though I'm sure there are others, the only two solutionsI can think of now are (a)don't nest packages beyond 1 parent/child relationship -- not really an option or (b)Store the path of .dtsConfig files for each .dtsx package as an environment variable on each machine. This option is unappealing because it would require adding an environment variable for every .dtsx package that has child packages. I don't think it would take long for this to grow into a large number, that would make managing environment variables cumbersome.
So far my experience with SSIS has been that there was a simple solution for each scenario I had. So this hoop jumping I'm going through seems to indicate I am just missing something.
hello again!, this time I'm trying to run a Master Package from the SQL Server Agent but I can't set relative paths to the connections for all the child packages that the master package contains. It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.
Is there any property in the SQL Server Agent or mayby a workaround to solve this?
It would appear that if a Child package is called more than once from a Parent using the 'Execute Package' task, then after the first execute the Parent Package Variables are not applied to child package. I.E we build dimensions in a master database and these are then loaded to a number of topic specific datamarts. We simply pass Parent variables to the child that hold source & target connection strings, the first time the package is called the correct database is accessed, subsequent Executes ignore the variables and use the original values. Manipulating the (our) event queue to run the package once results in the correct behaviour
Are packages cached when they are called from a Parent? if so is there a flag that I have missed to force a reload each time a child is executed?
This has just become a big problen for us so any guidance would greatly appreciated.
HI, I have some parent parent packages that calls child packages. When I added a bunch of packages, I faced the buffer out of memory error. I then decided to set the child packages property ExecuteOutOfProcess to TRUE. I noticed that the execution time is longer now. Is this a good practice to set the ExecuteOutOfProcess to true? If so, is it normal that the execution time is longer?
I have around 10 packages for dim and fact Table load. Using deployment Utility i create setup and put the packages in to test environment. Now my requirement is call all the packages in certain order from single package. Using Execute Package Task i could call only one package. how to achive this in deployment?
I have a Business Intelligence project containing 4 packages. One of the packages is a parent package that just runs the other 3 packages sequentially (passing in variables via Package Configurations).
When I set the properties of the Execute Package tasks in the parent package, it seems that I can only select SQL Server or File System locations for the child packages.
What I want to do is for the parent to run the packages in the Visual Studio Project so that I can debug the whole process. I don't want to be bothered deploying the child packages so that I can run them from the parent, at least not while I am debugging the whole process.
How do I get the children to run within Visual Studio as well as the parent? I can't for the life of me find anything on the MSDN about this.
In all packages they make connections to a SQL 2005 db which sits on the same instance as which the packages are being deployed. In the Child packages these connections have the connection string set as a package property variable in Package configs and in the Parent package this variable exists correctly. This variable in the Parent is itself defined in a PackageConfig which uses SQL Server as the holder for the configuration.
When I deploy the packages the connections to in the child packages fail, one connection by timing out, another with an acquire connection failure.
I have set the DelayValidation to false on all connections but it made no difference. I have also increased the timeout to 180s but again still a failure. When I deploy the parent package on its own it is successful, however when the child packages are deployed on their own these connections again fail to validate due to a timeout and connection failure, yet they are using the same connections defined in the parent.
The child packages are no larger than other single packags that I have deployed successfully with the same connection to the same server.
Is there anything special that I should be aware of regarding deployment of parent and child packages.
I have one package that executes 4 child packages (5 total). All 5 packages are set to log information using a connection string with a variable set at runtime for the location. The child packages use a parent variable to get the log connection string from the parent package.
The parent package logs fine, but a strange behavior occurs with the child packages.
The child packages all log data to the location used in the configuration file variable during runtime (like it's supposed to). However, I would get an error right before the first child package finishes execution saying path not found. Just for giggles, I created the folder I use in my development environment on the production environment. The error goes away, but the log file is created in that folder with no data in it. Subsequently, the log file with the data is created in the location set in the configuration file. Now I have two log files!!!!
I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.
Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?
Hi, I have been developing SSIS packages and recently I found a strange thing happening. When executing chilld packages from the parent packages the child packages dont open automatically in the Package Designer window. Hence I cant monitor the places where the errors/ row movement have happened in child pkgs without manually opening the them first and then executing the parent.
I've found this problem that when I change settings in my configuration file it does not automatically apply to all child packages which uses the same configuration file if run from a job in SQL Server Agent. I need to open the package and save it again from BIDS. I use one "load group" package to execute all other packages.
Is there a way from the job configuration to set a setting so the package allways will have the newest configuration?
I've been executing a package and passing a parent variable to the child using package configurations but I'd now like to do this using a script task. The script task would then programmatically load the package and execute it.
How do I do this and still use the parent variable?
I've found examples of how to load a package but I haven't been able to find out how to I load it specifying the parent variables.
I think it's possible. MSDN shows the available methods but the example is for the base method.
I've not really used SSIS for a while, and I'm now building some in 2012 and trying to utilise some of the features in the 2012 SSIS catalogue; however I've hit a bit of a stumbling block.
What I'm trying to do is have a master/child package relationship, with several child packages and where the child packages themselves are dynamically called (i.e. the master package may call a different child package based upon some value or state of data already processed.)
When I try and create an expression for the PackageNameFromProject property... well, that property doesn't appear to set dynamically. I know how to do this for old style packages by creating expressions for the package name etc; but that way I can't use the package parameters I have from my master package.
Has anyone had experience of using Parent/Child packages while enlisting them in Transactions. I tested this on a small sample and thought that I had got it to work, but in my real-world package it does not.
The parent package essentially calls three child packages. In each child package there are multiple DFT's that import and transform data into SQL Server. All data must be imported or not at all. Therefore I created a FELC container into which three Exec child package tasks were placed. The FELC is set to Trans Option 'Required' and the Exec child package tasks to supported. Unfortunately upon failure of one of the DFT's in the child the data was not rolled back.
So initially we had in terms of container hierarchy for the Trans Option property: Parent package Supported FELC for calling child packages Required Task execute child package Supported Child package Suppored Tasks Suppored
Looking at this more closely we thought that we would need Parent package Supported FELC for calling child packages Required Task execute child package Required Child package Required Tasks Suppored
for it to work. However, the latter now gives us failures with error messages on the tasks on the child packages. [Execute SQL Task] Error: Failed to acquire connection "Conn ECARS1CEDImport". Connection may not be configured correctly or you may not have the right permissions on this connection.
Even more strange the first couple of tasks in the child pkg complete successfully even though they use the same connection listed in the error. These tasks also have Event handlers.
I want to set multiple child packages running without waiting for them to complete in a parent SSIS package. The catch is that I will be running the same child package in multiple threads with different configuration variables set. I want to drive the config variables for the child packages via a SQL Server table, and start the execution of each child package from within a for-loop container.
Here's what I've tried, and why it didn't work:
1) Execute package task. Didn't work: Waits for the child package to complete before moving to the next step.
2) Kicking off the package via the "sp_start_job" stored proc in the msdn db. Didn't work: Can't specify config variable values.
3) Using the DTExec command line prompt. Didn't work: Same issue as 1). Waits for the package to complete.
Anything I'm missing? Any ideas on how to accomplish this?
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
