Update column on existing table in Data Flow Task???

Question

Post reply

Update column on existing table in Data Flow Task???

Brandie Tarvin

SSC Guru

Points: 173108
More actions
July 3, 2008 at 6:03 am

#194616

I have never been able to figure this one out.
How does one update a table in a Data Flow using an input data source?
I have two OLE DB Sources which I Merge Join together. I pull them down to a OLE DB Command Transformation and that's where I get stuck. I want to run an update statement. I can type the update statement with T-SQL, but how do I reference the incoming data?
Basically the T-SQL Command should look something like this...
Update t1
Set Cert = t2.Cert
from MyExistingTable t1
join IncomingDataSource t2
on t1.Key = t2.Key
But, again, I'm not sure how I reference the incoming data source. Any ideas? Or is there a Transformation task that does this (other than OLE DB Command) that I've completely missed?
BTW, I just looked at the LOOKUP transformation, but I don't think it does the update either.
Thanks in advance!
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Use the OLEDB Command transformation like you have.

Make the SQLCommand something like:

UPDATE MyTable SET MyColumn=? WHERE MyID=?

When you get to the column mappings you will find that you can now map your data flow columns to your variables in your command. Some providers support names paramters:

UPDATE MyTable SET MyColumn=@NewValue WHERE MyId=@ID

but I have never had good luck with these working and I tend to use ? and keep track of the order of the parameters.

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 2

Michael,

Does SQL Server support the parameter names consistantly?

Also, once I do this Update, I'm pretty much done with this data flow. I don't need to add a destination afterwards, do I? Will it break anything if I don't?

Thanks for your help. I didn't even think about the ? thing. You're SOUP-er! @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

No, SQL Server does not "consistently" support the named parameters. That's why I bailed out on really using them.

As far an anything beyond the OLEDB Command in the data flow - you do not need to hook it to anything. SSIS does not care if a data flow goes nowhere.

I tend to like it to actually terminate. You can download a Trash Destination component here: http://www.sqlis.com/%5B/url%5D that has always made me feel a bit better about data flows that go nowhere.