x

SSIS: Data Flow Task - Dynamic Query Problems

I have a package that has two streams: daily, bulk. Instead of having a completely separate Data Flow Task for each I want to dynamically structure the query for the DFT. I have created the package up to the point where I have to handle the data that comes out of the DFT OLEDB Source. Since I have created a dynamic query there is no way to identify output columns. As a result I can’t do any of the downstream mappings because the OLEDB Source resolves to blank. What I really need is a default query with no “Where” logic so I can apply the mappings and then some kind of dynamic “where clause” that runs at execution time so that I can control the DFT usage.

Is this even possible? Hopefully I've given a reasonable description of the problem.
more ▼

asked Aug 10, 2010 at 04:16 PM in Default

Lawndemon gravatar image

Lawndemon
105 3 3 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Hi Lawndemon,

Just to check, is the data flow task for each stream is exactly the same except for your OLEDB source SQL command. is that right?

If so it should be easy enough to create a variable, change the EvaluateAsExpression to true and enter the SQL command in the expression property like "Select ID,name,etc from someTable". This will be your default query with no where clause.

Then, In the OLEDB source, select "SQL Command from variable" from the data access mode. and select the variable name. You should then be able to setup the mappings for the rest of the dataflow task.

Now all you need to do is change the variable according to which stream is being run, I would use a script task on each stream and update the variable value in the script task with something simple like;

DTS.variables("variableName").value =  
"Select ID,name,etc from someTable where lastUpdateDate < getdate()"
Let me know if I'm off the mark. Good Luck!
more ▼

answered Aug 10, 2010 at 04:59 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 11 13 14

Thanks Daniel - Yes, the DFT is exactly the same either way. The only difference is whether I loop over a series of days, run for a single day, or bulk load all.

I completely follow your solution and I'm going to give it a whirl. I expect I may run into some problems crafting the query though as I'm not an exceptional script author and the query will be more like:

select col1, col2, etc from sourceTable where @var1 = XXX and @Var2 = YYY.

I'm not sure how to do that type of concatenation into a string inside a script... So I expect I'll be posting another question soon! :)

Cheers!
Aug 11, 2010 at 08:22 AM Lawndemon

Worked like a charm Daniel. I got around any complex scripting by setting the the master query variable and then assign it a value using another variable:

DTS.Variables("ExtractQuery").Value = Dts.Variables("ExtractQuery_Bulk").Value (or ExtractQuery_Daily depending on the path)

I'm using this as a template for source system extraction (lots of error handling wrapped around everything) so the variable driven approach makes it super easy to reuse.

Thanks for your help!
Aug 11, 2010 at 09:31 AM Lawndemon

I spoke too soon! The first few tests I ran I hadn't yet flagged "evaluate as expression" to true (just checking variables using break points) so it looked like it was going to work. However, as soon as I set EaE to true it prevents the variable from being overwritten so the query replacement no longer functions... It executes whatever default query was originally tied to the variable.

I wonder if it's possible to set the EaE flag on and off via task/script?
Aug 11, 2010 at 02:37 PM Lawndemon

So I created a work-around for the EaE as follows:

@var_DefaultQuery (EaE yes): select ... +@var_Qualifier @var_Qualifier (EaE no): blank @var_WhereClause (EaE yes): where date = @dateVariable

When I execute the bulk load the @var_Qualifier resolves to blank so no changes (select all). When I execute the daily load I set @var_Qualifier = @var_WhereClause. Since @var_Qualifier is EaE no it actually picks up the new qualified where clause.

If someone has a more elegant solution let me know. In the meantime, Daniel's idea with a slight workaround works great.
Aug 11, 2010 at 03:11 PM Lawndemon

hi Lawndemon, in the script task try putting this in, I was wrong when I said to change the "value" of the variable, you need to change the Expression property in the script. You also need the triple quotes so the string is wrapped in quotes.

Dts.Variables("sqlCommand").Expression =  
"""Select * from test where test = 'Left'"""
Aug 11, 2010 at 04:56 PM Daniel Ross
(comments are locked)
10|1200 characters needed characters left

I've had to do something similar but in my case the table didn't exist at design time. The hack I came up with is to assign a variable a query that returns the columns and datatypes/sizes that you need. eg

DTS.variables("variableName").value = "SELECT CAST(0 AS int)[ID], REPLICATE(N'A',25)[Name], REPLICATE(N'A',40)[Address1],CAST(0 AS decimal(10,2))[Income]"

Then at run-time you can add whatever table and WHERE clause you need
more ▼

answered Aug 10, 2010 at 06:01 PM

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

(comments are locked)
10|1200 characters needed characters left

I get a single SQLQuery in a string variable from script task. the query is different based on some condition. for e.g, in case 1 it will be like "select Id,name,age from customer" and for case 2 "select Id,orderId,amt from order". I want to pass this variable to OLEDBsource and based on the result of the 2 query, I want to insert some data in a table. the script task and DFT is in For each loop.

I am able to run the package, but every time it brings the records for query 1,as while configuring the variable I set its value to "select Id,name,age from customer".

Please suggest?

Thanks,

Vin

more ▼

answered Jul 13, 2012 at 01:50 PM

vin333 gravatar image

vin333
0

vin333 please ask this as a new question. If you ask it here, as an answer to another question, it won't get the attention it deserves.
Jul 13, 2012 at 03:16 PM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x941
x51
x9

asked: Aug 10, 2010 at 04:16 PM

Seen: 5328 times

Last Updated: Jul 13, 2012 at 03:16 PM