Can I Create an SSIS Package Variable in a C# Script Task at run time?
I think the title says it all. If I don't have a package variable named user::MyVar defined in the package, can it be created at run-time?
I want to be able to populate it with a value in a subsequent T-SQL Script Task.
Just for clarification, I only want to create it (as a string variable) if it does not exist. Its value will be set elsewhere.
I want to be able to populate it with a value in a subsequent T-SQL Script Task.
Just for clarification, I only want to create it (as a string variable) if it does not exist. Its value will be set elsewhere.
As far as I know it cannot be done. Just wondering, why would you want to do this? Why don't you just define it in the package and then assign it a value in whatever way (such as SQL Task as you mentioned) you like?
ASKER
Because we have come up with a "standardized" sequence container that consists, of other things, a C# Script task and subsequent T-SQL script tasks. One of the things the C# script does is populate a number of package variables from a database table, whose values, along with command line parameter values, are subsequently logged back in SQL Server. Not ALL packages contain one of the values that get set in the C# script, and is not necessarily always used by some of our packages. For consistency sake, rather than 'forcing' anyone who implements the standard code to add the package variable, I'd like to be able to create it in the C# script if it does not already exist.
If that can't be done, then I suppose we will go to plan B..
If that can't be done, then I suppose we will go to plan B..
Hello!
This may be possible. I haven't tried it yet.
Here's what I am thinking of doing: SSIS provides the ability the manually manage variables by using something called a "VariableDispenser". It may be possible to add a variable to the VariableDispenser.
Again, I haven't tested it - but will let you know once I do.
References:
- http://microsoft-ssis.blogspot.com/2010/12/how-to-use-variables-in-script-task.html
- https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.variables.add?view=sqlserver-2017#Microsoft_SqlServer_Dts_Runtime_Variables_Add_System_String_System_Boolean_System_String_System_Object_
This may be possible. I haven't tried it yet.
Here's what I am thinking of doing: SSIS provides the ability the manually manage variables by using something called a "VariableDispenser". It may be possible to add a variable to the VariableDispenser.
Again, I haven't tested it - but will let you know once I do.
References:
- http://microsoft-ssis.blogspot.com/2010/12/how-to-use-variables-in-script-task.html
- https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.variables.add?view=sqlserver-2017#Microsoft_SqlServer_Dts_Runtime_Variables_Add_System_String_System_Boolean_System_String_System_Object_
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nakul,
Thank you for your time and research. You have confirmed what I thought but had not been able to test out due to time constraints.
Thanks
Thank you for your time and research. You have confirmed what I thought but had not been able to test out due to time constraints.
Thanks
You're welcome, Doug! Glad I was able to help in confirmation of the theory. Have a good day, and thanks for the points!
ASKER