Why is using an SSIS package so difficult?

  • I have an Execute SQL task, with a hard coded value, as part of my control flow. Everything works fine until it came time for me to add a simple single variable and now I am finding myself having to add the entire SQL statement to a variable and changing the time of SQLSourceType from 'Direct Input' to 'Variable', etc. This is too much. Is there not a straight forward way to assign a variable to an "Execute SQL Task"?

  • What part of the SQL Statement are you trying to use a variable for? SQL has certain limitations on where a variable assignment can be used and if you are not using one of those, then you have to use the approach that you think is too much.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • It is likely that you can use an Expression to set the SQLStatementSource property to get what you want, without the need for all the faffing around you mention in your post.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Right now I have a simple test SQL statement with a simple where clause in my SQL Expression. I have a package level user defined variable. And I map this variable to my SQL Expression. So far none of the syntax that I use works. The SQL Expression doesn't recognize the variable when the package runs.

    The sql statements that I have tried are similar to this:

    select * from MyTable where id = @MyParm (local mapped var)

    select * from MyTable where id = [user::MyPackVar]

    select * from MyTable where id = @[user::MyPackVar]

  • Did you try using the 'Expressions' property as I suggested? Otherwise your statement will not get evaluated ...

    You will be able to paste your variable into your SQL statement using the GUI - removes the guesswork.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Example please?

  • OK, just tried this with my own system:

    "select f1, f2 from account where accountid = '" + @[User::AccountID] + "'"

    is the value of SqlStatementSource, under Expressions.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil Parkin (8/11/2009)


    OK, just tried this with my own system:

    "select f1, f2 from account where accountid = '" + @[User::AccountID] + "'"

    is the value of SqlStatementSource, under Expressions.

    OK that worked, thanks. But I have to tell you this is the most un-intuitive approach to using variables that I have ever seen. And what is the Parameter Mapping for and the Variables window for?

  • OK I successfully added an expression but when I run the expression and check my code I get the following errors on Execute SQL Task:

    "The result of the expression \"\"EXEC [dbo].[MyProc] \" +

    (DT_WSTR,4) @[User::MyID] + \"\"\" on property \"SqlStatementSource\" cannot be

    written to the property. The expression was evaluated, but cannot be set on the property.\r"

    What gives?

  • What does your expression look like?

    It looks like you are now calling a Stored Procesure from your SQL Source? If so, you don't need an expression, you can use the parameter mappings.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Parameter mappings should work for this.

    On the general tab of the Execute SQL Task editor set the SQLStatement to: select * from MyTable where id = ?

    Then go to the Parameter Mapping tab and map user::MyPackVar

    See attached screen shots.

    This should let you call the SQL statement and pass in the value from the package parameter. If you are returning a result set you have a few options to handle that.

  • regarding the title of this post: "Why is using an SSIS package so difficult?"

    Don't blame SSIS. Based on what I have read in here, you're trying to use variables without taking the time to learn how to. The 3 examples you gave for the SQL statement used did not make proper use of parameters in the query.

    When it comes to using variables in a query, there are 2 basic ways you can do that. One is by using ? in your query to take the place of a parameter to which you will assign the value of a variable. The other method is to use expressions to generate dynamic SQL. I believe both of these method have been demonstrated in this thread.

    There is also a third way to do this. You can use a variable for your query. You can then either use expressions or a script task to modify the value of the variable, thereby modifying your query.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Hi roundel1900,

    The provider drives a lot of this behavior. The most popular providers for Execute SQL Tasks are OLE DB and ADO.Net. The provider is set in the ConnectionType property of the Execute SQL Task, and it impacts how you pass parameters into the T-SQL.

    If you select an ADO.Net ConnectionType, you must use an ADO.Net Connection Manager. The same can be said for OLE DB ConnectionTypes (you must use an OLEDB Connection Manager).

    Passing parameter values into an ADO.Net is accomplished using a query you posted in the SqlStatement property of the Execute SQL Task:

    select * from MyTable where id = @MyParm

    In ADO.Net, parameters look very familiar to SQL Server database developers - they look like parameters for stored procedures and functions. The parameters are mapped by name: @MyParm is addressed on the Parameter Mappings page as "MyParm".

    On the Parameter Mappings page of the Execute SQL Task editor, click the Add button to add a new parameter. Set the Variable Name to the SSIS variable that contains the value you wish to substitute into the @MyParm parameter. Direction will remain Input. Assuming the SQL Server data type of ID is int, the Data Type of the parameter will be Int32. For Parameter Name, enter "MyParm". Leave Parameter Size set to -1. This will push the value of the SSIS package variable you select in the VariableName column into @MyParm when your Execute SQL Task executes.

    There are subtle differences when using the OLE DB ConnectionType. First, the SqlStatement should read:

    select * from MyTable where id = ?

    Question marks are used as placeholders for parameters. The question marks are mapped by ordinal: the first one is addressed as "0" or "Param0" (depending on the Data Flow transformation you're using - in this case, "0"). The second question mark will be addressed as 1, and so forth. It's a zero-based array (or collection) of question marks.

    On Parameter Mappings, set the Variable Name to the SSIS variable that contains the value you wish to substitute into the question mark. Direction remains Input. Assuming the SQL Server data type of ID is int, the Data Type of the parameter will be LONG. For Parameter Name, enter "0". Leave Parameter Size set to -1. This will push the value of the SSIS package variable you select in the VariableName column into @MyParm when your Execute SQL Task executes.

    I'm often asked which provider to use in SSIS development. In general, I use OLE DB for moving large amounts of data and ADO.Net for scalars and small datasets. There are exceptions, but this is where I usually start. I find OLE DB scales better and ADO.Net is easier to read. If you call a stored procedure from an Execute SQL Task, using ADO.Net and setting the IsQueryStoredProcedure property to True will perform well.

    I realize this appears non-intuitive and it's easy to blame SSIS for this particular complexity. However, this non-intuitive-ness comes to us by way of the OLE DB and ADO.Net providers - and actually pre-dates SSIS by a few years.

    I hope this helps.

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Viewing 13 posts - 1 through 12 (of 12 total)

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