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
Fig. 2 – The user ABROWN exists “Without Login” after the restore
Fig. 3 – A high level view of the package using the Foreach Loop Container
1 – Add an OLE DB connection to the package
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
3a – Add a result set, call it 0 (zero) and assign it to the db_login_ado variable
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
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
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 + ”
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
A successful run of the package
The user ABROWN is now linked to the login ABROWN after running the package