asked
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.
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!
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!
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!
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?
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.
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'"""
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
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
2 People are following this question.
