Hi, I have a SSIS Project in SQL Server 2008 R2 with several Packages and I want them to execute by just executing one main package. This is no problem as long as the packages are stored in the local file system and you can use the foreach file enumerator to loop over the packages and execute them all. Works perfect! Now my infrastructure situation changed and I have to deploy all the packages on the SQL Server and need to execute them from there. I modified my main package so that the execute task is called 10 times for 10 different packages, step by step. To reduce the maintenance cost I want to use an enumerator for looping as usual, but I couldn't find one that is able to loop over packages on the server. Does anybody know a solution for this szenario? Thanks a lot to all!

You could use an ExecuteSQL task to get a list of all of the packages in msdb: select * FROM [msdb].[dbo].[sysssispackages]. You could refine that by joining to [sysssispackagefolders]. Here is an example for using the recordset. http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspxRussel Loski, MCT

You can query the sysdtspackages (SQL Server 2008) or sysdtspackages90 (SQL Server 2005) table in the MSDB to get the list of the SSIS packages deployed to SQL Server. Since MSDB is a system database, you may face access issues while querying the database due to the security configured on the server. Calling the other packages from your Parent package looks a good solution to me. However, whenever new packages are deployed to SQL Server, you will have to add a new "Execute Package Task" to your parent package that will have a call to this new package and then deploy the parent package also. Perhaps you can consider creating a batch file that has a call to all your SSIS packages stored in your SQL Server database using the DTEXEC utility. But you will have to ensure that, as and when new packages are deployed to your SQL Server, you will have to add a new entry to this batch file. There is no need to have a parent package in this scenario; you just have to run your batch file when you wish to run the packages. Hope that helps. Cheers, ShalinShalin P. Kapadia

Hi Shalin, thank you for your answer. I forgot to mention, that the packages should run sheduled through an agent job. I have no experience in working with batch files, and I am not sure if this works with the agent job. But thank you for your input. I'm still learning...

Hi Russel, I took a look at the link and at the first glance it looks like a solution to my problem. I'll try it. Thank you very much! Greetings...

Hi Shalin, thank you for your answer. I forgot to mention, that the packages should run sheduled through an agent job. I have no experience in working with batch files, and I am not sure if this works with the agent job. But thank you for your input. I'm still learning... Hi Oertel, A batch file is a collection of windows commands. They are not difficult to work with. Say you have a package named PackageA stored in the folder named MyFolder in SQL Server named MyServer. You need to write the following command in the batch file - DTEXEC /sq "\MyFolder\PackageA" /ser "MyServer" More information on the DTEXEC utility is here. You can schedule a batch file in your SQL Server Agent. In the step that you create in your job, select Type as "Operating System Command (CmdExec)" and in the Command box, give the entire path of your batch file. Say your batch file name is MyBatchFile.bat and is stored in C:\BatchFiles, you need to give the Command as C:\BatchFiles\MyBatchFile.bat If you follow this approach, you wouldn't have to make any changes to your existing packages when new packages are deployed to the SQL Server. Also, editing a batch file is fairly simple; you just need to open it in notepad to edit it. But, as mentioned earlier, please ensure that you include a new DTEXEC command in your batch file after a package is deployed to SQL Server to execute this newly deployed package. Hope this helps. Cheers, ShalinShalin P. Kapadia