Changing SQL Task to Data Flow

Question

Changing SQL Task to Data Flow

Jason Crider

SSCertifiable

Points: 6019
More actions
December 4, 2008 at 7:27 am

#124717

I haven't done much with the Data Flow.
Let's say I inherit the following SQL task from DTS:
select distinct id
into #temp
from database1.dbo.table1 a
join database2.dbo.table1 as b
on b.number=a.number
join database2.dbo.medicaidnumber as c
on c.identifier=b.identifier and c.enddate is null
where a.enddate is null and a.status='a' and (LEN(c.Number) = 10)
update database1.dbo.table1
set status='M'
from database1.dbo.table1 as a, #temp as b
where a.id=b.id
If I wanted to take that and utilize a data flow and other SSIS tasks what's the best way to do that?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

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

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

You could make an SP out of the code you have posted below, adding a "SELECT * FROM #tmp" at the end. Call this stored procedure from an OleDb Source within your data flow, and then run the data through any other transformations you need.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Jason Crider SSCertifiable Points: 6019 More actions · Answer 2

Well, I was hoping to find a way to get away from the temp tables if possible. It seems that they aren't the best way within SSIS.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

grapefruitmoon SSCrazy Points: 2226 More actions · Answer 3

You could put the first part, the select statement without the into, in a SQL or OleDb Source in a Data Flow, creating a dataset that you can then pass to other transformation tasks...possibly a Merge Join from what is in your second statement.

-----------------------------------------------
SQL Server posts at my blog, Grapefruitmoon.Net[/url]

Jason Crider SSCertifiable Points: 6019 More actions · Answer 4

I don't understand that last part.

I have been able to create a OLE DB Source out of the first statement without the into.

It's the Update that I'm having the issue with. and where clause that I'm having issue with.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter