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 Saturday, July 16, 2011 9:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 268, Visits: 759
Hello,

I need to create a Report to show the jobs and history on various servers.

I found the code to run which tells me about the job history.

Now I want to run this script on each server.

1) Add a sql script task on to the control flow
2) Run script

But I want to then take the result set from the query and insert into a local table (tblMasterJob). I can then create an SSRS report that shows all servers, the jobs and how long each job is taking.

Question:

How to I stored the resultset returned from the query to put it the master table.

here is the pseudo code I want to create


For eachserver in ControlTable

--query currentserver
run sp for currentserver





Post #1142962
Posted Saturday, July 16, 2011 3:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
Here is a rough sketch of an SSIS package that will do what you want:

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.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1143005
Posted Saturday, July 16, 2011 4:26 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 268, Visits: 759
Thanks for the outline.

I was wondering how if you had an example or knew of an example for number 3.

I haven't done that before. I would think you would just be creating a string with the usual connection string properties (i.e. catalog, user, password, etc.)

Where would the ole db source handle the insert into the ole db destination?

Thanks.



Post #1143017
Posted Saturday, July 16, 2011 5:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
WebTechie38 (7/16/2011)
Thanks for the outline.

I was wondering how if you had an example or knew of an example for number 3.

I haven't done that before. I would think you would just be creating a string with the usual connection string properties (i.e. catalog, user, password, etc.)

In the Connection Managers Tab at the bottom of the Control Flow design surface, right-click and create a new OLE DB Connection. Go through the wizard to create it. Once created, click on the new connection in the Connection Managers Tab and press F4 (or right-click and go to Properties). In the Properties pane expand the Expressions tree item. You'll want to create a new Expression for the ConnectionString property. Have you worked with SSIS expressions before? Here is a decent demo that makes use of the Derived Column transformation to demo expressions. You can ignore the Deived COlumn piece nd just focus on what he's doing with Expressions. The expression language is the same as what you'll be using. In the end your expression will resemble your typical connection string with the server-name variable your Foreach loop uses thrown in to substitute for the server in the connection string.

Where would the ole db source handle the insert into the ole db destination?

This will happen inside the Data Flow Task noted in steps 5, 6 and 7 in my outline.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1143029
Posted Thursday, July 21, 2011 8:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 268, Visits: 759
Good morning,

I tried to do what you said.

1) I created a new connection manager.
2) I put a script task inside the foreach loop container.
3) I then put in a data flow after that.
4) Inside the data flow I've added a ole db source
5) I copied the connection string in the properties dialog for the new connection manager.
6) I went into the expressions and clicked connection string.
7) In the expression editor, I pasted the connection string properties.
8) I replaced the server name with the variable, but left everything else alone.

It fails to evauluate and throws an error.

Any idea what I might have done wrong?

Thanks,

Tony



Post #1145961
Posted Thursday, July 21, 2011 9:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
It sounds like you're close. Please post the expression you're trying to use as well as the exact error message you get when it fails to evaluate.

If you don't mind attaching your SSIS pkg to the thread I'll take a look at it on my side. Obfuscate names as needed.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1145996
Posted Thursday, July 21, 2011 10:22 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 268, Visits: 759
Thanks for the help!


Here is the expression that I am using:

Data Source=@[User::CurrServer]; User ID=RptUser;Initial Catalog=MY_CUSTOM;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Package-{44F8A456-FBBD-4165-8B66-A1E1A1D38E75}MYSERVER.MYDOMAIN.NET.MYDATABASE;

Here is the expression error message:

TITLE: Expression Builder
------------------------------

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2008&ProdVer=9.0.30729.1&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Attempt to parse the expression "Data Source=@[User::CurrServer]; User ID=RptUser;Initial Catalog=MY_CUSTOM;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Package-{44F8A456-FBBD-4165-8B66-A1E1A1D38E75}MYSERVER.MYDOMAIN.NET.MYDATABASE;" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

(Microsoft.DataTransformationServices.Controls)

------------------------------
BUTTONS:

OK
------------------------------



Post #1146096
Posted Thursday, July 21, 2011 11:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:00 PM
Points: 4,175, Visits: 4,257
Typically you get that error when you have a syntax issue.

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 #1146145
Posted Thursday, July 21, 2011 11:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
WebTechie38 (7/21/2011)
Thanks for the help!


Here is the expression that I am using:

Data Source=@[User::CurrServer]; User ID=RptUser;Initial Catalog=MY_CUSTOM;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Package-{44F8A456-FBBD-4165-8B66-A1E1A1D38E75}MYSERVER.MYDOMAIN.NET.MYDATABASE;

Here is the expression error message:

TITLE: Expression Builder
------------------------------

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2008&ProdVer=9.0.30729.1&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Attempt to parse the expression "Data Source=@[User::CurrServer]; User ID=RptUser;Initial Catalog=MY_CUSTOM;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Package-{44F8A456-FBBD-4165-8B66-A1E1A1D38E75}MYSERVER.MYDOMAIN.NET.MYDATABASE;" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

(Microsoft.DataTransformationServices.Controls)

------------------------------
BUTTONS:

OK
------------------------------


Try the expression this way:

"Data Source=" + @[User::CurrServer] + "; User ID=RptUser;Initial Catalog=MY_CUSTOM;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Package-{44F8A456-FBBD-4165-8B66-A1E1A1D38E75}MYSERVER.MYDOMAIN.NET.MYDATABASE;"


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1146150
Posted Thursday, July 21, 2011 11:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:00 PM
Points: 4,175, Visits: 4,257
Are you assigning the Connection String to a Variable?

If not try creating a variable of string type to hold the Connection String. @[User::ConnectionString]

Change the property EvaluateAsExpression of @[User:::ConnectionString] variable to TRUE


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 #1146160
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse