June 6, 2005 at 9:30 am
I need to, programatically, import a fixed-field file into a sql 2000 database using a DTS package. My old Vb6 code can do this but we are switching over to .NET and I have no idea what libraries, or approach, is appropriate in this new environment. Any ideas, code samples, etc, pointing me in the right direction would be appreciated. (I wonder if a "Professional SQL Server 2005 DTS" book by Brian Knight is in the works?)
Bill
June 7, 2005 at 2:15 am
billsalkin,
I managed to create a VB.NET project which enumerated and fired DTS packages but had a bit of difficulty responding to progress events correctly i.e. when each 1000 rows were imported. The rest works fine though.
Just add a COM reference to Microsoft DTSPackage Object Library and Microsoft DTSDataPump Scripting Object Library from Visual Studio and it will use tlbimp to create managed code wrappers for these COM objects.
The listing below is the file containing the main controller classes from my (rather rushed and definitely not pretty) project which hopefully should have sufficient examples of package enumeration, execution etc to get you started. There are much better examples around, and actually one on MSDN although the link escapes me at the moment.
Hope this helps anyway.
Option Explicit On
Option Strict On
Imports System.Runtime.InteropServices
Imports DTS
Namespace HighwaysAgency.EEDB
Public Delegate Sub PackageEventHandler(ByVal sender As Object, ByVal msg As String)
Public Class DTSEventArgs
Inherits EventArgs
Public EventSource As String = String.Empty
Public ErrorCode As Integer = -1
Public Source As String = String.Empty
Public Description As String = String.Empty
Public Cancel As Boolean = False
Public Sub New()
End Sub
Public Sub New(ByVal EventSource As String, ByVal Source As String, ByVal Description As String, ByVal ErrorCode As Integer, ByVal Cancel As Boolean)
EventSource = EventSource
Source = Source
Description = Description
ErrorCode = ErrorCode
Cancel = Cancel
End Sub
End Class
Public Class DTSPackageController
Private _servername As String = String.Empty
Private _packages As New Hashtable
Public Sub New(ByVal serverName As String)
Me.ServerName = serverName
End Sub
Public Property ServerName() As String
Get
Return _servername
End Get
Set(ByVal Value As String)
_servername = Value.Trim.ToUpper()
End Set
End Property
Public Overloads ReadOnly Property PackageInfo(ByVal packageName As String) As DTSPackage
Get
If Not _packages.ContainsKey(packageName) Then
Throw New NullReferenceException(String.Format("Package with name '{0}' could not located in the loaded collection.", packageName))
End If
Return DirectCast(_packages(packageName), DTSPackage)
End Get
End Property
Public ReadOnly Property Packages() As Hashtable
Get
Return _packages
End Get
End Property
Public Function LoadPackages() As Integer
_packages = New Hashtable
Dim dtsApp As New DTS.Application
Dim dtsPackage As DTS.PackageSQLServer = dtsApp.GetPackageSQLServer(Me.ServerName, String.Empty, String.Empty, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection)
Dim dtsPackages As DTS.PackageInfos = dtsPackage.EnumPackageInfos(String.Empty, True, String.Empty)
For Each dtsInfo As DTS.PackageInfo In dtsPackages
If Not _packages.ContainsKey(dtsInfo.Name) Then
Dim p As New dtsPackage(dtsInfo, Me)
_packages.Add(dtsInfo.Name, p)
End If
Next
Return _packages.Count
End Function
End Class
Public Class DTSPackage
Protected _packageInfo As DTS.PackageInfo
Protected _packageController As DTSPackageController
Protected _package As DTS.PackageClass
Public Event OnPackageEvent As PackageEventHandler
Public _recurse As Boolean
Public _dir As IO.DirectoryInfo
Public _fileFilter As String
Public _globalVariable As String
Public _connectionName As String
Public _log As PackageDataSet.Log
Public _ds As PackageDataSet
Private cpPoint As UCOMIConnectionPoint
Private intCookie As Integer
Private PES As PackageEventsSink
Private logCn As SqlClient.SqlConnection
Private logCmd As SqlClient.SqlCommand
Public Sub New(ByVal packageInfo As DTS.PackageInfo, ByVal packageController As DTSPackageController)
_packageInfo = packageInfo
_packageController = packageController
End Sub
Public ReadOnly Property PackageInfo() As DTS.PackageInfo
Get
Return _packageInfo
End Get
End Property
Public Sub Load()
_package = New DTS.PackageClass
_package.LoadFromSQLServer(_packageController.ServerName, String.Empty, String.Empty, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, String.Empty, String.Empty, String.Empty, PackageInfo.Name, System.Reflection.Missing.Value)
End Sub
Public ReadOnly Property DataConnection(ByVal connectionName As String) As DTS.Connection
Get
For Each con As DTS.Connection In _package.Connections
'Diagnostics.Debug.WriteLine(String.Format("{0} - {1}", con.Name, connectionName))
If String.Compare(con.Name, connectionName, True) = 0 Then
Return con
End If
Next
End Get
End Property
Public Sub BeginExecutePackageForFiles()
Try
Dim cpContainer As UCOMIConnectionPointContainer = CType(_package, UCOMIConnectionPointContainer)
cpContainer.FindConnectionPoint(New Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5"), cpPoint)
PES = New PackageEventsSink
cpPoint.Advise(PES, intCookie)
AddHandler PES.OnPackageOnError, AddressOf EventHandler
AddHandler PES.OnPackageOnFinish, AddressOf EventHandler
AddHandler PES.OnPackageOnProgress, AddressOf EventHandler
AddHandler PES.OnPackageOnQueryCancel, AddressOf EventHandler
AddHandler PES.OnPackageOnStart, AddressOf EventHandler
RaiseEvent OnPackageEvent(Me, "Bulk Package Execution Started")
ExecutePackageForFiles(_dir, _fileFilter, _globalVariable, _connectionName, _recurse)
RaiseEvent OnPackageEvent(Me, "Bulk Package Execution Complete")
Catch ex As Exception
RaiseEvent OnPackageEvent(Me, "Error occurred " & ex.Message)
Finally
_package.UnInitialize()
cpPoint.Unadvise(intCookie)
RemoveHandler PES.OnPackageOnError, AddressOf EventHandler
RemoveHandler PES.OnPackageOnFinish, AddressOf EventHandler
RemoveHandler PES.OnPackageOnProgress, AddressOf EventHandler
RemoveHandler PES.OnPackageOnQueryCancel, AddressOf EventHandler
RemoveHandler PES.OnPackageOnStart, AddressOf EventHandler
End Try
End Sub
Public Sub ExecutePackageForFiles(ByVal dir As IO.DirectoryInfo, ByVal fileFilter As String, _
ByVal globalVariable As String, ByVal connectionName As String, _
ByVal recurse As Boolean)
If recurse Then
For Each sourceDir As IO.DirectoryInfo In dir.GetDirectories()
ExecutePackageForFiles(sourceDir, fileFilter, globalVariable, connectionName, recurse)
Next
End If
For Each sourceFile As IO.FileInfo In dir.GetFiles(fileFilter)
_package.GlobalVariables.Item(globalVariable).let_Value(sourceFile.Name)
DataConnection(connectionName).DataSource = sourceFile.FullName
RaiseEvent OnPackageEvent(Me, String.Format("Package Execute Started - {0}", sourceFile.Name))
_package.Execute()
RaiseEvent OnPackageEvent(Me, String.Format("Package Execute Complete - {0}", sourceFile.Name))
Next
End Sub
Private Sub EventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
Dim msg As String = String.Empty
If e.ErrorCode = -1 Then
If e.Description.Length > 0 Then
msg = String.Format("{0} - {1}", e.EventSource, e.Description)
Else
msg = String.Format("{0} - {1}", e.EventSource, e.Source)
End If
Else
msg = String.Format("ERROR: {0} - {1} - {2} - {3}", e.EventSource, e.ErrorCode, e.Source, e.Description)
End If
'_ds.Msgs.AddMsg(DateTime.Now, _log.LogID, msg, _log)
Diagnostics.Debug.WriteLine(msg)
RaiseEvent OnPackageEvent(Me, msg)
End Sub
Private Delegate Sub PackageOnErrorEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
Private Delegate Sub PackageOnFinishEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
Private Delegate Sub PackageOnStartEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
Private Delegate Sub PackageOnProgressEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
Private Delegate Sub PackageOnQueryCancelEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
Private Class PackageEventsSink
Implements DTS.PackageEvents
Public Event OnPackageOnError As PackageOnErrorEventHandler
Public Event OnPackageOnFinish As PackageOnFinishEventHandler
Public Event OnPackageOnStart As PackageOnStartEventHandler
Public Event OnPackageOnProgress As PackageOnProgressEventHandler
Public Event OnPackageOnQueryCancel As PackageOnQueryCancelEventHandler
Overridable Overloads Sub OnError(ByVal EventSource As String, _
ByVal ErrorCode As Integer, ByVal Source As String, _
ByVal Description As String, ByVal HelpFile As String, _
ByVal HelpContext As Integer, ByVal IDofInterfaceWithError As String, _
ByRef pbCancel As Boolean) Implements DTS.PackageEvents.OnError
Dim e As New DTSEventArgs
With e
.EventSource = EventSource
.ErrorCode = ErrorCode
.Source = Source
.Description = Description
End With
RaiseEvent OnPackageOnError(Me, e)
Console.WriteLine(String.Format(" OnError in {0}; ErrorCode = {1}, Source = {2}," & _
" Description = {3}", EventSource, ErrorCode, Source, Description))
End Sub
Overridable Overloads Sub OnFinish(ByVal EventSource As String) _
Implements DTS.PackageEvents.OnFinish
Dim e As New DTSEventArgs
e.EventSource = EventSource
e.Description = "Package Execution Finished"
'RaiseEvent OnPackageOnFinish(Me, e)
'Console.WriteLine(String.Format(" OnFinish in {0}", EventSource))
End Sub
Overridable Overloads Sub OnProgress(ByVal EventSource As String, _
ByVal ProgressDescription As String, ByVal PercentComplete As Integer, _
ByVal ProgressCountLow As Integer, ByVal ProgressCountHigh As Integer) _
Implements DTS.PackageEvents.OnProgress
Dim e As New DTSEventArgs
e.EventSource = EventSource
e.Description = ProgressDescription
RaiseEvent OnPackageOnProgress(Me, e)
'Console.WriteLine(String.Format(" OnProgress in {0}; ProgressDescription = {1}", _
'EventSource, ProgressDescription))
End Sub
Overridable Overloads Sub OnQueryCancel(ByVal EventSource As String, _
ByRef pbCancel As Boolean) Implements DTS.PackageEvents.OnQueryCancel
Dim e As New DTSEventArgs
e.Cancel = pbCancel
If EventSource.Length > 0 Then
e.EventSource = EventSource
'Console.WriteLine(String.Format(" OnQueryCancel in {0}; pbCancel = {1}", _
'EventSource, pbCancel.ToString))
Else
'Console.WriteLine(String.Format(" OnQueryCancel; pbCancel = {0}", pbCancel.ToString))
End If
'RaiseEvent OnPackageOnQueryCancel(Me, e)
pbCancel = False
End Sub
Overridable Overloads Sub OnStart(ByVal EventSource As String) _
Implements DTS.PackageEvents.OnStart
Dim e As New DTSEventArgs
e.EventSource = EventSource
e.Description = "Package Execution Started"
'RaiseEvent OnPackageOnStart(Me, e)
'Console.WriteLine(String.Format(" OnStart in {0}", EventSource))
End Sub
End Class
End Class
End Namespace
June 7, 2005 at 2:51 am
I do not have much personal experience with DTS, but I can provide some links I think could be helpful.
SQLDTS.com should probably have all the answers you need.
Regarding Integration Services (formerly known as DTS) in SQL Server 2005, check out the sibling site SQLIS.com. And for upcoming books I guess The Rational Guide to Scripting with SQL Server 2005 Integration Services Beta Preview by Donald Farmer (I think he is Program Manager of IS or similar) would be a good read.
June 7, 2005 at 7:27 am
Well, there is two ways to approach this: 1. Create a COM wrapper that .net can call. That way you could use your existing code without rewriting it to managed code. 2. Option two is obviously to rewrite the code.
To use option 1, use the tlbimp.exe found in the .net framework bin folder.
Option two, here is a link to a webcast that I've found:http://support.microsoft.com/default.aspx?kbid=810581
Hope it helps.
Paul Krasucki, MCAD
June 7, 2005 at 10:48 am
You can certainly call the SQL Com components like you did in your previous VB6 app, so your previous app and knowledge are still useful.
I would question why you would use DTS to do this, and why not use SQLDMO and the BulkObject to import the data. Much, much quicker and easier to code, and you do not have to depend on part of your code in DTS and part in a VB program.
Mark
June 7, 2005 at 2:11 pm
First, I was overwhelemned by the number of interesting responses to this proglem. It will take me some time to absorb it all.
Regarding, SQL-DMO: I don't think our programmers are familiar with the SQL-DMO model, etc, but maybe it's time they became familiar.
Again, thanks to everyone for all the help! What a group!
Bill
June 7, 2005 at 2:16 pm
Re: SQLDMO.
Yes, they should.
Everything that Enterprise Manager does, is done with SQLDMO.
Everything that Query Analyzer does, is done with SQLDMO.
Everything that DTS does, is done in VBScript.
Mark
June 7, 2005 at 4:57 pm
Mharr,
>> and why not use SQLDMO and the BulkObject to import the data.
I have been studying SQl-DMO all day. (So I'm a real expert, ha, ha, ha) But I can say this much: some of it is very poorly documented! Documentation for the entire area on Bulk-related matters (including bcp itself) is little more than a skeleton. Haven't the MS guys ever heard of code or examples? The poor documentation of itself will encourage some to bypass bulk import tools (including that offered by sql-dmo)
Bill
June 8, 2005 at 7:05 am
Here's a little bit of VBScript I pulled from a larger script I've done (so some declares might be missing):
' any used constants from the type library must be explicitly declared Const SQLDMODataFile_TabDelimitedChar = 2 Const SQLDMOBCPDataFile_Char = 1 Const SQLDMOBCPDataFile_Native = 2 Const SQLDMODataFile_NativeFormat = 4 Const SQLDMOBackup_Database = 0 Dim oServer ' the SQL Server object Dim oDatabase ' the target database to use Dim oBCP ' the BCP object Dim nRows ' the number of rows returned from bcp
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.EnableBcp = True
oServer.LoginSecure = True
oServer.Connect SERVERNAME'Setup BCP Object
Set oBCP = CreateObject("SQLDMO.BulkCopy")
Set oDatabase = oServer.Databases(DBNAME)
If Not oDatabase.DBOption.SelectIntoBulkCopy Then
oDatabase.DBOption.SelectIntoBulkCopy = True
End If
oBCP.DataFileType = SQLDMODataFile_NativeFormat
oBCP.ImportRowsPerBatch = 1000
oBCP.MaximumErrorsBeforeAbort = 1
oBCP.ServerBCPDataFileType = SQLDMOBCPDataFile_Native
oBCP.UseExistingConnection = True
oBCP.DataFilePath = sPath & "CCI.bcp"
' start the importing process
nRows = oDatabase.Tables("CCI").ImportData(oBCP)
WScript.Echo " Imported " & nRows & " rows."
oDatabase.Tables.Refresh
Hope this helps some.
Mark
June 8, 2005 at 9:52 pm
That does help. I'll be toying with the script tomorrow.
Thanks,
Bill
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply