September 24, 2008 at 4:00 pm
First my problem. This is of course is my first attempt to use BI. I want to be able to move my package across several different nodes: My development box, testing, stage, and production. The databases on these boxes are all over the place and I can see no way to change the Data Sources as I move the package.
So, I decide to use ODBC. That way I can change the DSN definitions on each box and the package can remain static. 🙂
After much tinkering I have managed to create an ODBC Data Reader BUT, I'm really stumped with the Destination! The data sources are all old .DBF files so that has to be ODBC, but am I going about this all the wrong way with the SQL Server 5000 destination? The next phase of the project will require that I write to .DBFs, so the ODBC output thing must be resolved.
September 24, 2008 at 4:54 pm
Oh, did I forget to mention that there was a £10,000 reward for the answer? No? Aw, that's too bad because I figured it out.
The trick is to use a Script Component for the output. Here's the code that goes with it:
' 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
Imports System.Data.Odbc
Public Class ScriptMain
Inherits UserComponent
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
Dim connectionString As String
connectionString = " - your DSN goes here - "
odbcConn = New OdbcConnection(connectionString)
odbcConn.Open()
End Sub
Public Overrides Sub PreExecute()
odbcCmd = New OdbcCommand("INSERT INTO Person.Address2(AddressID, City) " & _
"VALUES(?, ?)", odbcConn)
odbcParam = New OdbcParameter("@addressid", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)
odbcParam = New OdbcParameter("@city", OdbcType.NVarChar, 30)
odbcCmd.Parameters.Add(odbcParam)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
With odbcCmd
.Parameters("@addressid").Value = Row.AddressID
.Parameters("@city").Value = Row.City
.ExecuteNonQuery()
End With
End Sub
Public Overrides Sub ReleaseConnections()
odbcConn.Close()
End Sub
End Class
It's pretty self explanitory. You just do it manually.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply