February 5, 2008 at 5:19 am
Hi
I'm trying to accomplish set based update in my ETL-Load. I loaded all the changed rows in an "in-memory ADO recordset". That so I don't have to create temptables and so on.
My problem is that I can't figure out how to update alla my rows:-). Is there any way to pass and use an objectvariable into an "Execute SQL Task" where I can do a clean update?
If not, what would you suggest to do the update?
regards
Michael Torsson
February 6, 2008 at 1:14 am
Maybe I don't get you right, but as I suppose that later you want to load your changed ADO recordset into some table why not use the normal way by doing transformations on this recordset?
February 6, 2008 at 1:47 am
Hi
My goal is to update all changed rows in my Source doing a Set based update insteadof a row by row update.
So I load all changed values and there SourceId in the in-memory set to later in the flow do a complete update on alla changed rows as:
Update Source SET Value=tmp.Value......
FROM Source s
INNER JOIN memoryset tmp on s.Id=tmp.Id
My problem is that I don't know if it is possible to pass the object variable to a Execute SQL task to do the update, or if I will have to make a temptable to store the changed values.
regards
Michael
February 6, 2008 at 2:17 am
I think you mistake the meaning of "row by row". AFAIK "row by row" means fetching each row from a recordset and executing one (or more maybe different) commands on each single row. A transformation on the other hand works set based, doing the same for all rows. Therefore use a data source for your "Source" one for your "memoryset" join them using the join transformation, modify the data using some other transformation and write the data back wherever you want to.
The Execute SQL Task is just a remedy that has hardly anything to do with the ETL concept. It's just an interface to the components outside of SSIS that you use if you design a workflow that closely interweaves ETL processes with other processes.
I'd say that you can use Execute SQL only with database connections with a reasonable effort. Therefore, if you really want to use an update statement use it on temptables.
HTH
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply