Post reply

How to Access an SSIS Task & its properties through script task?

  • November 22, 2007 at 9:18 am

    #119220

    Could any one answer for the following query please?

    Question: How to Access an SSIS Task & its properties through script task?

    Example: If I want to access the "Execute SQL" task & its properties through script task, how can I access it?

    Would be helpful if I get some code snippet, so that I can go in deep on that.

    Thanks in advance.

  • November 26, 2007 at 5:55 am

    #753982

    Let's start with asking why you would want to do that? If you are trying to use a script task like the DTS dynamic properties task, don't.

    Can you post what you are actually trying to do?

  • November 26, 2007 at 6:44 am

    #754008

    Thanks for reply....

    I am converting a package from DTS to SSIS and there

    in SSIS Package I need to have a equivalent functionality for the dynamic property task that is in corresponding to DTS.

    This dynamic property task configures SQL statement for a Execute SQL Task, that Query is having length of more than 4000 Chars which i can not Accommodate in the variable.

    Option of using file for Query is not acceptable. Please tell me some other alternative for the same.

    Also please suggest me why Script task should not be used for the above functionality of dynamic property task.

  • November 26, 2007 at 9:23 am

    #754121

    You really need to convert your SQL Statements into stored procedures that take parameters that do not overflow this 4000 character limit. I just do not think what you are trying to do will work.

    You could try using more than one variable - one that is the first half of your SQL statement and one that is the second half (or more if your SQL is more than 8000 characters). Then, you could use an expression for the SQL property that concatenates them together. However, I still think your SQL statement is going to get truncated at 4000 characters. I suppose, if you really needed to do this, you could use a file for your SQL statement, build all of the pieces of the SQL statement in variables or a table, and then stream the SQL statement in pieces into the file before you reach the ExecuteSQL task. It just feels like a poor solution though.

    As for why you should not use the script task as a dynamic property task - that is what expressions are for. They are much easier to manage and will generally perform much better.

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

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