How to use custom DLL in SSIS Package


This article teachs you how to consume/use third party DLL or assembly via a SSIS package script task control. This script task control is a SSIS common control to perform functions that are not provided by the standard Integration Services Task. For Example, moving processing logic from external batch or script files into the package, or invoking a third party API as part of the control flow. This is the control where you can write the C# or VB code to execute in the part to SSIS packages.

1. Create Class Library project:
Start Visual Studio to create Class Library Project () to create DLL or Assembly.
Create Project

2. Implement your logic:
Write your code in Class.cs/vb file, compile it, sign with string name and place it in GAC.
Code

Registering the DLL
Register the DLL

3. Create new SSIS project:
Then create new SSIS project to use the DLL or Assembly into Script task

What is Script Task: It’s a SSIS common control, it performs functions that are not provided by the standard Integration Services Task. For Example, moving processing logic from external batch or script files into the package, or invoking a third party API as part of the control flow.

Steps:
i). File->Add->New Project
Create SSIS project

ii). Add new Integration Services Project as shown in below snapshot
Create SSIS project1

iii). Then add a new Script task (from SSIS Tool Box)
New Script task

iv). Than edit the script task & add the reference of new DLL (DatavalidationSample.dll) as shown in next step.

4. Add Reference:
When we click the edit button from the script task properties, it will open as shown Vista Project Editor, then please follow the steps to add the reference in it.
i). Right click on the vista project
ii). Select the Add Reference… option
iii). Select the Browse tab from the Add Reference dialog box
iv). The browse the DLL from your project and click OK
v). The DLL has been added in references.
vi). Please click Save all.
Add Reference

5. Call the custom method in your SSIS package:
Please follow the below steps:
i). Add the namespace (As shown in snapshot)
ii). Create an object for the class
iii). Call the method which you want to execute (see the highlighted part in snapshot).
Call method from dll

6. Execute the SSIS package:
On execution of SSIP package, the Message Box “Hello Infy!” is displayed, this text is sent by the DLL.
Execute & final ouput


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: