Adding a task to a package or sequence container
Hi -- I have written some code that programmatically builds an SSIS package. It does it well except when it tries to add an Execute SQL task to a sequence container. The end result is that the Execute SQL Task is placed under the sequence container. You can still see it through the transparent portion of the container but when you move the container, you can see that the SQL Task does not move with the container. Here is my code that adds the task to the container: Public Function AddSQLTask(ByRef pkg As Package _ , ByRef seq As Sequence _ , ByVal Name As String _ , ByVal SQLStatementSource As String _ , ByVal ConnectionName As String _ , ByVal IsStoredProcedure As Boolean _ , ByVal SQLStatementSourceType As SqlStatementSourceType _ , ByVal ResultSetType As ResultSetType _ , ByVal BypassPrepare As Boolean _ , ByVal Description As String) As TaskHost Try Dim TheExec As Executable If seq Is Nothing Then TheExec = pkg.Executables.Add("STOCK:SQLTask") 'Add task to package Else : TheExec = seq.Executables.Add("STOCK:SQLTask") 'Add task to sequence container End If Dim TheTask As TaskHost = CType(TheExec, TaskHost) With TheTask .Name = Name .Properties("Connection").SetValue(TheTask, pkg.Connections.Item(ConnectionName).Name) .Properties("IsStoredProcedure").SetValue(TheTask, IsStoredProcedure) .Properties("SqlStatementSourceType").SetValue(TheTask, SQLStatementSourceType) .Properties("ResultSetType").SetValue(TheTask, ResultSetType) .Properties("BypassPrepare").SetValue(TheTask, BypassPrepare) .Properties("SqlStatementSource").SetValue(TheTask, SQLStatementSource) .Properties("Description").SetValue(TheTask, Description) End With Return TheTask Catch ex As Exception Throw ex End Try End Function I've tested this code to make sure the 'seq' variable is not nothing, yet it still puts the task under the sequence container. Am I doing something wrong? Did I leave something out? The code that calls this is shown below. I simplified it to exclude unimportant code logic. Dim pb As New PackageBuilder() Dim pkg As Package = pb.OpenPackage(sTempFileName) 'package already contains some annotations and a sequence container Dim th As TaskHost With pkg '.RegenerateID() .ProtectionLevel = DTSProtectionLevel.DontSaveSensitive Dim seq As Sequence = .Executables("SEQ - Test") End With th = pb.AddSQLTask(pkg, seq, "Run", "SELECT * FROM tbl", "OLEDB_DEVBOX", False, DirectInput, ResultSetType_None, True, "") Am I missing something here? This code should create a new Execute SQL Task and add it to the already-existing Sequence container (seq). But it's not happening. Any ideas? Bill
December 28th, 2010 4:33pm

what does the seq variable return when you test it to make sure it is not nothing?Please 'Mark as Answer' if found helpful - Chris@tier-1-support
Free Windows Admin Tool Kit Click here and download it now
December 28th, 2010 8:53pm

It returns a sequence container -- the one (and only one) sequence container in the package I have opened. Bill
December 29th, 2010 8:50am

I've even been able to reproduce this behavior in a console application. Code is: Imports Microsoft.SqlServer.Dts.Runtime Module Module1 Sub Main() Const template As String = "C:\SSISPackageTemplate.dtsx" 'contains annotations and one sequence container called "SEQ - Main Workspace" Dim app As New Application() Dim pkg As New Package() 'Load the template package. pkg = app.LoadPackage(template, Nothing) pkg.Name = "Sequence Test" Dim seq As Sequence = pkg.Executables("SEQ - Main Workspace") Dim TheExec As Executable = seq.Executables.Add("STOCK:SQLTask") 'Add task to sequence container Dim TheTask As TaskHost = CType(TheExec, TaskHost) TheTask.Name = "My SQL Task" 'Save the package. app.SaveToXml("C:\seqtest.dtsx", pkg, Nothing) Console.WriteLine("Package created") Console.WriteLine() Console.Read() End Sub End Module Just need to modify the template constant to the path of your choice. The template package must contain a sequence container called "SEQ - Main Workspace". Then this app can be run. Once run, open the dtsx file that it created. Notice that if you move the container the SQL task does not move with it. Wierd. I can't explain it. But I must be doing something wrong. ???Bill
Free Windows Admin Tool Kit Click here and download it now
December 29th, 2010 9:22am

Also, after running the console app, if I try to drag the SQL task into the sequence container, I get this error message: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. I'm stumped. Bill
December 29th, 2010 9:33am

Also, after running the console app, if I try to drag the SQL task into the sequence container, I get this error message: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. (Sequence Test) I'm stumped. Bill
Free Windows Admin Tool Kit Click here and download it now
December 29th, 2010 9:33am

Hi Bill, If I am correct, you encounter the issue while opening the saved package in Business Intelligence Development Studio(BIDS). I can reproduce the issue. And the issue is caused by that the layout information which is saved by the BIDS is not updated when we program the package and save it again. I details the cause and the workaround in the following thread: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/974c43ae-0e2f-48a5-a75c-cb2ddbbdb425 I verify the workaround can solve the issue you met too. Additionally, the code you used is fine, and the Execute SQL Task is added to the container correctly. If there is anything unclear, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT
December 30th, 2010 1:38am

Jin -- Thank you. Thank you. Thank you! This clears up a lot of confusion.Bill
Free Windows Admin Tool Kit Click here and download it now
December 30th, 2010 8:40am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics