Adding Reference to App_Code of current website

I know it's possible to add a reference to a report to a custom class, but I was wondering if it was possible to refer to the code in the App_Code directory of the current website?

We have a lot of built in functionality that I could take advantage of, if I can make a call to the classes in the current site. If I have to create a new class, there's a lot of things that would have to be duplicated to get it to work and we definately don't want to have to duplicate efforts.

TIA

Well, you can reference custom assemblies from within your reports (it could be private or signed ones)

herehttp://msdn2.microsoft.com/en-us/library/aa179513(SQL.80).aspx you gonna find all you need to do so.

Hope this helps

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

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

Adding Lookup Programmatically:How can I add column from reference dataset to the transformation

Hello,

I have created SSIS package programmatically, I want to add Lookup transformation,

How can I add column from reference dataset to the transformation?

I have try to add new output column but it gives me an validation error, I write following coed to add new output column to lookup.

IDTSOutputColumn90 outputColumn = this.lookup.OutputCollection[0].OutputColumnCollection.New();

outputColumn.Name = col.Name;

outputColumn.Description = "Staging table output";

outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

outputColumn.ErrorOrTruncationOperation = "Copy Column";

outputColumn.SetDataTypeProperties(col.DataType, col.Length, col.Precision, col.Scale, col.CodePage);

Please suggest other way to add column from reference dataset to transformation output.

You have to set the reference column name to the appropriate custom property of the output column. You do not have to set any truncation or data type attributes, just get the IDTSDesigntimeComponent90 interface and call SetOutputColumnProperty on it. It should set these attributes for you (assuming your reference metadata is set to the component properly).

It should look like this:

designTimeComponent.SetOutputColumnProperty(outputID, outputColumnID, "CopyFromReferenceColumn", refColumnName);

HTH.

|||

Thanks !

Now I have added designtimeComponent property, but I got following error in setting reference table command

"Command text was not set fro the command object" ,before setting designtimeproperties it's working fine

code I used:

this.lookup = this.dataFlow.ComponentMetaDataCollection.New();

// Set component's stock properties.

this.lookup.ComponentClassID = "DTSTransform.Lookup";

this.lookup.Name = "LookupTransform";

this.lookup.Description = "Lookup component";

CManagedComponentWrapper instance = this.lookup.Instantiate();

instance.ProvideComponentProperties();

this.lookup.RuntimeConnectionCollection[0].ConnectionManagerID

= this.package.Connections["OLEDBConnectionLookup"].ID;

this.lookup.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(this.package.Connections["OLEDBConnectionLookup"]);

instance.SetComponentProperty("SqlCommand", "Select id as stg_id1 from [dbo].[Temp2]");

// Attach path between the OLEDB source components Output, and the Sort Component's Input.

this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(

this.oledbSource.OutputCollection[0], this.lookup.InputCollection[0]);

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

//Ingore on failuare

this.lookup.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;

IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

IDTSInputColumn90 col = instance.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

instance.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");

}

IDTSDesigntimeComponent90 designTimeComponent = this.lookup.Instantiate();

designTimeComponent.ProvideComponentProperties();

designTimeComponent.AcquireConnections(null);

designTimeComponent.ReinitializeMetaData();

IDTSOutputColumn90 newColumn = designTimeComponent.InsertOutputColumnAt(this.lookup.OutputCollection[0].ID, 0, "stg_id1", "reference column");

designTimeComponent.SetOutputColumnProperty(this.lookup.OutputCollection[0].ID, newColumn.ID, "CopyFromReferenceColumn", "stg_id1");

designTimeComponent.ReleaseConnections();

instance.ReleaseConnections();

Any suggetions,Can I set component property (Such as SqlCommand) at design time.

|||

Well, this code seems to have many issues. Let's try to fix it iteratively: for the start do not call ProvideComponentProperties twice, it will reset everything. Also, ReinitializeMetadata has to be called only once.

How many upstream columns are coming to your lookup, it seems like you are mapping all of them to stg_id1. Only one can be used for this lookup.

Try to fix these things and then post the cleaned code and the new errors you are getting. Mark the places in the code where the errors are captured.

Hopefully, we will be able to nail it in the next iteration.

|||

Thanks Bob !

U mean to say I set all component properties using 'IDTSDesigntimeComponent90' instance not using 'CManagedComponentWrapper' (right?)

Can u explain me how can i set component properties at design time

|||

Hi

Now code is working fine but it gives a Validation Error,''Input column "Name" has datatype which cannot be joined on"

I have to input columns id & name. and I want to join on single column 'id',Now what can I do to remove validation error

// Add the component to the DataFlow task.

this.lookup = this.dataFlow.ComponentMetaDataCollection.New();

// Set component's stock properties.

this.lookup.ComponentClassID = "DTSTransform.Lookup";

this.lookup.Name = "LookupTransform";

this.lookup.Description = "Lookup component";

IDTSDesigntimeComponent90 designTimeComponent = this.lookup.Instantiate();

designTimeComponent.ProvideComponentProperties();

this.lookup.RuntimeConnectionCollection[0].ConnectionManagerID

= this.package.Connections["OLEDBConnectionLookup"].ID;

this.lookup.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(this.package.Connections["OLEDBConnectionLookup"]);

designTimeComponent.SetComponentProperty("SqlCommand", "Select id as stg_id1 from [dbo].[Temp2]");

// Attach path between the OLEDB source components Output, and the Sort Component's Input.

this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(

this.oledbSource.OutputCollection[0], this.lookup.InputCollection[0]);

designTimeComponent.AcquireConnections(null);

designTimeComponent.ReinitializeMetaData();

IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

foreach (IDTSInputColumn90 col inthis.lookup.InputCollection[0].InputColumnCollection)

{

if (col.Name == "id")

designTimeComponent.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");

}

//Ingore on failuare

this.lookup.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;

IDTSOutputColumn90 newColumn = designTimeComponent.InsertOutputColumnAt(this.lookup.OutputCollection[0].ID, 0, "stg_id1", "reference column");

designTimeComponent.SetOutputColumnProperty(this.lookup.OutputCollection[0].ID, newColumn.ID, "CopyFromReferenceColumn", "stg_id1");

designTimeComponent.ReleaseConnections();

Thanks Bob for solution, can you give me ur e-mail id so I can ask you que. directly if I have

My id omkar.pimplekar@.gmail.com

|||It looks to me that you are selecting every column, as though they all take part in the join, see

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

Apply the same filter that you do in the following section, so only select the id column.

|||

Yes he is selecting every column, and it is not necessary. You can select only the column you want to join. Replace the two loops with one like this:

Code Snippet

IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

if (vColumn.Name == "id")

{

IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

designTimeComponent.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");

}

}

I do not think that is causing your error though. The error is caused by data type mismatch between your "id" and "stg_id1" columns. They need to exactly match in data types.

HTH.

|||

Hi,

Thanks Bob,

Now All working fine,again thanks for quick suggetions

Adding assembly file to AS database via AMO

I am trying to add an assembly reference to an AS database using AMO objects. The class Microsoft.AnalysisServices.Assembly class does not expose any file property or source property to assign the assembly file path.

With SQL Management Studio, I can right click on the Assembly folder and add a new assembly. I want to do the same thing in code. Can someone provide me with an example? Thanks!!

The AmoAdventureWorks sample that is shipped with SQL Server has the following C# routine which does what you want.

static void CreateStoredProcedures(Database db)
{
// Create the CLR assembly
ClrAssembly clrAssembly = db.Assemblies.Add("StoredProcedures");
clrAssembly.ImpersonationInfo = new ImpersonationInfo(
ImpersonationMode.ImpersonateServiceAccount);
clrAssembly.PermissionSet = PermissionSet.Unrestricted;

// Load the assembly files
clrAssembly.LoadFiles(Environment.CurrentDirectory
+ @."\StoredProcedures.dll", false);

clrAssembly.Update();
}

|||Thanks. MSDN AMO objects tutorial only mentioned Assembly class and not ClrAssembly. Thanks again.

Adding a web reference to a Report Server project

Hi there,

My company has a centralised user profile repository that all new applications worldwide use for authorization and user details; we access it from the various apps via a web service.

What I'd like to do in a report is to pass a user's ID to the web service and return their name and contact details to display in the header. I imagine I'd need to do this in a custom function and I can see where I can add a reference to use in this function but not where to add a web reference. Is this possible?

Regards,

Uzum4k1i.

One possible solution to the issue is to add custom code to the report: custom code (e.g., a C# library project in which a web reference could be added) calls the web service and exposes the web service call result for the report to reference. the assembly containing custom code needs to be deployed on the report server. More details are available here:

http://msdn2.microsoft.com/en-us/library/ms252130.aspx

Another possible option is to configure the web service as an XML data source in which the web service URL is the connection string. Details here:

http://msdn2.microsoft.com/en-us/library/ms159741.aspx

http://msdn2.microsoft.com/en-us/library/ms345338.aspx

|||Cheers Tau Liang, both of those answers sound like they'll fit the bill nicely

Adding a web reference to a Report Server project

Hi there,

My company has a centralised user profile repository that all new applications worldwide use for authorization and user details; we access it from the various apps via a web service.

What I'd like to do in a report is to pass a user's ID to the web service and return their name and contact details to display in the header. I imagine I'd need to do this in a custom function and I can see where I can add a reference to use in this function but not where to add a web reference. Is this possible?

Regards,

Uzum4k1i.

One possible solution to the issue is to add custom code to the report: custom code (e.g., a C# library project in which a web reference could be added) calls the web service and exposes the web service call result for the report to reference. the assembly containing custom code needs to be deployed on the report server. More details are available here:

http://msdn2.microsoft.com/en-us/library/ms252130.aspx

Another possible option is to configure the web service as an XML data source in which the web service URL is the connection string. Details here:

http://msdn2.microsoft.com/en-us/library/ms159741.aspx

http://msdn2.microsoft.com/en-us/library/ms345338.aspx

|||Cheers Tau Liang, both of those answers sound like they'll fit the bill nicely

Adding a web reference (web service) to an SSIS script task?

Is it possible to do this under SSIS 2005? How? I see I can add a reference to system.web.services.dll.. but then what?

The web service was developed in vb.net/vs.net 2005 and I have no problem adding and consuming it from a web page developed using vs.net 2005 - asp.net/vb.net.

Thanks for any help or information.

VSA won't allow you set a web reference. You can compile the proxy created by Visual Studio and reference that.
|||

Jay is absolutely correct. Script Task uses VSA as its dotnet editor and that does not support web references.

Although it doesn't help you much now...the functionality you are after will be in katmai. In the meantime, do what Jay suggested.

-Jamie