Using the SSIS 2008 Foreach Loop Container

No Comments

Written by Luis Miguel – Originally published on November 27, 2011

This post is a demonstration of how to use the SSIS 2008 Foreach Loop Container to execute a routine against a dataset one record at a time. I have chosen the DBA task of fixing orphaned users to illustrate this technique.

The challenge: We need to restore several databases that are ready for testing from our Development environment into our UAT instance, as this databases are restored the login/user “links” are lost and we end up with orphaned users, if we restore a dozen databases and our testing group consist of another dozen users the task of manually fixing them becomes a little daunting.

The solution: Use an SSIS package to automate the process of executing the system stored procedure SP_CHANGE_USERS_LOGIN to each an every user on each and every database

Fig. 1 – A list of “fake” databases and users in my Lab’s UAT system, the target of the multiple database restores

List of databases and logins

List of databases and logins

Fig. 2 – The user ABROWN exists “Without Login” after the restore

Database user without login

Database user without login

Fig. 3 – A high level view of the package using the Foreach Loop Container

High level view of the package with the Foreach Loop Container

High level view of the package with the Foreach Loop Container

1 – Add an OLE DB connection to the package

The OLE DB connection

The OLE DB connection

2 – Add a package level variable and set its type Object, for this example I called this variable “db_login_ado”

3 – Add an Execute SQL Task (code below) and set the ResultSet property to “Full result set”
SELECT D.name AS database_, L.name AS login_
FROM sys.databases D, sys.server_principals L
WHERE D.database_id > 4 AND (L.type_desc = ‘sql_login’)
ORDER BY D.name

Execute SQL Task

Execute SQL Task

3a – Add a result set, call it 0 (zero) and assign it to the db_login_ado variable

Result set 0 of the Execute SQL Task

Result set 0 of the Execute SQL Task

4 – Add a Foreach Loop Container and set it’s collection to “foreach ado enumerator”

4a – In the Collection area of the this container set the “ADO object source variable” property to the variable coming from the Execute SQL Task

The Foreach Loop Container

The Foreach Loop Container

5 – Add two String variables, one for the databases and the other one for the logins

6 – In the Variable Mappings area of the container add two entries one for each one of the string variables you just added in the previous step. Make sure your “database” variable, which the first column of the dataset is set to Index 0 and the “logins” variable, which the second column in the dataset is set to index 1

Variable Mappins of the Foreach Loop Container

Variable Mappins of the Foreach Loop Container

7 – Add an execute sql task to the foreach container
DECLARE @dbname VARCHAR(128)
DECLARE @dbuser VARCHAR(128)
DECLARE @commandchangeuser VARCHAR(1024)
SET @dbname = ?
SET @dbuser = ?
SET @commandchangeuser = ‘USE ‘ + @dbname + ‘; IF EXISTS (SELECT name FROM sysusers WHERE name = ”’ + @dbuser + ”’) EXEC sp_change_users_login ”auto_fix”, ‘ + @dbuser + ”
EXECUTE(@commandchangeuser)

Execute SQL Task inside the Foreach Loop Container

Execute SQL Task inside the Foreach Loop Container

7a – Map the input parameters to the string variables set in the Foreach Loop Container, parameter names must be 0 and 1, the parameter size is the length of the VARCHAR data type

Parameter Mappings of the Execute SQL Task

Parameter Mappings of the Execute SQL Task

A successful run of the package

The execution of the pacakage completed successfuly

The execution of the pacakage completed successfuly

The user ABROWN is now linked to the login ABROWN after running the package

User status after the package ran

User status after the package ran

Importing a server-side trace into a table and recycling the trace file

No Comments

Written by Luis Miguel – Originally published on November 6, 2011

Bringing the data captured by a server-side trace into a table is a very convenient way to review the trace results when monitoring and analyzing database activity. One thing I immediately thought of when implementing this kind of solution was to do it periodically and incrementally.

SQL Server 2008 does not come with built-in functionality to append data to an existing trace file or to overwrite it and start fresh using the same file name.

One possible arrangement to achieve this has been explained in other blogs and requires the use of several trace files which is SQL Server’s default behavior. Since this technique is not without flaws and I can afford to stop the data capturing process for a minute while I import the data and refresh the trace file I did come up with the following script which lets me simulate the “refreshing” feature not available in SQL Server 2008.

Run this command first to build the table that is going to store the trace data before you put the script in a store procedure or an SQL Server Agent job


SELECT *
INTO   db_name.schema_name.table_name
FROM   Fn_trace_gettable(‘d:\trace\my_trace_file.trc’, DEFAULT) trace 

Once you have the table in place you can use the script below to keep adding data to it as frequently as necessary or possible


DECLARE @trace_id INT
-- Use the path to your trace file to get the Id of the trace you are working with
SET @trace_id = (SELECT id
                 FROM   sys.traces
                 WHERE  [path] = ‘d:\trace\my_trace_file.trc’)
-- If you get something back the trace is running
IF @trace_id IS NOT NULL
  BEGIN
      -- Stop the trace so it dumps the data into the file
      EXEC Sp_trace_setstatus
        @traceid = @trace_id,
        @status = 0
      -- Import the data from the trace file into a table
      INSERT INTO db_name.schema_name.table_name
      SELECT *
      FROM   Fn_trace_gettable(‘d:\trace\my_trace_file.trc’, DEFAULT) trace
      EXEC Sp_trace_setstatus
        @traceid = @trace_id,
        @status = 2
      -- Delete the trace file
      EXEC Xp_cmdshell ‘erase d:\trace\my_trace_file.trc’
  END 

Now you are ready to kick start the trace again using the same (fixed) file name you used before, probably another step in an SQL Server Agent job.

Powered by Netfirms