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'tit 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 folderconnection 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, RobinUse 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.

Hold on a minute.... So we're saying that if you use file system storage, youHAVE to use one connection manager per child package to be executed? That seems a really ugly way to do it. BTW - Looping through all the packages in a location is no good to me, I need to be able to call an individual package with an individual Exec Package Task in a very controlled way. It seems so crazy, can I ask you to confirm what I'm experiencing is expected behaviour? Up to now, I've always stored packages in SQL. My parent package has a connection manager pointing to the SQL dB, then when I want to execute a child package, I justset the Connection property (of the Exec Package Task) to the connection manager (which is obviously the same for each child), and set the Package property (of the Exec Package Task) to the package name. Works great. Now I'm attempting to build a dead simple template parent/child system for a new project. Based mainly on Jamie's and Kirk's recommendations, I thought, "OK, let's do it their way. Let's do file system storage". I thought that I could create a file system connection manager to point to the package storage folder, then set the Package property to the package name within that folder. However, when you set the Package Location property to File System it only gives you the option to point to a connection manager, not a connection manager and a package name. This means that the package name has to be defined in the connection manager. This means that you have to have one connection manager per child package. Are there any established workarounds with dynamically modifying connection managers with expressions? So really, do you have master packages that have say..50 or 100, or in Cheese Bread's case 115 different connection managers? Surely not! Isn't this a really strong case for using SQL Storage?

Sam Loud wrote: So we're saying that if you use file system storage, youHAVE to use one connection manager per child package to be executed? Yes, that is correct. Sam Loud wrote: BTW - Looping through all the packages in a location is no good to me, I need to be able to call an individual package with an individual Exec Package Task in a very controlled way. You can use a file mask to control what packages get selected in a For Each loop. but that may still not provide the fine grained control you need, particularly if you need precedence constraints. Sam Loud wrote: So really, do you have master packages that have say..50 or 100, or in Cheese Bread's case 115 different connection managers? Surely not! Isn't this a really strong case for using SQL Storage? Well, yes, if you use file storage. I still prefer file storage over SQL storage - mostly for ease of deployment. Personally, for complex master packages like that, I use some C# code to generate a master package programmatically, based on a database table that stores the list of packages and their dependencies. An alternative is to use a Script Task to run the packages. You can set up a connection manager that points to the folder, and use the script task to append the name before loading the package. Regardless, being able to specify the folder and filename separately would be useful. I'd suggest that you open a enhancement request on Connect (https://connect.microsoft.com/SQLServer/Feedback).

OK John, thanks for clearing that up for me. I've raised an enhancement request here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=321276 BTW, all of the approaches you suggest seem sensible, elegant ways of getting around this issue - thanks for the tips. However, I'd still say that the ability to point to a file system package using a just a connection manager and the package name is something that should be present.

I agree, I just voted for it on the Connect site.

Great work here Sam. I've updated my blog article accordigly: http://blogs.conchango.com/jamiethomson/archive/2006/02/20/SSIS_3A00_-Deploy-to-file-system-or-SQL-Server.aspx -Jamie

I just added my vote as well. :{> Andy

Robin, I ran into this same limitation. Seems ridiculous to have to create a separate connection per package. Expecially considering you can create a connection manager to represent a folder and there is an attribute for package name (only used for SQL Server storage). I have a package with 50 package calls. They can run in parallel so there are no dependencies within their sequence container, just whatever threads the SSIS engine wants to start up. I am not going to create 50 connection objects, that is just silly and hard to maintain, not to mention visually upsetting. Guess my only option is SQL Server storage. Uggh.... Ken