Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - Run a query for multiple servers and stored resultset in local database/table?!


SSIS - Run a query for multiple servers and stored resultset in local database/table?!

Author
Message
WebTechie
WebTechie
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 916
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



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
WebTechie
WebTechie
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 916
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.



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
WebTechie
WebTechie
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 916
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



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
WebTechie
WebTechie
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 916
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
------------------------------



Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
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/
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search