HI, we have something like 120 packages that need to be "upgraded" to a newer version of a template. Basically, we need to add a bunch of variables of various types (12-15 variables). Is there a way to open the package in a script task and add those variables programmatically? Or is there another way to do it (e.g. modify the dtsx file)?
Thank you,
Ccote
There's no way to do this using the Designer. However a package is just an XML document so you try adding a new variable by processing those XML documents using XQuery.
If you work out how to do that - reply here and let me know
-Jamie
|||
HI Jamie, I have been able to achieve something interesting by using a script component with the test code below:
Public Sub Main()
Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim packagename As Object = Dts.Connections("ChildPackage").AcquireConnection(Nothing)
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)
If Not package.Variables.Contains("VarTestNum") Then
package.Variables.Add("VarTestNum", False, "User", 0)
application.SaveToXml(packagename.ToString, package, Nothing)
End If
Dts.TaskResult = Dts.Results.Success
End Sub
The basis of the code I am using comes from Brian Knight web site.So far, I am not able to specify the type of the variable; It seems that SSIS resolve it using the Value method parameter (last one). I do not know if it will bw able to parse a date value correctly. But at least, if all I have to do is to change a type of a couple of variables in all package, the worst is done.
Ccote
|||You're right about the variable type It is determined by the type of the object that you pass in that parameter.
-Jamie
Yes it is more complex than DTS, but much more powerful, you can start from here: