Friday, October 3, 2008

It can't be this hard...

That was my lament as I worked through my first SQL Server Integration Services [SSIS]utility...something that was so simple in SQL Server 2000's Data Transmformation Services [DTS] has been pretty frustrating to reproduce using the SSIS tools integrated with Visual Studio 2005. We use Visual Studio 2008 Team Foundation Server with eScrum, so needless to say I found it pretty disappointing that SSIS still only works with VS2005...which means that the SSIS Package, the DB Scripts, etc. cannot be put under Source Control.

First, let me describe my development environment...I'm running SQL Server 2005 locally on a Windows Server 2008 x64 machine. On top of that, we're a Microsoft shop that is learning to use Scrum in our environment and, along those lines, we're starting to use VS 2008 with Team Foundation Server and TDD. I have an existing database that was very poorly designed and I'm working on refactoring the design to get rid of the major smells. One of the things I'm working on is taking some generic lists [like countries, states, months, weekdays, etc.] and putting them into tables. In SQL Server 2000, this would be a breeze using, bang, we're working with SQL 2005 and the Business Intelligence Studio...VS 2005 with a bow...and life isn't so easy.

My first task is to take a list of countries [swiped from the US State Department website and -->view source --> copy/paste --> some Excel voodoo --> and tada, a delimited list of countries...the database design is configured NOT to allow NULL values in any of the fields and default [empty strings] are specified...the Dept. of State list has a number of NULL values for countries without a "long name" my first challenge is that I need to interrogate each row and replace any Null values with an empty string [because, although there are defaults defined in the database, and although I know that SQL Server will allow me to specify "No Value" when inserting a record, it will not allow me to explicitly insert a NULL into a column that expressly forbids it]. After getting an Excel Connection Manager, and an OleDB Connection Manager [a SQL Server connection didn't work because of my 64bit environment...more can be found HERE and HERE] on the Data Flow canvas and configured to point to my Excel Spreadsheet [Named Range], drag a "Script Component" Transformation onto the canvas, drag the green arrow to the script component and configure it...most notable, the Script menu has a button to "Design Script" this button and viola, a VB.NET class opens...

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.ShortName_IsNull Then
Row.ScriptShortName = ""
Row.ScriptShortName = Row.ShortName
End If
If Row.LongName_IsNull Then
Row.ScriptLongName = ""
Row.ScriptLongName = Row.LongName
End If
If Row.Abbreviation_IsNull Then
Row.ScriptAbbreviation = ""
Row.ScriptAbbreviation = Row.Abbreviation
End If

End Sub
End Class

This is somewhat familiar as it is similar to the ActiveX Script task type in DTS. Next, I need to convert the Unicode strings that come out of Excel to SQL VarChar data in order to insert it into my table. This part was seamlessly handled by DTS, now I need to explicitly convert it or else I get an error indicating that Unicode Strings cannot be converted to VarChar. What a next I drag a "Data Conversion" Transformation onto the canvas and map the input columns to the output columns...finally I connect the Ouput of the conversion activity to the OleDb connection...after a day of messing around with this, I finally got it to perform the simplest task...something that would have literally taken 5 minutes in DTS.


ikeshianagamine said...

This is nicely known|a broadly known} firm that has its casino platform with 10 other in style casinos running on it. Betmaster is a casino operator and an online bookmaker established in Estonia. The casino has a license from Curacao that ensures the fairness of the games. What is extra, the casino utilizes the latest SSL-encryption that ensures protection and confidentiality for his or her customers. The Arari Village Theme Park is an exhibition space the place guests can stay a couple of of} days and see what the individuals had been like in a rural village during the Joseon Dynasty.

galaineuhde said...

Just like the unique Ina, the Ina 2 can stimulate your clitoris while additionally offering internal stimulation that's oh-so-satisfying. The internal component actually strikes up dildoes and down, similar to how a companion's fingers would. But with this latest version of the Ina Wave, there are more ranges of intensity AND it is 30 percent more highly effective.

quinbeevacha said...

You purchase virtual playing 토토사이트 cards with pre-filled numbers, and after starting the sphere, you find out that are the profitable combos. The numbers from 1 to 90 participate in the draw, and 15 of them are picked at random. The most profitable on-line bingo is "Rio Bingo" - a product of PariPlay.

abbigaelzaffino said...

Macau's casinos have been dropping tens of millions of dollars monthly outcome of} coronavirus restrictions because the special administrative area follows China's 'dynamic zero COVID' policy, which goals to curb all outbreaks. Sign Up NowGet this delivered to your inbox, and extra data about our services and products. Was down 1.18% in its ultimate hour of trade while gaming stocks rebounded following news of an e-visa system for mainland Chinese guests to Macao. It provides a much-needed increase to Ocado at a 카지노 time when grocery sales are slowing, and its shares jumped 33% to 632p on the news. There have been no stories of harm or accidents from areas where the alerts have been issued. Bullet train providers in these regions have been quickly suspended following the missile alert before resuming shortly.