Sql Customizing Report: Custom Object in SSIS

I wrote a C# class that has a couple of methods that can be called to grab data out of a flat file and import it to a DB. This class was written for a web applicaiton originally. (class has many complex business rules in it)

Now I would like to use this same class in a SSIS package.

Is it possible in a script task to create a new object with my class and use it's methods?

In the end all I want SSIS to do is create my object, call a method in that object and be done.

If I was doing this in DTS, I would have to create an .Net EXE that used my class but I'm hoping to avoid that in SSIS.

Frenchy

The simplest way would be to use the class as-is, and just reference it from a Script Task, which gives you a VB.Net environment to create your class and call your methods as required. You can also leverage connections and variables for a more integrated and dynamic approach if required. You will need to place your assembly in a defined folder to get the Add Reference to work on VSA, the script environment used by SSIS - http://msdn2.microsoft.com/en-us/library/ms136007.aspx|||

I also need the same. My code is in C# and that could not be converted in VB.NET. Did you find any way to invoke C# class ? Please let me know also If you find any solution.

Thanks,

Pooja

|||

Pooja B wrote:

I also need the same. My code is in C# and that could not be converted in VB.NET. Did you find any way to invoke C# class ? Please let me know also If you find any solution.

Thanks,

Pooja

Darren answered this question already (above). Is anythig not clear?

-Jamie

|||

Thanks Jamie. No its clear but I am totally new to SSIS so dont have that much understanding. Can you send me some sample code that is accessing already existing classes in Script task.

Thank you so much...

Pooja

|||If you follow the steps in Darren's post (adding a reference to the .dll that holds your custom code), then you just create a new instance of the object and begin using it - just like you would in any .NET code.|||

For adding a reference to required dlls we need to make them strongly typed. I made them strongly typed and added them to ../windows/microsoft.net/framework/2.0.., then I am able to run my code sucessfully but could not access these dlls in SSIS package. When I add these dlls in GAC then I could access the dlls in SSIS packages but then my application starts giving errors as it gould not refer some of the dlls because of making it strongly named. Can you please suggest what could be the problem. I am using microsoft enterprise libraries in my code.

Thanks,

Pooja

|||This is really a question for a different forum - you might try one of the groups under .NET Development.|||

PLEASE help us to help you-

What application gives these errors?

What are the errors?

An existing application that referenced non-strong named assemblies should keep working fine, assuming you have left the old assemblies in place. It would however make senses to updae your code to use the new strong named version so that all code, SSIS and other applictions, share the same common assemblies.

|||

Thanks for your reply. I am able to fix that problem. In my appplication as I strongly named the assemblies, it was not able to find that in GAC so i need to change that in my app.config file.

I am new to SSIS, Can you please tell me how to specify configuration settings for SSIS. Like my cofiguration settings are like this.

</configSections>

<add

name="abc_Dev"

providerName="System.Data.SqlClient"

connectionString="Data Source=;Initial Catalog=;User ID=; Password=" />

<add

name="abc_Prod"

providerName="System.Data.SqlClient"

connectionString="Data Source=;Initial Catalog=;User ID=; Password=;" />

</connectionStrings>

</configuration>

Thanks,

Pooja

|||Take a look at "Package Configurations" in SQL Server Books Online.|||

I have a similar scenario - EntLib and SSIS. I'm afraid I haven't found a way to use the same configuration file - both SSIS and EntLib have their own ideas about file format.

What I do is to manage the EntLib configuration files in the usual way (as in your code example above); and for the SSIS packages I use XML package configurations. You can find out more about these at http://msdn2.microsoft.com/en-us/library/ms141682.aspx.

|||

I specified the database configuration like this but how to specify the configsections for EntLib in XML configuration secion.

Data Source=[Server Name];

User ID=[user name];

Initial Catalog=[database name];

</ConfiguredValue>
</Configuration>

</DTSConfiguration>

Can you please show me the dtsconfig xml file that you used in your application.

Thanks,

Pooja

|||

SSIS configurations and Enterprise Library configurations are clearly not the same, SSIS has no concept of .Net configurations. You will never be able to get SSIS to use Ent Lib config sections.

The best you can ever do is get .Net code hosted in SSIS to use Ent Lib config. This is not the same as making SSIS use Ent Lib or it's config, this is using .Net code. This is important if you have managed code, that uses Ent Lib functions. As covered above, to use external (external to SSIS I mean) assemblies, you need to ensure they are in the GAC, a challenge for first time Ent Lib builds.

For the configuration, think about how any .Net config works, the host process is responsible for loading it. So if you write an assembly which calls Microsoft.Practices.EnterpriseLibrary.ExceptionHandling stuff, and you want the exceptionHandling\exceptionPolicies config stuff to be available, then you need to add that to the config file for the calling application. If you use this assembly in a Script Task, and execute your package through DTExec, then you need to create a DTExec.config file, with suitable entries.

Other hosts of interest will be DtsDebugHost.exe , DtsHost.exe, DtExecUI.exe

|||

I have done in the same way. I added the assemblies in GAC that are using Enterprise libraries and then I am refering those dlls in my SSIS package.

Now you are saying that for configuration I need to create a DTExec.Config file. I am not able to understand how i should define my configuration setttings in this config file.

What I did is as my configuration file is like this

</configSections>

</connectionStrings>

</configuration>

So I created one Connection manager named "abc" for that package and then in package configuration I made the configuration settings like

<DTSConfiguration

<ConfiguredValue>Data Source=[server name];User ID=;Initial Catalog=[database name];</ConfiguredValue>

</Configuration>

</DTSConfiguration>

I have config file named app.config in which i have added only configuration settings for EnLib like this

</configSections>

</configuration>

then I executed it through dtExec like this

EXEC xp_cmdshell 'dtexec /conf "C:\App.Config" /f "C:\Package1.dtsx"'

On executing this I get the exception in code. This exception is coming because it is not able to access the data layer. If I comment the code in which it is accessing database then it work ok.

Please help me. Problem is in configuration file only.

Thanks,

Pooja

Sunday, March 11, 2012

Custom Object in SSIS

No comments:

Post a Comment

Sql Customizing Report

Blog Archive

About Me