locked
how to go about it. RRS feed

  • Question

  • Well,

    I have a main server say ParentServer, which as a table called MyServers.
    This table has information about other servers. say..
    Client1   10.10.10.01
    Client2   10.10.10.05
    .
    .
    Client40   10.10.22.05


    I have written a procedure which loops through this table and connects to each server (using openrowset) and gets data from their. Now, this procedure is providing inefficient, since it is unable to handle connection errors. (try-catch block doesn't work).

    Hence i want to do this using SSIS.

    Please suggest me how should i begin. I'm new to this

    P.S : i can't create linked servers, since i don't have admin rights on my server.

    Thank You.
    Liquidloop.
    Tuesday, April 8, 2008 11:59 AM

Answers

  • I have created an SSIS package that does exactly what you need (based on a sql server magazine code here , i have extended the solution to be more manageable and added some more info (replication info , custom logging..etc). i also created some front-end reporting services reports that present the data in user-friendly way.

     

    I can send you a copy of that solution if you would like , just i will need to prepare it for you.

     

    you can try the code in the link , it is really very good start.

     

     
    Wednesday, April 9, 2008 12:28 PM

All replies

  • Linked server is the recommended one although you can achieve this with SSIS (But its hard and time consuming when compare Linked Server)

     

    Tuesday, April 8, 2008 12:39 PM
  • what all components i need to use to start...
    Wednesday, April 9, 2008 11:27 AM
  • I have created an SSIS package that does exactly what you need (based on a sql server magazine code here , i have extended the solution to be more manageable and added some more info (replication info , custom logging..etc). i also created some front-end reporting services reports that present the data in user-friendly way.

     

    I can send you a copy of that solution if you would like , just i will need to prepare it for you.

     

    you can try the code in the link , it is really very good start.

     

     
    Wednesday, April 9, 2008 12:28 PM