SSIS get reference to package

Question

I'm trying to programmatically add an Execute SQL task from within a script task of my SSIS package. I know that the Microsoft.SqlServer.Dts.Runtime.Package class has an Executables collection which I can add my new task to but how to I get a reference to the package Im inside of?

Answer 1

It is not possible, SSIS does not support self-modifying packages.

To prevent any attempts of doing what you are trying to do, the task code does not have access to the package API, so you can't obtain reference to Package object from a task. But even if you find a way to curcumvent this - the results are not predictable, as package is not allowed to modify itself at runtime.

If you could describe what you really want to achieve (rather than asking for particular way to do it) - someone might find a way to do it. Maybe you can use child package - it is OK to modify child package before its execution, or maybe it is enough to just change some variables that are used by Execute SQL task later in this package?

Answer 2

As Michael says above, it's not possible to do what you are asking. However, you may be able to find a solution by setting variables at runtime, or by enabling or disabling certain packages at runtime using dtexec. For example:

dtexec /f e:\ssis\master.dtsx /set \Package\YourPackageName.Disable;True