August 10, 2009 at 12:21 pm
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"?
August 10, 2009 at 1:35 pm
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
August 11, 2009 at 2:54 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 11, 2009 at 6:36 am
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]
August 11, 2009 at 6:46 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 11, 2009 at 7:30 am
Example please?
August 11, 2009 at 8:02 am
OK, just tried this with my own system:
"select f1, f2 from account where accountid = '" + @[User::AccountID] + "'"
is the value of SqlStatementSource, under Expressions.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 11, 2009 at 12:19 pm
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?
August 24, 2009 at 9:13 am
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?
August 25, 2009 at 3:36 pm
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.
August 26, 2009 at 11:43 pm
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.
August 27, 2009 at 8:47 am
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.
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]
August 27, 2009 at 11:33 pm
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