Developing Matt

My Technical Journal

Execute Sql Task to Insert

leave a comment »

SSIS has the ability to make your complex etl transactions work great as well as the magical ability to make an ordinary person COMPLETELY CRAZY.

All I wanted to do was insert into a table with a variable…kind of like a status showing that my package processed a file that I pulled down from the ftp site.  Perusing the controls I figured the best way to do this was with an execute sql task.  My ftp file name is stored in a variable named @ftpFile.  So, the trick was that I wanted to use the variable value on my insert.  So I filled out the  parameter mapping with my variable name and set my insert statement to insert into tablename (filename, success) values (? ,1)  as per the recommendations by several online sites as well as the two books sitting in front of me.  They all lie.

Here is the error I received upon implementation of these valuable recommendations:

[Execute SQL Task] Error: Executing the query ""insert into <tablename>(filename, succ…" failed with the following error: "Line 1: Incorrect syntax near ‘+’.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I tried everything I could think of with my result set short of throwing my computer out of the window.

My only guess is that this technique is possible with select statements, but not inserts (and if you use this technique note that this is ADO, OLEDB, AND ODBC specific).  I’m not spending any more time figuring out why…although if you want to tell me I’m eager to learn.

THE SOLUTION

So, after trying different things all day I decide to set my SQLSourceType = Variable which allows me to select a custom variable that I created that holds my insert statement. 

executesqltask

My variable (named InsFtpFilename) holds a string that points to another variable.   To make this work set the EvaluateExpression = true and set the value to something like this:

"insert into tablename (filename, success) values (‘" +  @ftpFile + "’] ,1)" 

(note: @ftpFile is my filename…it is referencing the other variable dynamically stored in another variable).  Also, for some reason in your Execute Sql Task it is required that a parameter is set (go to parameter mapping and make up something).  I don’t use it.  Nor will you.  But keep it cheerful none-the-less.

(I don’t see how it uses it…as I already set my variable above)

executesqltask2

Advertisements

Written by matt

February 19, 2010 at 4:38 pm

Posted in SSIS

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: