Script component or script task

[Ankor]

I am pulling data from the database (DB1) in the following format:
DepartmentID DepartmentDesc
AABB Sales
AAAB Marketing
ABAB Customer Service ext.
Another database (DB2) has a table that also has DepartmentID and DepartmentDesc columns, but not all records are the same: departments can be shown in DB1 and not shown in DB2 and vise versa. I want to pull DepartmentDescs from DB1 and update the DB2 with them without touching the departments that exist in DB2 only.

My idea was to put DepartmentID and DepartmentDesc from DB1 into the variables that then will be sent to the stored procedure in DB2. The procedure will update the table for each sent row, or insert a new record in case if DepartmentID does not exist in DB2.
Is it doable?

Another idea was to load all DepartmentID and DepartmentDesc from DB1 to the temporary table in DB2, then run an update and delete the table, but I cannot see the temporary table in OLEDB Destination. Should I use any other control?

Thank you.

 

[mrdenny]

I would use the second option.

You will need to create a perminant table then load the data into that table, then drop the table when you are done.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ankor]

Unfortunately, I don't have rights on production server to create and drop permanent tables. Was the possibility to use temp tables dropeed in SQL 2005, or we cannot use it from SSIS only?

Also, I am challenged with scripting task now .

 

[Ankor]

I figured it out!!! Everyone, thanks for your support.

 

[Catadmin]

Could you post your solution, please? We're all still learning here and would be interested in seeing how you got around this particular challenge.

Thanks,



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Ankor]

In Control Flow create a Script Task. Open it and go to Script/Design Script.
Under Public Sub Main() you will need to create two connections: one for the database where you pull from, and another one - for the database where the data should be loaded.
I also wrote a stored procedure that will update my data in the destination database.
Run a query that will pull data from the source database and put the results into the reader. Then open the connection to the destination database. Write the following loop:
While (YourReader.Read())
Pull the parameters from the reader
Execute your stored procedure with the parameters
End While

This loop will run your procedure with each set of the parameters that you pulled from the source database.
Close both connections.

 

[Catadmin]

Thanks for posting this! It's greatly appreciated.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"