Search code examples
sql-serverssisscript-component

Assign a Runtime Value to an SSIS Component

enter image description here

I needed to assign a value to this querystring at runtime, I tried using variable but it doesn't work. I also thought about using a task script but I don't know if I can assign values to a component through the script. Does anyone have an idea if this is possible?

I tried using variable but it doesn't work.

Solution

  • In a field like that, there's no mechanism for variable substitution. Thus, what you're submitting is the literal characters @[User::yearVar]

    Having never worked with the OData components, it looks like the way one gets dynamic content is through stepping back to the Control Flow, Right clicking on the Data Flow and selecting Properties. From there, Expressions

    enter image description here

    You can see 3 of the 4 options for OData Source: CollectionName, Query, ResourcePath, UseResourcePath. Hopefully, Query there is the same as Query Options in the GUI.

    You might be tempted to put a value in the Expression like

    "$select=..." + @[User::yearVar]

    And that's syntactically correct (assuming yearVar is a string). But save yourself the debugging hassle. Create a new SSIS variable scoped to the Control Flow, which is the default, called User:QueryOptions of type String and then in the Expression, use

    "$select=..." + @[User::yearVar]

    You will then assign the Query property of your OData Source to be

    @[User::QueryOptions]

    It is an extra step, but when you have to try and debug why this isn't working, there is no way to extract what the expression is that is built on the component. You can infer, guess, theorize what the runtime value is but the only way to see it, is to build the expression on something you can inspect, like an SSIS Variable.