Hi, I am calling oneSSIS package from another using the Execute Package Task. I also need to pass a parameter to the called SSIS package. Can I do this? If yes, how? If no, then what will be the work-around for this? Thanks in advance.

Yes, you can use parent package variable configuration for this. Look here for more information: http://msdn2.microsoft.com/en-us/library/ms345179.aspx Also, you can search the forum for "parent package variable" and I am sure you will see many use cases. HTH, Senthil

How can you call a child package twice in a parent package with different variables/parameters?

You could use a foerach loop container; but be aware that the second iteration will not start until the first has been completed.

Or you can just use a second Execute SQL Task, specifying the same package.

Rafael Salas wrote: You could use a foerach loop container; but be aware that the second iteration will not start until the first has been completed. Hi Rafael, That is exactly what I would like to do next. I want to execute the package as a separate thread. What I understand from some previous posts is that I need to use the "Execute Process Task" instead of the "Execute Package Task" for this. However, even after using the Execute Process Task and calling the child package using the "dtexec.exe" utility, I am not able to execute the process as a separate thread. I don't want the For Each Loop Container to wait for the child package to execute. Instead, it should immediately process the next file using another instance of the child package. How can I achieve this in SSIS? Thanks.

jwelch wrote: Or you can just use a second Execute SQL Task, specifying the same package. Hi John, The number of input files is variable. Once the parent package starts execution, I want it to "spawn" multiple instances of the same child package and process these files in parallel. Can I do this using SSIS? Thanks.

AVNIP, I haven't tried the Execute process task approach myself; but in theory it should work. What results are you getting when trying it? How do you know the process are not going concurrently?

AVNIP, It is possible to implement in SSIS what you need. Sometimes it is called fire and forget technique. This technique will allow you to spawn processes in the loop without waiting for the execution of the individual process to end. So the spawned processes will run in parallel. You need to use the Execute Process Task. Here is how to configure the task properly. The Process Executable property should be the operating system file cmd.exe (command prompt). Everything else starting with the Dtexec.exe and its parameters list should be passed in the Process Arguments property. Regards, Yitzhak

Rafael Salas wrote: AVNIP, I haven't tried the Execute process task approach myself; but in theory it should work. What results are you getting when trying it? How do you know the process are not going concurrently? Hi Rafael, I have the Execute Process Task inside a For Each Loop Container. Here, I have specified the process as "cmd.exe" and I have an expression for the arguments. The argument expression translates to something like this - Code Snippet /c "dtexec /FILE "E:\Avnip\test.dtsx" /SET \Package.Variables[User::VarFilename].Properties[Value];"E:\avnip\InputFiles\<the filename appears here>" The filename is fetched into a variable by the For Each Loop Container. I observed that the For Each Loop Container is waiting for the Execute Process Task to complete execution and only then goes into the next iteration. I am sure that the processes are not running concurrently as they are processing the files one by one. The files are being deleted after processing and I can see them disappearing from my explorer window one by one with a lot of time-lag in between.

Yitzhak Khabinsky wrote: AVNIP, It is possible to implement in SSIS what you need. Sometimes it is called fire and forget technique. This technique will allow you to spawn processes in the loop without waiting for the execution of the individual process to end. So the spawned processes will run in parallel. You need to use the Execute Process Task. Here is how to configure the task properly. The Process Executable property should be the operating system file cmd.exe (command prompt). Everything else starting with the Dtexec.exe and its parameters list should be passed in the Process Arguments property. Regards, Yitzhak Hi Yitzhak, Thank you for the detailed response! In fact I am also doing exactly the same thing. However, the For Each Loop Containeris still waiting for the Execute Process Task to complete before moving over to the next iteration (file). The Executable property is set to "cmd.exe" as you have mentioned and the Arguments property gets the rest of the command: /c dtexec.exe /File "E:\Avnip\test.dtsx" /set \package.variables[User::VarFileName].Properties[Value]; "E:\Avnip\InputFiles\filename.txt" I might be missing something

You might need to use the System.Diagnostics.Process class for this (may have been answered in your other thread). http://msdn2.microsoft.com/en-us/library/system.diagnostics.process.aspx

An other option is a script task. Instead of pulling the variables from the child package, you can push them from the parent package: Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() ' Call child package and pass through parameters / variables ' The child package is called with the same connection ' manager as the Execute Package Task would do Try ' Configure the child package Dim app As New Microsoft.SqlServer.Dts.Runtime.Application() Dim package As Package package = app.LoadPackage(Dts.Connections("child.dtsx").ConnectionString.ToString(), Nothing) package.Variables("User::variable1").Value = "some value" ' Execute package and return result Dim result As DTSExecResult result = package.Execute() Dts.TaskResult = result Catch ex As Exception Dts.TaskResult = Dts.Results.Failure End Try End Sub End Class - Joost (Atos Origin)

I have been over my package several times now and have reproduced every step they describe in this document. My package compiles and runs but the values do not get passed correctly. The parent package is reading the values from a configuration file and that part is working. When the Parent package calls the child packages I set up a configuration Package to pass the values but in the child package all of the variables end up blank.