Create SSIS package - SQL Server 2008 R2

Question

How can I reate an SSIS package to copy all logins, jobs, linked servers, and custom error messages, etc. ?

Can I do that from Sql Server management studio?

Need sample please.

Thanks :)

Answer 1

You can use:

• Transfer Job Tasks
• Transfer Error Messages Task
• Transfer SQL Server Objects Task

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 2

Where is it? (Transfer job, error, sql ...)

I didn't find it on the Sql server management studio.

Thanks :)

Answer 3

Hi Eitan Mizrahi,

Those tasks that Raunak J has supplied are residing in Business Intelligence Development Studio(BIDS),  BIDS is a tool is used to create SSIS package. Please refer to: http://msdn.microsoft.com/en-us/library/ms174181(v=sql.105).aspx

If you would like to use Sql Server management studio to copy all logins, jobs, linked servers and so on, please refer to the following links:

How-To Copy SQL Agent Jobs between servers with script: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4e096c70-5063-4740-8dfd-8c48e7fcc3dc
How to transfer logins and passwords between instances of SQL Server: http://support.microsoft.com/kb/918992?wa=wsignin1.0
Creating Linked Servers (SQL Server Database Engine): http://msdn.microsoft.com/en-us/library/ff772782(v=sql.105).aspx 

Thanks,
Eileen

---

Eileen Zhao

TechNet Community Support

Answer 4

Just copy+paste the result of EXEC sp_help_revlogin, or copy+past the jobs - That's a solution, but not seems as a best practice attitude of Microsoft.

Is there any best practice, or other way doing that?

(Such a package on SSIS, or something ...)

I know little about SSIS, but this I need several guidlines in order to making scripts that I can transfer from one environment to another, please.

Thanks :)

Answer 5

Hi Eitan Mizrahi,

I suggest you can use Copy Database Wizard,  it lets you move or copy databases and their objects easily from one server to another, with no server downtime.  For more information about it, please see:
http://msdn.microsoft.com/en-us/library/ms188664(v=sql.105).aspx 
http://www.codeproject.com/Articles/167714/Copy-Database-Wizard-MS-SQL-Server-2005-and-later 

Thanks,
Eileen

---

Eileen Zhao
TechNet Community Support