Can I use an Expression to set a variable in a "SQL Statement Task" ?
I am designed an ETL solution loading a plain text file from a website. The user write the main parameters, like month, year, registercount, and press the processing button. The ETL solution starts.
Until now, I am used to read/set variables with SQL statements inside same "SQL Statement Task" but sometimes they do not work properly when the solution is started from the web app with these same params used to set these variables. I have no problem to read variables in an expression but I am not able to write them.
If you are talking about the "Execute SQL Task", expressions are possible. Here what I usually do is to configure the type as "direct input" and then define the whole statement - including my parametrized values - as expression. If you rightclick the task, you can select properties and in the properties you open the expression popup by clicking the "+" left of "Expressions" and then "..." which appears right of it. Then after selecting the property "SqlStatementSource" you can click the "..." at the right again and enter your expression here.
Well, the debugging experience is for sure enhanced, if you define a string variable with the expression on it and then select SQL command from variable instead of direct input - then you can put a watch on the variable value and track as it changes...
