SSIS: Intergrating two servers

So I have two servers. SER1, SER2. SER1 is the one from where I am supposed to copy the data and put it in SER2.

Also I have few stored procs on SER2 that I need to execute and then put back the desired results in a different table on SER1.

Now, I need 2 more stored procs. 1. to copy basic data from SER1 to SER2 and other for copying all DATA from SER2 to SER1.

Question:

1. On which server should I make these stored procs ?
2. I am supposed to make a SSIS package which can run ALL SP's so SSIS package is taking care of copying data from SER1 to S开发者_如何学PythonER2 ? (i dont have linked servers)

Yes you can execute stored procs as data sources without having a linked server in SSIS. However, you cannot use temp tables in the stored proc. I have gotten aorund this by using derived tables or CTEs. The proc should only be a select not an action proc.

You want the proc to be inthe location where you can run it from SSMS. SO if it referces tables in server a, database b, that is where it needs to be.

In SSIS you create a connection to each database you want o beable to use as either a source or destination.

Then create a data flow and use the database containing the proc you want to run as the source connection in an OLE DB source connection. Set the data Accessmode to SQL Command and put the exec statemenet to run the proc inteh SQL Command text. If you need need input parameters, click on the parameter button to do that. Now check the columns tab. If no columns appear, your stored proc needs adjustment to make it work as a connection.

Then select the connection for the database you want to put the data in as the destination connection. and tell it what table and do the mappings.

First, you need linked servers, I dont understand what you mean by "I dont have linked servers" - with that you need to create a linked server.

For 1) You write the stored procedure based on the logic, if you have two servers 1 and 2 and you want to copy data from server 1 to server 2 then server 1 should logically contain the stored procedure. Does it have to be this way, no absolutely not, it can be on server 2, but think logically about this.

For 2) You need a linked server, how else can you execute ServerName.DbName.Owner.Table ?