Hi
I'm new to SSIS field.
I'm importing data from flat files to sql server 2005 through SSIS packages.
I have around 30 packages which transfers data from flat files to corresponding database tables.
I want a single package that will run all the 30 packages by running that single package.
Like in a single stored procedure we can run multiple stored procedure, I want the same solutions for my packages.
Is there any method which can solve the above issue?
Please kindly guide me .
Its urgent. please reply soon.
Thanks in advance.
Hi priyanka,
You could use the Execute Package Task to call your other packages on one "mother" package. You can find it in your Toolbox under Control Flow Items.|||
Hello Jon;
Thanks a lot for your quick response.Your reply gave me a lot of knowledge, but still I'm in problem. By using Execute Package Task I can run any existing package, which is previously created.But I have Two queries.
1. It will run the package which contain the last flat file as data source, here my question is how to add a new flat file(since my flat file changes monthly) while running the package using "mother" package.I guess you got my point. Secondly,
2. By solving the point no.1 will not also solve my problem. As my requirement is , I want one package, on running this all other packages saved in one folder should be run automatically. I want to say that instead of running all packages one by one, I want to run them by one single package.
Thanks a lot again and eagerly waiting for your reply.
Thanks
Priyanka
|||
Hello Jon,
Sorry for disturbing again. I got the solution to point no-1. If possible help me for point no. 2, Where by running one mother package I can execute all other child projects.
Thanks
Priyanka
|||You can actually have them execute all at the same time by NOT connecting them to each other with the control flow arrow connectors. The packages will then execute all stand-alone tasks at the same time.
Try to experiment this first with 3 to 5 Execute Package Task objects. You'll see that they'll turn yellow (executing) all at the same time.
I hope I understood your problem correctly.|||
Thanks a lot. You are really a very nice person.
The above points solved my problem.Now I can run all the packages at a time.That was my requirement.
But it is fine if I'll run this in my machine. If I want to send the package to the client , who require to run the package to update the database at his end(He wants to run only one package. I can give him all the child packages and the mother package after running them at my end).But how he will be able to set the path for source flat file connection and destination sql server database connection.
In the Execute Package Task , is there any option where I can change the source and destination of each package, or is there any other way?
Thanks a lot again...
priyanka
|||
Priyanka,
To facilitate deployment in different environments, SSIS can use 'Package Configurations' that basically allows you to store package's property values outside of the package to be applied at run time; overriding the values given at design time. Such configuration values can reside in a table, XML file, environment variables, etc. I think using package configuration will solve the problem of deploying the packages at your client location. Search on this forum and the web and you will find a lot of good information for that.
|||Rafael is right on about package configurations.
You can find info on this here, here and here.|||Oh, and one more tip. If the "mother" and "children" packages are all in one folder, you may try to totally omit the absolute path of the child packages in their connectionstrings and leave just the file name. That would make the "mother" package search for the "child" package in the same folder as it is running.
Goodluck 
|||
Hello;
Package configuration not working.
In my project I 've one Mother package and 10 child packages.I've added the package configuration to all the child packages.
I ve created XML type package configuration.
In all the package configurations I've checked the Connection Manegers checked boxes.
But while I'm trying to run the Mother package, its throughing error as bellow
"Error: Error 0xC0012050 while loading package file "E:\Visual Studio 2005\TestPackage\PackageWithConfig\Integration Services Project1\Integration Services Project1\Divisions.dtsx". Package failed validation from the ExecutePackage task. The package cannot run."
Without using package configuration, its working fine.
Can you please tell me the entire flow of process with package configuration?
Thanks
Priyanka
|||
That is weird. It seems like you create a new set of children packages in a different location; make sure you are not missing something on that new set of files. Try to execute a single child package without calling it from the master package, just to check that is not something else.
There are a lot of posts and other good information about package configurations on line:
http://www.google.com/search?hl=en&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=SSIS+package+configurations&spell=1
|||Can you look for the message where the package fails in validation?|||
Thanks.
The issue has been resolved.Actually I was not concern about the security issue for password, that was missing in the dtsConfig file. By giving the password , it is now working fine.
Thanks to all of u.
|||
Is there any better approach to integrate packages into one package?
My target is to create a single package , by running this will run all the child packages.In the above process I've to change the flat file source path each time by searching it from the Xml file(Package configuration file) , which is a time consuming and boring process.I have a number of child files.So for each file searching for source path in Package configuration file is not an interesting approach.
Anyways I have to set the source path but is there any other easier and fast approach to achieve this.
Thanks.
|||
 priyanka.dash wrote: |
|
Is there any better approach to integrate packages into one package? My target is to create a single package , by running this will run all the child packages.In the above process I've to change the flat file source path each time by searching it from the Xml file(Package configuration file) , which is a time consuming and boring process.I have a number of child files.So for each file searching for source path in Package configuration file is not an interesting approach. Anyways I have to set the source path but is there any other easier and fast approach to achieve this. Thanks. |
You know you can create several copies of the XML configuration file and then simply copy whichever one you need to the "real" xml config file.... Then you don't have to spend time editing the files. Just create them once and copy whichever one you need at the time.
