July 27, 2010 at 4:44 pm
Hello... I am very new to SSIS and am trying to come up with a way to loop through a SQL table that has certain data elements I need to create an UPDATE statement and execute that statement against mySQL.
Currently I run an eCommerce site that has a mySQL backend. I need to keep my inventory in sync so I have already created an SSIS package that will connect to a vendors FTP site and copy down the latest inventory file. I then have a Data Flow to transform their data and put it into a Staging SQL table that has the following columns (Model, Quantity and Price).
What I now need to do is have a process that will loop through this table and create/run an update statement like:
UPDATE Products SET Quantity = (Quantity from SQL table), Price = (Price from SQL table) WHERE Model = (Model from SQL table);
I am assuming I will need to someone use variables and possibly a for loop, but not really sure how to put it all together. Like I mentioned, I have the FTP portion working, transformation working and even the connection to mySQL working... I just need to know how to build the UPDATE statement and run them.
Thanks in advance for any help
July 27, 2010 at 5:44 pm
Have you considered loading the data into a temp table in mysql and then running a batch update in mysql through a stored procedure? If you do that then you can join the two tables together (the source table and the table to be updated) and run the update in a much more simplified form than a looping mechanism.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply