Hello,

I am currently moving everything from SQL Server 2005 SP2 to SQL Server 2012. I have a method for getting users, logins, roles and SQL jobs. But I also have to get copy all of the SSIS packages from 2005 to 2012. I know I can go to the 2012 SQL Server and click on the MSDN folder and choose import. However, this only enables me to import one package at a time. I have 95 packages. Is there a way to get them all from the 2005 SQL Server to the 2012 SQL Server in one shot? Doing one at a time will take me a very long time. I am not a SQL developer nor am I a DBA but I have been assigned this task.

Thank you for any assistance you can provide,

You need to open the SSIS projects in VS2013 with SSDT-BI or in the latter and upgrade the project(s) thus the packages.

Important: verify if they work.

Then  using the build command generate a .ispac file that can be copied to the target and the packages then need to be deployed to the SSIS catalog (needs to be pre-arranged).

I suggest you do not rush ahead before studying SSIS 2012.

The ispac file will deploy as many packages at once as you have in the project.

Hi Katherine,

Before I read your post I had managed to get 2 packages that belonged to one project over from the 2005 SQL Server to the 2012 SQL Server. The way I did it was I went to the 2012 SQL Server (destination server) and connected to Integrated Services; then right clicked on the MSDB folder and chose Import Package. I already had the database, users, roles & SQL jobs on the destination server. I then ran the package by itself and it did complete successfully. I then ran the SQL job which executes the 2 packages and that also ran successfully.

When I follow your steps and get to the point of selecting Add Existing Item the only choices I have to select are the .dtsx files currently stored on the destination server (2012 SQL Server). Does this mean I need to go to the source server (2005 SQL Server) and copy all of the .dtsx files to the 2012 SQL server?

Thanks for your assistance,

Dave

Hi Dave,

If you already export all packages from SQL Server 2005 MSDB, then you can copy all of the .dtsx files to SQL Server 2012. If not, then you can using the following two ways to export all packages from SQL Server 2005 MSDB as I suggest above:
http://sqlblog.com/blogs/jamie_thomson/archive/2011/02/02/export-all-ssis-packages-from-msdb-using-powershell.aspx
http://www.ssistalk.com/2011/03/14/ssis-export-all-ssis-packages-from-msdb/

Thanks,
Katherine Xiong

Hi Dave,

According to your description, you want to copy all SSIS packages at one time from SQL Server 2005 to SQL Server 2012.

As per my understanding, we should export all SSIS packages from msdb using Powershell or SSIS, then load all SSIS packages into one SSIS project. To load all SSIS packages into one SSIS project, we can create a SSIS project in SQL Server Data Tools, then right-click the Project name in the Solution Explorer tab, then select Add Existing Item option to select all packages from other projects. At the same time, they would automatically run the SSIS Package Upgrade Wizard from SQL Server Data Tools (SSDT) to upgrade the packages from 2005 to 2012. The following screenshot is for your reference:

After loading and upgrading all packages in one project, we can directly deploy the project to SSISDB database in SSDT or install all packages to MSDB based on your requirement.

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong