Archive for Coding

C# vs VB.NET

Recently my co-worker and I we’re discussing the use of C# for all future development on Task Factory. His reasoning made sense in that he said that most developers (including Microsoft employees and newbies) are overwhelmingly using C# vs VB.NET. Currently Task Factory is written in mostly VB.NET with the SCD component written in C#. To me, there isn’t that big of a difference between the two. Usually C# is ahead of the VB.NET team by about a year on new features but other than that, same thing.

Of course the syntax is different but they both use the same .NET libraries and both compile down to IL.

To me, I couldn’t care less about what language we develop Task Factory in. I’ve written applications in both C# and VB.NET and I can switch between the two very easily.

We got on the subject of how funny it is that some developers will “frown” when you tell them they have to develop in VB.NET. I never understood that. Like C# is this magical language and VB.NET is a script kiddie language. I always get a chuckle out of developers are adamant about using one language over the other. If you do a quick search on on one of the search engines for vs C# you’ll quickly see what I mean.

Some say C# just feels more “solid” because of the history of vb and vbscript. Some say VB.NET makes a lot more sense to them because of the use of curly braces in C#. One C# purist said that VB.NET doesn’t feel “complete”. As if Microsoft would ever put out any product that isn’t “complete”. Oh wait… 🙂

I say they’re both wrong. Both languages are so easy to work with that you really have no excuse as a .NET developer to not know both languages. I learned C# in one week. Yep, no lie. It’s that easy. I read Jessie Liberty’s Programming C# book over a week long vacation and started writing C# programs the next week.

Here is a REALLY great overview of C# vs VB.NET that goes over 24 different sections of both languages and shows you the difference between the two languages.

So my suggestion to you purists out there is that you stop bickering about “What language is better” and learn both. You’ll be a more “solid” programmer for it.

Upgrading SSIS Custom Components to 2012

Recently we had to upgrade all of our components in Task Factory to utilize the new 2012 SSIS assemblies for integration into SQL 2012. I learned a couple of things along the way that you should know about if you’re doing the same.

Even though Parameters are available using the Variables class (i.e. $Project::MyFile), they cannot be written to from the Variables class. Meaning Variables(“$Project::MyFile”).Value = “C:\ssis\test.xml” will fail.

Parameters cannot be written to within a Task but can be within a PipelineComponent. The reason for this is PipelineComponent has a reference to the Parameters class whereas the Task class does not. You can only access Parameters from the Variables class in a Task.

All Parameters are available for both the Project and Package level parameters within the Variables collection. Meaning if you list all of your variables, they will be part of the Variables collection. The namespace for the parameters available in the Variables collection is either $Project or $Package

There isn’t a need to “Choose Items” for new Control Flow and Data Flow tasks. If you put them in the correct location of %programfiles%\Microsoft SQL Server\110\Dts\Tasks and %programfiles%\Microsoft SQL Server\110\Dts\PipelineComponents, they will automatically show up in the SSIS Toolbox.

You will not have to change very much of your code to get your components to work in SQL 2012. All of the interfaces are the same for both Tasks and PipelineComponents. You WILL have to add references to the SQL 2012 assemblies like Microsoft.SqlServer.DTSPipelineWrap and Microsoft.SqlServer.DTSRuntimeWrap.

You will need to use .NET 4.0 for SQL 2012 Tasks and PipelineComponents as well as use GACUTIL from .NET 4.0 to add the assemblies to the GAC. GACUTIL for .NET 4.0 is usually located in %programfiles%\Microsoft SDKs\Windows\v7.0a\.NETFX 4.0 Tools\.

That’s it for now. I will update this as we continue to build Task Factory for SQL 2012.

Using GetPackagesInfos2

Recently I was creating the Advanced Execute Package task for Task Factory. One of the ways to retrieve a list of packages from SSIS is by using the GetPackageInfos method. However, the problem with GetPackageInfos is that it requires you to pass in a username and password. With the use of an OLEDB or ADO SSIS connection manager, this isn’t possible. You can grab the username from the SSIS connection manager connection string but you cannot retrieve the password. In comes GetPackageInfos2 which is an undocumented (of course it is!) way of retrieving packages from the MSDB server. The function signature for GetPackageInfos2 is:

Public Function GetPackageInfos2 ( _
	strFolder As String, _
	connectionObject As Object _
) As PackageInfos

Notice the second parameter is an Object type? Yeah, uh… good luck finding out what it is on your own. Fortunately I figured it out and was able to use it in my project… and so that you can just read this blog and not waste a few hours looking for an answer.

Turns out the connectionObject parameter is looking for an SSIS connection manager object. So to get the GetPackageInfos2 to work you simply grab a reference to a connection manager and pass the connection manager object into the function. mConnections is an a Microsoft.SqlServer.Dts.Runtime.Connections object.

Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
Dim cm As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = mConnections("MyConnectionManager")
' pass in the root folder of "/"
Dim packages As Microsoft.SqlServer.Dts.Runtime.PackageInfos = app.GetPackageInfos2("/", cm)

For Each package as PackageInfo in packages

So it’s as simple as that. Grab the connection manager reference from an OLEDB or ADO.NET connection managers, pass it into GetPackageInfos2 and you got your reference.

Leave a comment if you have any questions.