Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to pass variable to oledb source Expand / Collapse
Author
Message
Posted Friday, December 20, 2013 1:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:53 AM
Points: 119, Visits: 411
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
Post #1524895
Posted Friday, December 20, 2013 3:31 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1524913
Posted Friday, December 20, 2013 4:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:53 AM
Points: 119, Visits: 411
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.
Post #1524918
Posted Friday, December 20, 2013 4:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1524920
Posted Friday, December 20, 2013 4:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:53 AM
Points: 119, Visits: 411
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
Post #1524932
Posted Saturday, December 21, 2013 1:25 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 606, Visits: 2,109
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.
Post #1525247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse