October 5, 2005 at 1:54 pm
Hello,
Following is the goal I am trying to achieve:
Table: tSQLServers
Fields: ServerName, ServerTypeID, IsActive
DTS Package: MyDTS with Conn1 and Conn2 and a SQL Task that executes a SQL Job. Conn1 will be static connection querying a table to get SQL SERVER names out and assign each to Conn2 so long WHERE conditions match until the table is empty.
SQL using Conn1:
SELECT ServerName
FROM tSQLServers
WHERE ServerTypeID = 2 AND IsActive = 1
Assign the ServerName to Conn2 and run the job in SQL Task in each server coming out of the above SQL SELECT statement.
I know this can be done using a combination of ActiveX Script and Global Variables or Dynamic Properties and Global Variables, but not sure of the exact syntax and logical order. Has anyone done this before? Can you suggest a simple way of getting Server names out of a table and put it into an array or (want to avoid use of cursors) something similar if necessary then iterate through the resultset and assign each server name from the resultset to a SQL Server connection object (Conn2 in my case) then run SQL Task for each new Conn2 server name until resultset is EOF? I am having trouble finding the exact logic so if you can help or direct, that'd be great.
JN
October 6, 2005 at 3:28 am
It can be done, but you will have to be willing to do some legwork.
Step 1 - Investigate output paramater global variables. Basically these allow the result of a select statement to be stored. In this case store in type of 'rowset'
Step 2 - Create an activex script to loop through the recordset one at a time setting the connection along the way. You need another variable here to act as a check flag to signify if this is the last record.
Step 3 - Execute the pump task
Step 4 - This is the trickiest bit. You need to check to see if this not the last record. It it isn't then, call Step 2 again.
The first 3 steps are easy enough. For step 4, check this article out:
http://www.sqldts.com/default.aspx?6,103,246,0,1
It deals with looping through files, but you can use the same principle to loop through servers.
And good luck
October 6, 2005 at 3:32 pm
This might not be the most elegant way to do it but it seems to have worked for me whenever I encountered this same problem.
I prefer to write a stored procedure and add a column to your servertable (tSQLServers) called currentserver char(1). The stored procedure has a cursor that loops through your servertable doing the following:
1) update currentserver column to 'Y' for the current row the cursor is on
2) execute the dts package: the dts package is comprised of a dynamic properties task that sets the second connection's properties (for me these were usually the DataSource, UserID, Password, and Catalog). Within the Dynamic Properties Task, each of these properties are set independently with a query that looks like "Select property from tSQLServer where currentserver = 'Y' " The rest of the dts package looks like a normal package consisting of your two connections and the Data Transformation Task/Pump between them. You also want to put in a workflow so that the pump only executes on success of the Dyanmic Properties Task.
3) update currentserver to 'N' for the current row the cursor is on
4) cursor repeats steps 1-3
That's about it. You don't have to worry about ActiveX scripting (unless you want to slightly change the actual query in your Data Transformation Task/ pump but that is really a whole 'nother problem) and you don't have to worry about checking for the last row in the table since your cursor takes care of it for you. Depending on what I want to do with it, I either run the stored proc right there or set it up in a step of some job, etc.
Hope that helps, and if anyone has any comments or questions on this approach I hope to hear from you.
Dan Godshall
October 10, 2005 at 11:08 am
If anyone interested in knowing how I resolved and implemented the question in this thread, here it is:
Table: tSQLServers
Lists SQL Server names and other related fields.
SPROC: usp_get_SQLServers
Queries tSQLServers for ServerName (row by row processing, but no cursor use) and runs EXEC dbo.spExec_DTS_CheckMailOutage passing necessary variables. The EXEC spExec_DTS_CheckMailOutage is issued as many times as there are Servers in tSQLServers table matching WHERE clause.
CREATE PROCEDURE usp_get_SQLServers
/*
*********************************************
Name: dbo.usp_get_SQLServers
Purpose: Queries tSQLServers for ServerName (row by row processing) and runs EXEC dbo.spExec_DTS_CheckMailOutage
passing necessary variables. The EXEC spExec_DTS_CheckMailOutage is issued as many times as there are
Servers in tSQLServers table matching WHERE clause.
Author: JN
Created: 10/7/2005
Tested: 10/7/2005, 4PM on Stage SQL Servers
*********************************************
-*-*-*- REVISIONS -*-*-*-
*********************************************
*/
AS
SET NOCOUNT OFF
SET SET ANSI_WARNINGS OFF
DECLARE @SQLServer_Count INT,
@sqlserver VARCHAR (30)
SET @SQLServer_Count = 0
SELECT ServerName INTO #tmpSQLServer
FROM dbo.tSQLServers S
WHERE S.ServerTypeID = 2 AND S.IsActive = 1
WHILE @SQLServer_Count < (SELECT COUNT(*)
FROM dbo.tSQLServers S
WHERE S.ServerTypeID = 2 AND S.IsActive = 1
)
BEGIN
SELECT TOP 1 @sqlserver = ServerName From #tmpSQLServer
-- EXEC spExec_DTS_CheckMailOutage 'SQL Server where DTS is', 'DTS Package Name', SQL Server Security Level, 'DTS Global Variable Name', Destination SQL Server to run the job in.
EXEC dbo.spExec_DTS_CheckMailOutage 'SQL8DS1\I1', 'Check_Mail_Outage_TEST1', 1, 'gvSQLServer', @sqlserver
DELETE FROM #tmpSQLServer WHERE ServerName = @sqlserver
SET @sqlserver = NULL
SET @SQLServer_Count = @SQLServer_Count + 1
END
DROP TABLE #tmpSQLServer
GO
SPROC1: spExec_DTS_CheckMailOutage
Receives necessary variable values from dbo.usp_get_SQLServers SPROC and Execute DTS "Check_Mail_Outage_TEST1" via sp_OAxxxx specifying one DTS Global Variable. Original content courtesy of Database Journal site (referred by PW) - http://www.databasejournal.com/features/mssql/article.php/10894_1459181_3. Modified to suite my needs.
DTS Package: Check_Mail_Outage_TEST1 (residing on MySQLServer\I1)
DTS executed from within spExec_DTS_CheckMailOutage SPROC, which feeds a value to gvSQLServer global variable. This package executes JOB msdb..sp_start_job 'Check Mail Outage' in Destination SQL Server set by gvSQLServer to verify if SQL Agent and Mail Services are working properly as expected. The SQL in the JOB sends two e-mails to SQL@MYDomain.com mail group results of the mail verification job stating success or failure
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply