SQLStatementSource via an Expression: Be Careful

Often when using parameters in an Execute SQL task the thing just does not understand me. Or at least not what I am trying to do with the parameters. It seems the SQL Native Client is even more dumb when it comes to passing parameters than the MS OLE DB Provider for SQLServer, though I have not done a thorough investigation to identify the dumbest of the two.
A workaround is to put the SQL statement as an expression in the SQLStatementSource property. But be careful… this has already cost me hours of unnecessary debugging. With a simple trick this waste of time can easily be avoided.

Consider a simple SQL statement like this and single mapped parameter:

insert into sometable
select ?

This returns with an error Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

To make it work: write the same SQL via the expression builder in the SQLStatementSource property but put the parameters directly where you need them. Notice you are building a string so everything is put in double quotes and the parameter is cast to a string. If the parameter would have been a string already, then extra single quotes would have been necessary to build correct SQL.

sqlexpr

Evaluate the expression to check the result.

Now here is the time saver: put /* EXPRESSION */ in front of the SQL statement! When Visual Studio saves the package it will overwrite whatever is in the SQLStatementSource field in the properties pane with the evaluated expression. But… this, of course, looks like plain ordinary SQL. When – weeks later – you need to make an update to the SQL you might be tempted to update the SQL in the properties field directly and then wonder why your modification does not work. That was what happened to me… So now I consistently put the eye catcher /* EXPRESSION */ in every SQL statement fields that are in fact expressions.

sqlexpr2

5 Comments »

Comment by Rafael Salas
2008-06-21 22:29:30

I like the /* Expression */ trick. Like you, I have been fooled many times when attempting to modify the SQL statement rather than the expression…nice post!

Rafael

 
Comment by Farian HS
2011-09-23 09:25:09

I have an error “A string literal in the expression exceeds the maximum allowed length 4000 characters.”
My query is about 7000 characters,
T.T
do you have any suggestion? could you send the answer to my email too ?

thank you

 
Comment by Xitiz Pugalia
2012-11-22 07:59:38

Hi i need to execute the query :—

SELECT security_alias,to_char(to_date(effective_date,’dd-mon-yy’),’mm/dd/yyyy’) as effective_date,src_intfc_inst,alpha_rating,numerical_rating,to_char(to_date(update_date,’dd-mon-yy’),’mm/dd/yyyy’) as update_date,update_source,analyst_code,reason,approved_by,to_char(to_date(submitted_by,’dd-mon-yy’),’mm/dd/yyyy’) as submitted_by,rating_comments,approval_comments,to_char(to_date(submitted_date,’dd-mon-yy’),’mm/dd/yyyy’) as Submitted_date,ipo_flag,offering_price,ipo_verify_flag,provider,source_name,to_char(to_date(source_date,’dd-mon-yy’),’mm/dd/yyyy’) as source_date,rating_code,rating_type_indicator,program,rating_enhanc,audit_alpha_rating,to_char(to_date(audit_date,’dd-mon-yy’),’mm/dd/yyyy’) as Audit_date,rating_char1,rating_char10,rating_char2,rating_char3,rating_char4,rating_char5,rating_char6,rating_char7,rating_char8,rating_char9,rating_date1,rating_date2,rating_date3,rating_float1,rating_float2,rating_float3,rating_char11,rating_char12,rating_char13,rating_char14,rating_char15,rating_char16,rating_char17,rating_char18,rating_char19,rating_char20 FROM SECURITYDBO.RATINGS where security_alias IN (select security_alias from securitydbo.security_master)and provider = @User::L_PROVIDER

IN MS OLEBD SOURCE…

here, @UserL_PROVIDER IS a string variable which is getting the data from an EXECUTE SQL TASK which i have created… when i write the above query,

it shows error:::: Missing Expression…

& if i write variable in single quotes, it thinks it to be a string value but does not fetch the data from the variable… plz tell me what to do??? I need it to be sorted out urgently…. contact no. : +91-8939005024

 
Comment by Ian McGowan
2014-11-15 04:14:39

I’ve been beating my head against a problem for hours, reading lots of stackoverflow and other great blog posts. I was trying to truncate tables in a loop and apparently you cannot pass a table name as a parameter. Thanks so much for taking the time to write this up – I bet you’ve helped thousands of people over the years!

 
Comment by Jing
2015-02-12 18:14:59

/*EXPRESSION*/

INSERT INTO LOC_Summary_Table

select “+ (DT_WSTR, 5) @[User::RowCountA]+”,”+ (DT_WSTR, 5) @[User::RowCountB]+”,”+ (DT_WSTR, 5) @[User::RowCountC]+”,”+ (DT_WSTR, 5) @[User::RowCountASS]+”

I have four variables need to be passed on into a table that I created in earlier step of package.

what is wrong with this expression? It did not work.

Jing

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post