how to pass variable to oledb source

Question

how to pass variable to oledb source

Nomvula

Hall of Fame

Points: 3778
More actions
December 20, 2013 at 1:47 am

#146446

hi guys
i've created sql task with the following query 'SELECT MAX(wwo.StatusChangedOn) AS MaxDate
FROM wrkWorkOrders AS wwo where wwo.Description = 'Completed Costing' and set up Result Set: Result Name = MaxDate, Variable Name = User::MaxDate.
i want to join Data flow task add and on the oledb source write a select statement to select * from wrkWorkOrders where StatusChangeOn <= MAX(wwo.StatusChangedOn) from the result set.
please help

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

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Nomvula (12/20/2013)
hi guys
i've created sql task with the following query 'SELECT MAX(wwo.StatusChangedOn) AS MaxDate
FROM wrkWorkOrders AS wwo where wwo.Description = 'Completed Costing' and set up Result Set: Result Name = MaxDate, Variable Name = User::MaxDate.
i want to join Data flow task add and on the oledb source write a select statement to select * from wrkWorkOrders where StatusChangeOn <= MAX(wwo.StatusChangedOn) from the result set.
please help

Why not write this as a single query in an OLEDB Source component? Are your data sources different?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Nomvula Hall of Fame Points: 3778 More actions · Answer 2

i'm going to create different sources but on each i need to select values withing the date range variable i'm declaring in the beginning.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

Nomvula (12/20/2013)
i'm going to create different sources but on each i need to select values withing the date range variable i'm declaring in the beginning.

Easy enough - just use start and end date parameters for the source queries.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Nomvula Hall of Fame Points: 3778 More actions · Answer 4

i don't think i understand, in the execute sql task i created the command SELECT MAX(wwoq.StatusChangedOn) AS MaxDate

FROM wrkWorkOrders AS wwo

INNER JOIN dbo.wrkWorkOrderQueue AS wwoq ON wwo.WorkOrderQueueId = wwoq.Id

INNER JOIN dbo.wrkWorkOrderStatuses AS wwos ON wwo.WorkOrderStatusId = wwos.Id

WHERE wwos.Description = 'Completed - Costing'

then set the Result set to variable User::MaxDate

now i want use the oledb source to select * from table where ChangedOn <= User::MaxDate, but i'm not sure how to do that

Nevyn SSChampion Points: 14210 More actions · Answer 5

Hi nomvula.

You need to set up an expression on your data flow task. it will let you dynamically set the SQL Command for your OLE DB source.

So, properties for the data flow, add expression, SQL command type, then put in a formula to build your query using the variable you build. Remember though, you are building a string with the query in it, not parameterizing the query, so your formula will be outputting the variable into a string and appending that string into your query. Format it accordingly.

Generally I design the whole data flow first with its own SQL command hard coded, then add the expression at the end.