Solution to the problem of SSIS calling the third party Library

Time:2021-2-24

Background:

The company wants to make a program that calls Google API to upload files to Google drive and share them with others. SQL server agent has been used to process other tasks, so it is required to use similar technology to complete the function.

After:

1. Using Google API to develop SSIS components

After a round of searching, I didn’t find any components that can be used directly in my own tools, but I found an article after searching https://www.cdata.com/kb/tech/googledrive-ssis-task-export-2008.rst After careful examination, they found that they provided paid products, which were abandoned.

2. Create script task, add Google API package with nuget in the project generated during editing, and generate the project

After a package is added, it is compiled and passed. However, when it is executed, it is always reported that the dependent item cannot be found. Just as the reference has not been added, if you open the project again, the added package reference will be lost. The reason is that SSIS script task does not support third-party package references. I guess it only contains code files, which will be compiled at run time and will not contain other dependent files.

Solution:

Some people on the Internet said that it should be OK to put the dependent assembly file into the assembly search path, but I didn’t try, because it had a great impact on the production environment

3. Using reflection

1) Create a new project, reference the third-party library, compile and generate the assembly

2) All generated files are placed in one folder

3) Add a variable to SSIS and set it as the folder path of the previous step

4) Using reflection call code in script task of SSIS

 

In the third scheme, it fully meets our requirements, and the code is controllable. If new functions are needed later, it can also be extended. Record here to provide reference for latecomers and reduce detours.