jump to navigation

SSIS, C#, and Class Libraries February 4, 2010

Posted by Phill in Other Stuff.
Tags: , , , ,
comments closed

The past couple of weeks I’ve not been working in Java, I’ve actually been using SQL Server Integration Services. We are in the process of converting an old C++ based application to Java, with a SQL Server backend. Some of the old export processes that were previously written in C++ we have decided to re-write using SSIS.

I won’t bore you with the details, but I do want to blog about the way C# is used in SSIS. You can create ‘Script Tasks’ which basically run a piece of code for you. You can write it in VB.NET or C#.

Due to the particular export I was running, a lot of the logic needed to go into the C# code. The issue is, SSIS doesn’t make it easy for you to re-use code: essentially, when you create a script task you are creating a whole new C# project. This isn’t good.

What I did (after taking some advice from Stack Overflow), is create a class library. Being new to SSIS and the world of C# in general, I had to do a fair bit of trial and error to get it working. The best tutorial I found was here. Those steps were enough to get me started.

There are a few other things to mention as well:

  • For some reason, SSIS 2008 seems to be stuck in the world of .NET 2.0. I don’t know why, but that is why you have to copy the .dll to the .NET 2.x folder. You will probably also need to set the project properties (of your class library project) to .NET 2.0.
  • There seem to be two different ways of accessing SSIS variables from within a script, depending on whether your script is part of a Data Flow Task or not. If it’s part of a Data Flow Task, you access them via the ‘Variables’ member – easy enough. (Although remember to set them up as read-only or read-write variables in the task properties first). If your script task isn’t part of a data flow task, you will need to use the Dts variable. This confused me at first!
  • For some reason, rebuilding the Class Library package in Visual Studio seems to mean the Script Tasks using it also need to be recompiled. Update: I’ve found out why – if the version increments, you will need to rebuild the dependent projects. To stop this, you just need to set the class library version to be constant. This was actually the default in my project, but I’d changed it! You can do it from project properties -> Application -> Assembly Information dialog.

In general, it’s been a learning experience – worth doing though! I will hopefully update a bit more when I’ve had a chance to use it more…

Advertisements

CallableStatements, JdbcTemplate and jTDS October 15, 2008

Posted by Phill in Data Access Layer, General J2EE, Spring.
Tags: , , , ,
comments closed

I’ve been using the jTDS driver to connect to our SQL Server database. We have a stored procedure which I needed to call. In this project I would normally use iBATIS, but this particular call required the features of JdbcTemplate and CallableStatements (there are good reasons which I won’t go into now!)

The stored procedure has one out parameter, and also returns a ResultSet. I thought this would be pretty simple to set up – but all my attempts kept coming up with the error message “Could not set up parameter for remote server” (which the jTDS driver was sending back).

At the time I was using named parameters. What I’ve done is to switch to using positioned parameters (i.e. setString(1, “Parameter”); registerOutParameter(2, Types.VARCHAR)) and that has seemed to fix the problem.

A bit bizarre, but as I couldn’t find any info about this error anywhere on the internet I thought it was worth posting about in case it saves someone else a headache 🙂