How to do SSIS & Stored Procedures - FOR BEGINNER

basically I have 2 Db's to do a basic hello world example to get my head around stored procedures and SSIS in MS Server 2005.

I've got a table SSIS_test_src and SSIS_test_des. The src database has 1 table (people) with 3 records (id, name, age). What I want to do is take the table and all records from the src database and insert them into the des database.

I'm not fully understanding of the hierarchy of SP's and SSIS packages, but i've been doing some tutorials and still getting nowhere.

If somebody could help by telling me how to get the data from my source 开发者_运维问答database into my empty destination database using SSIS packages and stored procedures it would be great.

Thanks, Cian

---

Is there a particular reason you want to use a stored procedure for this? To transfer data from one db to another using SSIS is trivial, and does not require a stored procedure. Here's how I would do it:

• Create a pair of Connection Managers: One for the source db and one for the destination.
• Create a data flow task.
• Create an OLE DB Source and an OLE DB Destination node to the data flow.
• Connect the source to the destination.
• Set the Connection Manager for the source node to the source db
• Do the same for the destination node with the destination db
• Make sure the mappings in the destination node are correct (if the source and destination tables are the same, then it should be ok)
• Run the package.

Like I said, I'm not sure why you want to use Stored Procedures to perform this task. I hope that helps.

---

Basically the source and destination databases have different table structures entirely. We need to get data from different tables in the source database and populate one table in the destination database with the returned data sets.

I need to basically do a bulk import of data into the destination table but I am new to all the SSIS and SP world so I am not sure where to start etc.

Cian