3

Custom Script Task Needs Replacing In Many SSIS Packages. We can do it manually, but there is 170 packages, need to automate. Code works so no issues. When manually replaced, script task shows no errors.

Hello, I have about 170 packages in SSIS. I need to replace a task script in every package. In every package the name of the task is the same.

How can we replace the code or task in every package dynamically, rather than having to go into every task put the new one in, take the old one out, and rename the new one with the old task name.

Or better yet, simply replace the old one over the top of the one? The script is actually a DDL.

Thank you. P

DrDuran
  • 101
  • 1
  • 3
  • 12

2 Answers2

0

There is no official way to do that, since altering multiple packages at once is not supported in Visual Studio.

You can do a workaround (since dtsx packages are Xml files), by using a text editor to find and replace the piece of code you want:

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

I would recommend creating a custom task.
It will be a separate dll library outside of the package. You will have to once update all the packages to use custom task instead of the script task, but all future changes will be much simpler - you will have to update only the external dll and all SSIS packages will start using new one.
Developing a Custom Task

Other supported option would be to use "Microsoft.SqlServer.Dts" library to modify packages using SSIS API. You code will look like:

Imports Microsoft.SqlServer.Dts.Runtime  
Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask  
Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask  

Module Module1  

  Sub Main()  

    Dim p As Package = New Package()  
    ' Add a File System task to the package.  
    Dim exec1 As Executable = p.Executables.Add("STOCK:FileSystemTask")  
    Dim thFileSystemTask1 As TaskHost = CType(exec1, TaskHost)  
    ' Add a Bulk Insert task to the package.  
    Dim exec2 As Executable = p.Executables.Add("STOCK:BulkInsertTask")  
    Dim thFileSystemTask2 As TaskHost = CType(exec2, TaskHost)

https://learn.microsoft.com/en-us/sql/integration-services/building-packages-programmatically/adding-tasks-programmatically?view=sql-server-2017

Piotr Palka
  • 3,086
  • 1
  • 9
  • 17