Transfer Jobs and Logins using SSIS

We can use SQL Server Integration Services to transfer the logins and jobs from SQL 2005 to another SQL 2005 or SQL 2008. This comes in handy when its difficult to script each of the jobs . Firstly we need to create an SSIS package.

Open Business intelligence development studio click file new project select integration services project as the template and provide a suitable name for it.

SSIS_Transfer_logins_1

Open solution explorer using Ctrl + Alt +L or go to view and select solution explorer. Expand SSIS under solution explorer and expand SSIS packages right click and select new SSIS package.

1. Drag and drop Transfer Job task from Control flow item into Control flow tab and double click it

2. In the general tab, give a specific name and description for the transfer job task

3. In Jobs tab, specify the source and destination server name under connections and test the connection as shown below

4. We also have an option to specify whether to transfer all the jobs or specific jobs. In my case I am setting the option as False so as to transfer only specific jobs. Then we need to select the list of jobs that needs to be transferred from the JobsList

5. In the options, if the objects already exist (in our case it is the job) we need to specify what needs to be done. We can either specify it as FailTask if the job exists in destination or skip the object or overwrite it if its already present

6. Finally we need to specify the option to enable the jobs in destination server after getting transferred

SSIS_Transfer_logins_2

SSIS_Transfer_logins_3

SSIS_Transfer_logins_4

Once the above steps are completed we need to save the package (Ctrl + S) and execute it as shown below. We can view the status of the package execution under Progress tab.

SSIS_Transfer_logins_5

We also have an option to rename the package as indicated by the arrow marks.

SSIS_Transfer_logins_6

Once the package has executed successfully we can schedule the package as a job. First connect to Integration service right click MSDB Import package select file system specify the package path and provide a name for the SSIS package.

SSIS_Transfer_logins_7

Expand SQL Agent node and right click on Jobs new job and select the type as SQL Server Integration Services Package. Specify server name and package source as SQL Server. Click the ellipse button next to package and select the package we created. Specify the schedule for the job as per your desire.

SSIS_Transfer_logins_8

To create a package for Transfer login task, we need to create a new package and drag and drop the transfer login task from control flow items window and specify the source, destination server name. We need to choose the option to transfer all the logins or selected logins for specific databases. Also we have option to overwrite/skip/fail the package similar to the transfer job task. Finally we need to set the option Copy Sids as true to transfer the security identifiers as well. We need to enable this option in for transferring logins while doing log shipping where there might be mismatched id due to restore operation.

SSIS_Transfer_logins_9


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *