Adding Reference and Importing .NET into Script Transformation

I have a .NET component that I want to import into a Script Transformation of a Data Flow. Going into the script code (editing Script transformation and clocking "Design Script" button), I try to "Add Reference" to the component (Add Reference selection under "Project" menu), but I do not see it - nor do I have the option to Browse for the component.

How do I establish a reference to an external .NET component so I can use it in my transformation? It seems unnecessary to have to add all of the class modules for the .NET component into the transformation or Copy/Paste the code from those same class modules just to execute that code?

Any ideas?

Many thanks...

You'll need to put the assembly into the framework folder for it to be found by the Add Reference dialog-

C:\WINDOWS\Microsoft.NET\Framework\v2.0.<whatever>\

You will also need to GAC it for runtime.

|||Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.|||

I have a very simple .dll with a method that returns a string.

I can see it, and therefore reference it through the script. However, when I run the package I get the following error:

Could not load file assembly ‘Test_dll’, version=1.0.0.0,Culture=neutral, Publickey=null’ or one of its dependencies. The system cannot find the file specified.

What does it mean?

Thanks.
-w

|||I see that publickey is null, hence it's not strong named, hence it could not have been placed in the GAC. You need to strong name it (look up docs for sn.exe) and place it in the GAC (gacutil /if mydll.dll). Hope this helps.|||

Where do I add the DLL and GAC it - on the Client machine performing the development or on the local SQL Server machine?

|||Wherever you execute the package, so both.

I assume you will execute the package on the workstation during development, so it will need to be GAC'd there just for testing the package, and when you deploy to the server it will also need to be in the server's GAC.|||If the package is saved in File System as opposed to SQL Server, am I correct in assuming that the package, even initiated from a command prompt or BAT/CMD file, still "runs" on the SQL Server?|||The package runs on the machine that is running the bat file (dtexec).|||

DouglasL wrote:

Here is the current BOL comment on this subject, from the updated topic "Using the .NET Framework and Other Assemblies in the Script Component:"

The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined by file location and not by installation in the global assembly cache (GAC) or by other assembly properties. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing other managed assemblies, and does not include the COM tab for referencing COM components. Furthermore, you cannot cause assemblies from other locations to be displayed in this list in VSA by adding other folder names under the AssemblyFolders registry key, as described in the Microsoft Knowledge Base for use with Visual Studio. For more information, see How to display an assembly in the Add Reference dialog box.

So is there a way to refence other assemblies from a VSA script task. For example we have a web service that uses WSE and need to reference this from our script. We also have a generic proxy which uses this which we need to reference. How do we do this?

The reason I'm asking is that I'm working with one of our .Net guys on this and he recoiled and squirmed his face when I talked about putting DLLs into Windows\Microsoft.Net\Framework folder. And I can kinda see his point. VSA does seem rather limited in this respect.

-Jamie|||Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.|||

DouglasL wrote:

Unfortunately there is not.

I likewise recoiled and squirmed about having individuals (including myself) cluttering the .NET Framework directory with their homemade DLLs, and squirmed even more about saying so in BOL...which is why the paragraph only hints at the solution for others to deduce.

A peculiar restriction indeed of VSA.

Thanks for the confirmation Doug.

We've got 2 options at the moment. Stick them in .NET framework dir or roll our own tasks. Guess which is looking most likely at the moment!!!

-Jamie|||Its worth pointing something out here that I believe to be true.

The DLL does need to get put into the .NET Framework directory as Doug has said - but only to enable you to use it at design-time. The DLL does not need to be there at runtime - VSA can pick it up from the GAC. To prove this - change the name of one of your DLLs that has been put in this folder in order for VSA to use it. You'll get errors at design-time because VSA won't be able to find the DLL but your package will still execute successfully if the DLL is GAC'd. This means that the DLL only has to go into the nasty .NET Framework directory on development machines - not on your machines that execute the packages in a live environment

Perhaps this is obvious to people that know .Net intimately but it was a pleasant surprise to me.

-Jamie|||Just for future reference, a BOL link to the topic Doug mentions. It doesn't highlight the runtime vs design-time GAC requirement though.

Using the .NET Framework and Other Assemblies in the Script Component

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/c3913c15-66aa-4b61-89b5-68488fa5f0a4.htm