Transfer SQL Server Jobs Task and Transfer SQL Server Logins Task in SSIS

By:   |   Comments (6)   |   Related: More > Integration Services Control Flow Transformations


Problem

Making a replica of your production server to create a dev or test environment or moving your production data to new hardware are tasks a DBA needs to perform. So what are different methods to perform these tasks efficiently and without much effort? In this tip I am going to demonstrate how you can use the Transfer Jobs Task and the Transfer Logins Task to perform some of these operations.

Solution

In my last tip, Transfer Database Task and Transfer SQL Server Objects Task in SSIS, I showed how you can transfer your databases and SQL Server objects with the Transfer Database Task and Transfer SQL Server Objects Task. In this tip I am going to demonstrate you how you can transfer jobs and logins using the Transfer Jobs Task and Transfer Logins Task respectively without writing any code.

Transfer Jobs Task

Transfer Jobs Task is used to transfer SQL Server Agent jobs from one SQL Server instance to another. This task gives you an option to copy all the jobs or selected jobs from the source server to the destination server.

Let's demonstrate how you can create a SSIS package using the Transfer Jobs Task. Goto START -> Microsoft SQL Server 2005/2008 -> SQL Server Business Intelligence Development Studio -> then goto File menu -> New -> Project -> Select "Business Intelligence Projects" in the left tree pane -> Select "Integration Services Projects" and name the project as you wish and click OK.

In this new project you will see there is one package already named "Package.dtsx". Drag a Transfer Jobs Task from the Toolbox (which is normally on the left side) to the Control Flow pane as shown below. Right click on this task and click on Edit...

demonstrate you how you can transfer jobs and logins using the Transfer Jobs Task and Transfer Logins Task respectively without writing any code

In the Transfer Jobs Task Editor, select Jobs on the left and now you are ready to configure this task.

transfer SQL Server Agent jobs from one SQL Server instance to another

These are the items that can be configured:

  • Connections
    • SourceConnection - specify the connection for the source SQL Server instance, if you have already created a connection then you can reuse it here or can create a new one.
    • DestinationConnection - specify the connection for the destination SQL Server instance.
  • Jobs
    • TransferAllJobs - if this is set to True it will transfer all jobs. If this is set to False you can select specific jobs you want to transfer.
    • JobsList - this will be enabled if TransferAllJobs is set to False. Then you can select specific jobs to transfer. See the image below where I am only selecting two jobs (Backup Databases and Rebuild Indexes) to transfer to the destination.
  • Options
    • IfObjectExists - If the jobs already exist on the destination then you have three choices, first FailTask execution, second Overwrite the destination job and third Skip the existing job and continue with others.
    • EnableJobsAtDestination - After the transfer you can also specify whether to enable or disable the jobs.

the person executing the package to transfer jobs must be sysadmin or member of any fixed SQL Server Agent fixed database role on both the source and destination instances

Once you are done with all these configurations you can hit F5 or click on play icon to execute the package. Your task will turn yellow during execution and then either red or green depending on the execution outcome. You can go to the progress/execution results tab to see any error messages if the execution fails.

Note: the person executing the package to transfer jobs must be sysadmin or member of any fixed SQL Server Agent fixed database role on both the source and destination instances.

Transfer Logins Task

Transfer Logins Task is used to transfer either all logins (except sa) or selected logins or all logins of selected databases from the source to the destination SQL Server instance. After the transfer, all the transferred SQL logins are assigned random passwords and SQL logins are disabled. The DBA needs to change the password and enable the SQL login before it can be used on the destination.

Let's walk through an example. Create another package in the current project and drag a Transfer Logins Task from the Toolbox to the Control Flow. Right click on the task and select Edit to configure the task's properties as shown below.

Create another package in the current project and drag a Transfer Logins Task from the Toolbox to the Control Flow

These are the items that can be configured:

  • Connections
    • SourceConnection - specify the connection for the source SQL Server instance
    • DestinationConnection - specify the connection for the destination SQL Server instance
  • Logins
    • LoginsToTransfer - You have three options for this:
      • AllLogins - this will transfer all logins from the source.
      • SelectedLogins - this allows you to select specific logins
      • AllLoginsFromSelectedDatabases - This allows you to transfer all logins associated with one or more databases as shown in the image below.
    • LoginsList - this will allow you to select specific logins if you select SelectedLogins for LoginsToTransfer
    • DatabaseList - this will allow you to select the databases if you select AllLoginsFromSelectedDatabases for LoginsToTransfer
  • Options
    • IfObjectExists - If the logins already exist on the destination you have three choices; first FailTask execution, second Overwrite the destination logins and third Skip the existing login and continue with the others.
    • CopySids - if you set it to True then security identifiers (SIDs) associated with logins are also copied to the destination

SQL Server Integration Services

Note: the person executing the package to transfer logins must be sysadmin on both the source and destination instances.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, July 24, 2014 - 6:15:45 AM - Fantomas Back To Top (32855)

Thanks for your tutorial :-)

When I start the Job I receice the following error:

[Task 'Aufträge übertragen'] Fehler: Fehler bei der Ausführung: 'Der Wert NULL kann in die owner_sid-Spalte, msdb.dbo.sysjobs-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei INSERT.
Die Anweisung wurde beendet.'.
NULL value not allowed

SQL Server 2008 R2

What can I do?

Thomas

Tuesday, May 27, 2014 - 9:07:33 PM - John Mendez Back To Top (31960)

I have logins from SQL Server 2000, and I was wondering if I can use transfer logins task on SQL Server 2008 to import them on my current SQL Server.

Tuesday, May 7, 2013 - 7:54:46 PM - atcheswara Back To Top (23784)

Hi

 

thanks for sharing the knowledge.

 

I have one doubt here..Do we get logins with same password or do we need to change the password after move the logings to toher instance.

 

Can you please clarify ..

 

 

Friday, March 22, 2013 - 3:43:11 PM - Daniel Figueroa Back To Top (22974)

Thanks for your tutorial, this was just what i was looking for...

Tuesday, May 22, 2012 - 12:36:53 PM - Basil Heighter Back To Top (17592)

Thanks for the easy to follow tutorial.  Made copying 30+ jobs and 100+ logins very simple.  In regards to the comment about ensuring the default database is set to Master for all logins prior to copying them.  Is there an easy way to change all logins?  Will there be any hinderance to the system by mass changing this setting?

Wednesday, August 4, 2010 - 9:24:43 AM - AJ Fritz Back To Top (10010)
Funny enough, I just implemented a SSIS package to transfer Logins, Jobs and Stored SSIS packages from a production server to a log shipped secondary.  A few of the problems that I had to overcome were that the Transfer Jobs task assumes that the agent operators, proxies and credentials are all present on the destination server.  I have so far found no way to transfer proxies and credentials, but I ended up putting in a Data Flow task to transfer new operators from production to the secondary before transferring the jobs.  With regards to the Transfer Logins task, if a login has a default database other than MASTER and that database is not in an online state (like recovering) the task will fail with a database inaccessable error.  The simple simple solution is to make sure that all your logins have Master as their default database on the primary before running this task.














get free sql tips
agree to terms