Access SSIS Task Properties Using an SSIS Script Task

Question

Access SSIS Task Properties Using an SSIS Script Task

I H

Mr or Mrs. 500

Points: 518
More actions
November 23, 2009 at 6:28 am

#216988

Hi,
I can’t find anything on this on Google or in the forums so not sure if this is possible – but in case does anyone know how to access and/or set the properties of a task object from within a script task?
For example if a package consisted of two tasks – 1 execute sql task and 1 script task, could the script task be used to dynamically read or set the SqlStatementSource property of the execute SQL task (i.e. read or set the actual SQL statement that the exec sql task would run)?
I’d have thought this would be in the dts namespace, e.g.
dts.package.task_name.[property].value, however it seems that you can only access package variables via the DTS object. . .
Any help much appreciated.
Thanks,
Iain

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Todasdasdfd asfasf Ten Centuries Points: 1340 More actions · Answer 1

No, you can not access any other tasks' or components' from within a Script.

If you want to modify another task or component's properties, the best ways to do that are to tie that other task or component's property to a variable, and modify that variable. OR use property expressions to construct the value on the property. You can even use both together to get more sophisticated and flexible. Scripts are rarely helpful or more powerful in this situation. (They may be more comfortable, however.)

What specific scenario are you trying to accomplish?

Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I'm with Todd on this, you can't really get access to those properties from a script. And the Expressions functionality is really built to handle this.

The only way to get access to such properties is likely to write your own custom task and that is WAY overkill for what you are asking for. Set a variable and use expressions to use it in other components..

CEWII

I H Mr or Mrs. 500 Points: 518 More actions · Answer 3

Todd/Elliot,

Thank you both for your answers and also for the hint of expressions, unfortunately I'm working with a third party app which is passing user selections directly tasks properties via the API, it doesn't use/isn't aware of expressions and so I cannot take this route. I'll see if there is some other workaround, otherwise thanks again for the answers as they've probably saved me another day of fruitless Googling!

Cheers,

Iain

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Perhaps we didn't understand..

What API are you refering to?

Can you set a variable in SSIS from an API call? If so then you can use expressions. Maybe some additional detail to exactly what you are trying to accomplish from start to end will help us give you better advice.

CEWII