I have seen many posts recently in the SSIS MSDN forum from people having trouble mapping parameters inside of SQL statements in both Execute SQL tasks and OLE DB source components (using the ‘parameter’ settings of those 2 components). If you are one among those having that difficulty, let me show you an alternative method:
Let’s work under the assumption you already have a SSIS variable call @[User::MyParameter] that holds the value to be used as parameter in the SQL Statement.
- Create a variable of string type to hold the SQL Statement. Let’s say @[User::MyQuery]
- Change the property EvaluateAsExpression of @[User::MyQuery] variable to TRUE
- Click the ellipsis button in the Expression property of @[User::MyQuery] and create an expression like:
“Select X,Y,C from Table Where X=” + @[User::MyParameter]
- Click ‘Validate expression’ and make sure the query looks right. Tip: copy and paste this query inside of SSMS or other native querying tool to test it.
- Go to the Execute SQL task set the SQLSourceType to variable and assign the variable holding the SQL statement to the SourceVariable Property. If you are using an OLE DB Source Component instead, change the Data Access mode property to SQL Command from Variable and then choose the variable from the Variable name drop down list.
BTW, this approach also works for Datareader source components. In that case, after you have configured the variable @[User::MyQuery], go to the control flow and select the data flow having the datareader you want to affect. If you look into the properties of the data flow task, you should be able to see the expressions property. Expand that property and look for for a property that looks like [DataReaderName].[SqlCommand], and place an expression using the variable holding the query. The expression should look like: @[User::MyQuery]
I like this approach because I can see how the SQL statement is being evaluate in the expression editor, plus, in the case of the Execute SQL task, I don’t have to be aware of different ways of ‘naming’ and mapping the parameter based on the connection being used.
Notice: Unless you change the DelayValidation property to true, you may need to provide an initial value to the variable holding the parameter value, as the package could fail validation.
Update: See Darren's comments about some diffrences when using an expression Vs parameters: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2415979&SiteID=1
Thanks for all the SSIS packages.
I am working on Excel sheets, when ever i insert data in a column, it is not starting in a selected range. Sheet3$E2:E2000. Can you suggest?
I am using a datareader source and trying to pass a variable using the methods in this post but I keep getting the following error message: [DTS.Pipeline] Error: "component "DataReader Source" (1)" failed validation and returned validation status "VS_ISBROKEN". Any suggestions?
That error suggests that the metadata of the data flow pipeline has changed (eg column names, number of columns, data types). The technique described in this post is for passing parameters to the SQL statement, this can be the where clause or order by. But if you try to alter the layout of the data set returned by the query, SSIS will fail the package.
I tried to follow the steps, however I'm not able to launch the expression builder as the ellipses just don't appear even when change variable properties and set "EvaluateAsExpression" to true.ReplyDelete
The expression builder for a variable was added in Service pack 1. You should install latest SP available.
Installed SP2 and all good now.
I tried all the steps mentioned to pass parameters to DataReader and it is working fine with no error, but datareader is not reflecting new parameter values from the script task. DateReader is running with default values mentioned in Variables. I created 1 'Main' variable of string type and followed steps mentioned in blog after that I provide the query with parameters to the variable Main in Expression. I have a ID variable in Main which should change at runtime and I providing the value to this ID variable through Script task, but when I am runing the SSIS, ID value do not change in DataReader. It still load data with default ID mentioned in design time. Main EvaluateAsExpression = TrueReplyDelete
This is great material and example. I used it to apply a variable in a update statement. It saved me tons of time.
Thanks again !
Does it apply to OLE DB source for Oracle? I received the following error even without adding the parameter when I validated the expression: failed. the expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.ReplyDelete
I have not tested it against Oracle, but I don't see why it would not work. make sure you use the 'validate expression' button to see the SQL statement, copy it and paste it in an Oracle query tool (toad, SQL*Plus); I am guessing that the expression is resolving to an invalid SQL statement.
Is it possible to execute a stored procedure with this mechanism? Thank you in advance.ReplyDelete
Absolutly. You just need to make sure the expression get evaluated to a valid SQL statement.
I see that you say you can do this for a Stored Proc, but I have been unable to make this work.ReplyDelete
Here is what I am trying to do:
I have a stored proc that I need to query - and the results of that query need to be mapped to a table in my database.
I have a few different procs, but for simplicity sake I will work with one proc that has only one parameter that needs to be passed.
The Parameter Variable I setup is @FiscalYear and is an Expression type. Where I need to set that Variable Equal to a value in a table
In SQL it would look like this: (assuming the variable is already declared)
SELECT @FiscalYear = RIGHT(RTRIM(ShortDesc), 4) FROM AccountPeriod WHERE ID IN (SELECT AccountPeriodParentID FROM ImportType)
Then I tried to setup a Query variable in SSIS where I put my stored proc call in the Expression with the Variable being passed as instructed:
"EXEC dbo.ImportIS " + @FiscalYear
I just keep getting errors that it cannot be evaluated or that the @FiscalYear variable cannot be evaluated.
I am working on a project for a client and any help you can give me with this would be much appreciated. I am struggling to find any answers on the web.
thank you very much for this post. It helped a lot, but I'm still in trouble: I have to use an Ado.Net Source Provider, but this provider doesn't offer the data access mode "sql command from variable". when I enter the variable in the sql command text window it doesn't work (of course). how can I use a statement like "select * from table where id = @[User::MyID]" via Ado.Net ?
Very, very usefull.
I've publish a word document (in spanish) with screenshots to make it more clear to our people.
I can forward it if you want.
Regards and thanks a lot again!
Just let you know that I've tested with Oracle and works perfectly.
How would you specify a variable as an OUTPUT in an expression?
I'm trying to call my Oracle procedure aml_position_pkg.get_client_position, which has 1 input and has 28 outputs.
My expression value is:
"BEGIN aml_position_pkg.get_client_position(" + @[User::CurrentClient] + ", " + @[User::ProductType] + ", " + @[User::CreditLimit] + ", " + @[User::FixedLoanBalance] + ", " + @[User::VariableLoanBalance] + ", " + @[User::TotalLoanBalance] + ", " + @[User::UnsettledAmount] + ", " + @[User::PledgeBalance] + ", " + @[User::PledgeSecurityValue] + ", " + @[User::TotalLiability] + ", " + @[User::PortfolioMarketValue] + ", " + @[User::PortfolioMarketSecurityValue] + ", " + @[User::PortfolioSecurityValue] + ", " + @[User::CashBalance] + ", " + @[User::CashSecurityValue] + ", " + @[User::TotalSecurityValue] + ", " + @[User::SecuritySurplus] + ", " + @[User::TotalBufferValue] + ", " + @[User::FundsAvailable] + ", " + @[User::LimitExceededFlag] + ", " + @[User::CreditLimitFlag] + ", " + @[User::AmountOverCreditLimit] + ", " + @[User::MarginCallFlag] + ", " + @[User::AmountInMarginCall] + ", " + @[User::BufferFlag] + ", " + @[User::AmountInBuffer] + ", " + @[User::PercentageInBuffer] + ", " + @[User::VariableLoanNumber] + ", " + @[User::VariableInterestRate] + "); END;"
The error indicates that my variables, which are empty Strings, are being treated as INPUTs when I want them to be treated as OUTPUTs:
Error: 0xC002F210 at Get Client Position, Execute SQL Task: Executing the query "BEGIN aml_position_pkg.get_client_position(1162243, , , , , , , , , , , , , , , , , , , , , , , , , , , , ); END;" failed with the following error: "ORA-06550: line 1, column 53:
PLS-00103: Encountered the symbol "," ...
Remember that you can always click the 'evaluate expression to get the sql statement the expression produces. You can copy and paste it in the native tool (SSMS, TOAD, SQL PLUS) to test the queryReplyDelete
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property. Why ?ReplyDelete
Nice but it does not work if your parameter is DATE type. Got the following error: "The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operandReplyDelete
types could not be implicitly cast into compatible types for the operation"
How to modify this string? Thanks.
thanks a lot! saved me from headacheReplyDelete
Por tu nombre creo que hablas español.
Necesito ayuda en lo siguiente,
Tengo que consultar la información de clientes utilizando un SP Oracle.
Utilizando SSIS pienso que agregando un Execute SQL Task y obtengo los ID tipos y los ID a traves de un query, y con un Foreach Container consulto todos los resultados desplegados en el query.
Como hago para que el Foreach reconozca cada TipoID y ID del query y me ejecute el SP para cada uno.
De antemano gracias por tu colaboracion.
Tendrias que connectar el execute sql task con el ForEachloop. Dentro del for each loop tendrias que usar algunas variables para almacenar los IDs. Mira este otra entrada en mi blog a ver si te ayuda: http://rafael-salas.blogspot.com/2010/01/ssis-loop-through-multiple-database.html
very useful specially with DB2 databases! thanks!ReplyDelete
I am creating an SSIS package where we have same tablename with different schema. So I created a variable as object and passing it in a foreach loop container and then adding oledb source and choosing the option " tablename or viewname as variable" and then when i select the variable it doesn't display. Can you tell me if i am missing something here?
I created an SQL task containing a script that will delete data based on value in a column. The script will go through specific tables and delete records based on the selected value. I am wanting to create a configuration file that other users may use, and enter a different value based on instructions. Question: How would I alter my script in order to use the variable? Many thanks
Greate thanks. How simple and usefulReplyDelete
My EST, Drop TABLE '`Table Excel worksheet name` query throws an error:
`Table Excel worksheet name` does not exist.
This may not be an issue with your expression. I haven't tried it, but I don't think excel supports 'DROP' statements. I know the SSIS msdn forum has several discussion around deleting rows and dropping sheets in excel files. I would suggest to go there and search and/or post your own question
Thanks for the post. I was able to create @[user::Myquery] expression and evaluate it and set [DataReader Source].[SqlCommand] property to @[user::Myquery] in data flow. But my DataReader Source says Error at Data Flow Task [DataReader Source]:The sql command has not been set correctly. What step am I missing?
Awesome Awesome Awesome!!!ReplyDelete
Thanks heaps for the post!
In summary I had prepared a query with CTE due to receursion. The resultset was required to be populted in an excel file and my problem was/were-
1) OLE_SRC would not let me pass params to the query since it has CTE in it
2) I couldn't plonk the query in a proc since that will not initialise the metadata for OLE_SRC
3) I couldn't plonk the query in a table valued UDF since it had a CTE in it
but then your solution worked beautifully!
now that its all over I wonder .. what a loose integration of CTEs MS did in SQL Server 2005?!?
Can't even begin to explain how grateful I am for this solution! I spent hours trying to get variables to pass to a stored procedure in an SQL Task yesterday and could not get it to work - but 10 minutes spent trying your solution and it's done!ReplyDelete
I've added a link to this post on my blog:
Thanks again Rafael
im getting string cant glue date params to it.ReplyDelete
would be good for simple ID's and strings?
This is a nice article..ReplyDelete
Its easy to understand ..
And this article is using to learn something about it..
c#, dot.net, php tutorial, Ms sql server
Thanks a lot..!
Nice but it does not work if your parameter is DATE type. Got the following error: "The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand
types could not be implicitly cast into compatible types for the operation"
How to modify this string? Thanks. "
I had a similar problem. What I did to get around it was to bring in my date parameter as a string and then do a cast.
"select * from MyTable with (nolock) where (LastModifiedDate >= CAST('" + @[User::incoming_date_parameter] + "' AS DATETIME));"
My incoming_date_parameter variable would have the date in the YYYYMMDD format. i.e. 20111001 for October 1, 2011.
I don't know if this is the best way to go but it worked for me.
This article is a real life saver!!! I really appreciate your work...ReplyDelete
Thanks a ton!
Thank you, Rafael. A great article; it saved me from continued frustration.ReplyDelete
hola rafael prodrias ayudarme en un DTS profa mi Skype es Chuy.MasterReplyDelete
Bueno soy estremadamente nuevo en esto del itegration services jejeje
te comento un poco lo que quiero hacer
tengo 2 data flow task y un excecute Execute SQL Task.
data flow task1 me sube un excel por medio de un EXCEL DB SOURCE y convierte los datos con un dataconverter ESTO LO QUIERO GUARDAR EN UNA VARIABLE1
data flow task2 es una consulta en mi base de datos para traer la clave del excel por medio de un OLE DB SOURCE ESTE LO QUIERO GUARDAR EN OTRA VARIABLE2
en mi Execute SQL Task quiero hacer la insercion en una tabla (ImportarClientesTemporales) con los registros tomados de la VARIABLE1 del data flow task1 y VARIABLE2 del data flow task2
This comment has been removed by the author.ReplyDelete
This comment has been removed by the author.ReplyDelete
Thanks. EXACTLY the instructions I needed.ReplyDelete