Assigning result of SQL statement to varible and adding to WHERE clause

Question

Assigning result of SQL statement to varible and adding to WHERE clause

cidr

Ten Centuries

Points: 1353
More actions
August 30, 2011 at 5:25 am

#390300

Hi there,
I've been looking for a resolution to this all week but can't get it to work, plus I'm new to SSIS. Hopefully someone can help.
The overview
1. pull data from sap using a WHERE clause for recent data
2. The Where clause (lastdate = '@varible') must have a variable with a specific date
3. load the data pulled from SAP to SQL table
I have an Xtract table container (used by SAP as a source container to pull the data). It has a WHERE clause which I need to populate with a specific date using the last job date (so I'm only pulling data since then)
I have set up a Execute SQL task container. Now this is where I'm a little stuck. I've added a sql statement to the SQLStatement field within the general tab, like so
SELECT dat_last_succeeded FROM dbo.DTSJobLog WHERE JOBNAME = 'last_Job'
This returns the last date (maxdate) appended to the DTSJobLog table. I've then went to Result Set tab and added a new varible called User::MaxDate
Now I'm unsure what do to. I was given advice to create a new string varible and assign a select statement to an expression like so :
"SELECT ..... WHERE datefield =" + @[User::MaxDate]
and use that for the parameter, so if above varible was called ExpDate then have this in the where clause.
To be honest, I'm a little confused at the point where i've added a sql statement to the SQL task container.
Can anyone help me?
Many thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rgtft Hall of Fame Points: 3518 More actions · Answer 1

Add a script task and have it print out (MessageBox.Show) the SQL statement that you are building. Does everything look okay? Break it down a step at a time. Once you've got the SQL you want, then you can put that in your data flow's data source task.

cidr Ten Centuries Points: 1353 More actions · Answer 2

Thanks for your reply. I'm not really sure how to build an sql query statement using the function MessageBox

Am I right in saying I click on Edit Script on "ReadWriteVaribles" property? I'm not sure of the syntax. Do I assign a string varible to "ReadWriteVaribles"?

If you could help me a bit more I'd appreciate it.

Thank you

rgtft Hall of Fame Points: 3518 More actions · Answer 3

Just select the variable ( @[User::MaxDate] ) you want to display in the ReadOnly variables -- you're not going to update it, just display it.

Then click on the Edit Script button; if you're doing C# do something like:

public void Main()

{

MessageBox.Show("My variable " + Dts.Variables["MaxDate"].Value.ToString());

Dts.TaskResult = (int)ScriptResults.Success;

}

The purpose of this message box is just to make sure the pieces are coming across correctly; then once the pieces are correct, you can build the SQL statement you want for your data source.

HTH,

Rob

cidr Ten Centuries Points: 1353 More actions · Answer 4

Thanks again.

I'm sorry, I've not really used code in Visual studio and I'm perplexed. I added the code. Do you know if I'll still be using a Execute SQL Task for this? to hold the SQL query or will I be doing it in the Edit Script window?

I've added the code that you've given me and I've right clicked the Script Task container and choose Execute just to run that container and it's showing red.

Error:

The element cannot be found in a collection. This happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

How do I run this lol?

Thanks

rgtft Hall of Fame Points: 3518 More actions · Answer 5

Make sure you are using C# (the code I gave you) as opposed to VB.Net -- this is set in the Tools, Options of VS.

The error is saying it can't find a variable by that name. Make sure you use the same variable name in the code as you selected in the ReadOnlyVariables property.