November 13, 2007

SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component


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

42 comments:

  1. Dear Rafael,

    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?

    ReplyDelete
  2. Useful post!!!
    Kind Regards!!
    pedro

    ReplyDelete
  3. Dear Rafael,

    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?

    ReplyDelete
  4. Dinoh,

    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.

    ReplyDelete
  5. 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
  6. Saurabh,

    The expression builder for a variable was added in Service pack 1. You should install latest SP available.

    ReplyDelete
  7. Thanks Rafael.
    Installed SP2 and all good now.

    ReplyDelete
  8. 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 = True

    ReplyDelete
  9. Thanks Rafael,

    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 !

    ReplyDelete
  10. 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.

    thanks!

    ReplyDelete
  11. JY,

    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.

    ReplyDelete
  12. Is it possible to execute a stored procedure with this mechanism? Thank you in advance.

    ReplyDelete
  13. lupa,

    Absolutly. You just need to make sure the expression get evaluated to a valid SQL statement.

    ReplyDelete
  14. I see that you say you can do this for a Stored Proc, but I have been unable to make this work.
    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.
    Thanks!

    ReplyDelete
  15. dear rafael,
    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 ?
    thanks

    ReplyDelete
  16. Hi Rafael,
    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!
    Aser

    ReplyDelete
  17. Hi Rafael,
    Just let you know that I've tested with Oracle and works perfectly.
    Regards
    Aser

    ReplyDelete
  18. Hi Rafael,

    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 "," ...

    Please help!

    ReplyDelete
  19. 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 query

    ReplyDelete
  20. Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property. Why ?

    ReplyDelete
  21. 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.

    ReplyDelete
  22. thanks a lot! saved me from headache

    ReplyDelete
  23. Hola Rafael,
    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.
    Saludos

    ReplyDelete
  24. Wilfredo,
    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

    ReplyDelete
  25. very useful specially with DB2 databases! thanks!

    ReplyDelete
  26. Hi Rafael,

    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?

    ReplyDelete
  27. Hello Rapheal,

    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

    ReplyDelete
  28. Greate thanks. How simple and useful

    ReplyDelete
  29. Please help!

    My EST, Drop TABLE '`Table Excel worksheet name` query throws an error:
    `Table Excel worksheet name` does not exist.

    What's wrong?

    ReplyDelete
  30. Cookie,
    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

    ReplyDelete
  31. Rafael,

    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[77]]:The sql command has not been set correctly. What step am I missing?

    Thanks,
    Sayli

    ReplyDelete
  32. Gaurav Saini10/11/10 9:53 PM

    Awesome Awesome Awesome!!!
    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
    etc.. etc..
    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?!?
    thanks
    Gaurav

    ReplyDelete
  33. 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!

    I've added a link to this post on my blog:
    http://atominnovation.blogspot.com/2011/01/ssis-mapping-parameter-inside-of.html

    Thanks again Rafael

    ReplyDelete
  34. im getting string cant glue date params to it.

    would be good for simple ID's and strings?

    ReplyDelete
  35. This is a nice article..
    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..!
    ri80

    ReplyDelete
  36. "cutebaby said...
    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.

    ReplyDelete
  37. This article is a real life saver!!! I really appreciate your work...

    Thanks a ton!
    Vinay

    ReplyDelete
  38. Thank you, Rafael. A great article; it saved me from continued frustration.

    ReplyDelete
  39. hola rafael prodrias ayudarme en un DTS profa mi Skype es Chuy.Master
    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

    ReplyDelete
  40. This comment has been removed by the author.

    ReplyDelete
  41. This comment has been removed by the author.

    ReplyDelete
  42. Thanks. EXACTLY the instructions I needed.

    ReplyDelete

I will love to hear your feedback and comments. Thanks.