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 DTS...bing, bang, boom...now 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"...so 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"...click 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 = ""
Else
Row.ScriptShortName = Row.ShortName
End If
If Row.LongName_IsNull Then
Row.ScriptLongName = ""
Else
Row.ScriptLongName = Row.LongName
End If
If Row.Abbreviation_IsNull Then
Row.ScriptAbbreviation = ""
Else
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 drag...so 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.
Posts
