July 16, 2011 at 9:02 am
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
Things will work out. Get back up, change some parameters and recode.
July 16, 2011 at 3:32 pm
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
July 16, 2011 at 4:26 pm
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.
Things will work out. Get back up, change some parameters and recode.
July 16, 2011 at 5:48 pm
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
July 21, 2011 at 8:46 am
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
Things will work out. Get back up, change some parameters and recode.
July 21, 2011 at 9:09 am
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
July 21, 2011 at 10:22 am
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.
------------------------------
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
------------------------------
Things will work out. Get back up, change some parameters and recode.
July 21, 2011 at 11:18 am
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/
July 21, 2011 at 11:24 am
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.
------------------------------
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
July 21, 2011 at 11:35 am
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/
July 21, 2011 at 12:16 pm
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
Things will work out. Get back up, change some parameters and recode.
July 21, 2011 at 12:25 pm
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/
July 21, 2011 at 12:29 pm
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
July 21, 2011 at 12:40 pm
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.
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/
July 21, 2011 at 2:36 pm
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
Things will work out. Get back up, change some parameters and recode.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply