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!
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