Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SSIS - Run a query for multiple servers and stored resultset in local database/table?! Expand / Collapse
Author
Message
Posted Thursday, July 21, 2011 12:16 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 17, 2014 9:12 AM
Points: 269, Visits: 790
Well, I got that part to work.

I had to put quotes at the beginning and the end of the expression.

After that, it evaluated properly.

Now the last part:


1. Load the list of instances you want to iterate over into SSIS however you're doing that now.
2. Add a Foreach loop to your Control Flow and configure it to iterate over your list of instances however you're doing that now.
3. Setup an OLE DB Connection object and setup an Expression for the ConnectionString property to have it point to the current server in your Foreach loop.
4. Setup another OLE DB Connection object and have it point to the DB where your "master" table resides.
5. Add a Data Flow Task to your Control Flow.
6. In your Data Flow Task, use an OLE DB Source to get the data from each server
7. In your Data Flow Task, use an OLE DB Destination to load the data from the OLE DB Source into a table.



If I have an OLE DB source which is dynamically set #6 and a OLE db destination #7, how am I running a query?

For every server, I need to:

1) Run a query
2) Take the resultset of that query and place it into the OLE destination table.


Thanks guys for your help in this.

Tony





Post #1146182
Posted Thursday, July 21, 2011 12:25 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
How about a SQL Task inside a Foreach Loop Container?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1146189
Posted Thursday, July 21, 2011 12:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 7,135, Visits: 12,746
WebTechie38 (7/21/2011)
Well, I got that part to work.

I had to put quotes at the beginning and the end of the expression.

After that, it evaluated properly.

Now the last part:


1. Load the list of instances you want to iterate over into SSIS however you're doing that now.
2. Add a Foreach loop to your Control Flow and configure it to iterate over your list of instances however you're doing that now.
3. Setup an OLE DB Connection object and setup an Expression for the ConnectionString property to have it point to the current server in your Foreach loop.
4. Setup another OLE DB Connection object and have it point to the DB where your "master" table resides.
5. Add a Data Flow Task to your Control Flow.
6. In your Data Flow Task, use an OLE DB Source to get the data from each server
7. In your Data Flow Task, use an OLE DB Destination to load the data from the OLE DB Source into a table.



If I have an OLE DB source which is dynamically set #6 and a OLE db destination #7, how am I running a query?

For every server, I need to:

1) Run a query
2) Take the resultset of that query and place it into the OLE destination table.


Thanks guys for your help in this.

Tony



The OLE DB Source will contain your query. In the source you can issue any valid SQL query including calling a stored proc.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1146191
Posted Thursday, July 21, 2011 12:40 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
Ah yes, create an OLE DB Source in the Connection Manager as well.

You will dynamically set the connection properties.

You may find this article useful.

http://www.simple-talk.com/sql/ssis/working-with-property-expressions-in-sql-server-integration-services/


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1146197
Posted Thursday, July 21, 2011 2:36 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 17, 2014 9:12 AM
Points: 269, Visits: 790
Putting the query in the OLE DB source worked well.

That did it! Task complete.

I can now run this on all production servers and keep the results a monitoring database.

Thanks for all your help. I can now use this knowledge in quite a lot of upcoming projects.

Thanks again,

Tony



Post #1146287
Posted Thursday, July 21, 2011 2:42 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
WebTechie38 (7/21/2011)
Putting the query in the OLE DB source worked well.

That did it! Task complete.

I can now run this on all production servers and keep the results a monitoring database.

Thanks for all your help. I can now use this knowledge in quite a lot of upcoming projects.

Thanks again,

Tony


I'm glad that the members on this forum were able to assist you.

Your are in the processing of developing an arsenal of SSIS Projects.

Check them into Source Control and save them on your own media.

Once you get in the grove you will be on a roll.



For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1146294
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse