none
Changing SQL-command with a script RRS feed

  • Question

  • Hello

    I want to change the sql-command that is behind a ole db datasource with a script


    So that first the script is run and that the sql-command is changed

    Somebody who has any idea??

    Thursday, October 27, 2005 1:55 PM

Answers

  • You cannot access object or change their properties from Script in SSIS, a somewhat dramatic change from DTS.

    First choice would be to use an Property Expression on the SqlCommand property, but unfortunately this property has not been exposed as an expression.

    Next choice would be to use the "SQL Command from variable". You can use a script to set the variable, and the source can be set to use that variable, so job done. I woudl actually be tempted to set the SQL through an expression on the variable. Highlight teh variable in the Variables pane and select the properties grid. You can then set EvaluateAsExpression, followed by the Expression itself.


    Thursday, October 27, 2005 2:20 PM
    Moderator

All replies

  • You cannot access object or change their properties from Script in SSIS, a somewhat dramatic change from DTS.

    First choice would be to use an Property Expression on the SqlCommand property, but unfortunately this property has not been exposed as an expression.

    Next choice would be to use the "SQL Command from variable". You can use a script to set the variable, and the source can be set to use that variable, so job done. I woudl actually be tempted to set the SQL through an expression on the variable. Highlight teh variable in the Variables pane and select the properties grid. You can then set EvaluateAsExpression, followed by the Expression itself.


    Thursday, October 27, 2005 2:20 PM
    Moderator
  • Thats indeed a dramatic change

    Can you do it then in Microsoft Visual Studio for Application


    Thx
    Friday, October 28, 2005 6:45 AM
  •  g4rc wrote:
    Thats indeed a dramatic change

    Can you do it then in Microsoft Visual Studio for Application


    Thx


    In the context of SSIS, a script task and VSA are the same thing. So the answer is "No".

    You should use the approach outlined by Darren in the third paragraph of his earlier post. Its very easy - far easier than ActiveX scripting.

    -Jamie
    Friday, October 28, 2005 7:12 AM
    Moderator
  • can you give me a link to the post
    I cannot find it
    Friday, October 28, 2005 7:17 AM
  •  g4rc wrote:
    can you give me a link to the post
    I cannot find it


    Its at the top of this page Smile

    i.e. The first reply on this discussion thread.

    -Jamie
    Friday, October 28, 2005 7:46 AM
    Moderator