August 6, 2014

SQL SERVER: Clone SSIS Package

Recently, I was assigned a job to create many DTSX packages. When I review the packages, I found those packages (Information flow) are moreover same. There is just a difference of Source file connection and destination SQL table which is different in each of the package. It might take couple of hours to create each of the package, but I wanted to get it done in few minutes. To achieve the same, I have looked up the DTSX code (XML) and updated as follows to achieve get it done efficiently and save some time.

Please find following steps to achieve the same:

1. Existing package:
 
SQLYoga.com

2. Copy Package:
Right click on existing package and click on copy option and after right click on SSIS Packages folder and Paste on the location

SQLYoga.comSQLYoga.com

3. Rename Package Name for newly Pasted file:
To rename package name as your mind and When message box open then click yes button

 SQLYoga.com

SQLYoga.com

4. Open package in Notepad Editor:

Go to folder where package is exist and open with package in notepad editor

SQLYoga.com

5. Replace package Name in Notepad file:

Replace old package name to new package name with Replace option

SQLYoga.com SQLYoga.com

6. Replace more text

Replace more text if you know to Change (Example: “Activity” text replace to “Job”)

SQLYoga.com

7. Check Replace name

Check all task name, SQL task and Data Flow task in replace with new text and Annotation text will no be changed, so it needs to be change manually

SQLYoga.com

8. Need to change manually

If SSIS package has Package SQL server Configuration then we need to change that manually too.

SQLYoga.com

No comments:

Post a Comment