Creating a Package of dtsx packages

Hi

I am trying to build a package that is comprised of 100+ dtsx packages but cannot seem to get it to work. I have created a new connection where the connectionmanagertype = file and the file path is equal to the folder in which my dtsx files are located. I (location = fileSystem). No matter what I do I get an access denied error that shows the folder location but no package. I manually typed the name of the package in the PackageName property and have pasted in the PackageID in the appropriate property as well but I don't see anything in the PackageNameReadOnly. I have read the MSDN information but I don't see a step by step way to build a package of packages against which I can compare. Can anyone set me straight?

Thanks.

Robin

You can't point a connection to a folder; it has to point to a package (.dtsx file).|||

Hi Cheese,

You can use Execute Package Tasks to do the trick. Here's how I build these:

1. Create a new SSIS package.

2. On the Control Flow, drag an Execute Package Task from the toolbox.

3. Double-click it to open the editor.

4. On the General page, give it a descriptive name.

5. On the Package page, click the COnnection dropdown and select <New Connection...>

6. Select Existing File for Usage Type and navigate to the file.

7. If the package is password protected, click the ellipsis (sp?) in the Password textbox and enter it (and confirm).

8. Click OK to exit the editor.

That should be enough to run a package.

Hope this helps,

Andy

|||Yep, I assumed Robin was trying to use the Execute Package tasks... If not, then Andy is spot on.|||

Hi

What I was actually trying to do was execute 115 packages (children) from a single package (parent) where I only used one connection manager. I thought that if I use the existing folder option I shouldn't have to create a connection manager for each one. I can do this writing code in an execute process task and the .net framework but that was more effort (troubleshooting properties) than it was worth. To me it would seem that I should be able to define a directory and recursively execute each *.dtsx package in a looping fashion in a simpler fashion. So far the only way I have gotten this to work is to 115 excecute package tasks with 115 connection managers (1 to 1 relationship of course) and then execute each one upon completion and out of process. This while successful seems to be a very poor way of doing things.

Thanks,

Robin

|||

Do you need to execute those packages serially or in parallel? If you need them to run in a serial fashion; you may use a ForEach loop container and 1 execute package task/Connection manager.

But if you need them to run in parallel or if you need to create precedence constraint between packages; I don't see a better way than having 115 execute package tasks and connection managers.

Notice that is you use Execute system task; the task will return succesfull right after sending the command; so the master package will not know whether the called package fail or not.

|||

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible. My solution right now is to have a connection manager for each source file (all 115 of them). This seems to be a really stupid thing to do.

Thanks,

Robin

|||

Cheese Bread wrote:

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible.

This sounds like the best approach for your scenario and is what I would have done. Why didn't it work?

-Jamie

|||

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

|||

Cheese Bread wrote:

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

Use a foreach loop to spin through your specific folder looking for *.dtsx files. Then, inside that foreach loop, you have one execute package task. Using the expressions feature of that task, you can set the Connection property to the variable populated by the foreach loop.