EXPORT DES DONNÉES SQL SERVER 2000 VERS ACCESS : UTILISATION ET EXECUTION D'UN LOT DTS

Imports System.Runtime.InteropServices
Imports DTS

'Nom du lot = package = "ExportAccess"
Public Class ExportDTS

	Function Execute(ByVal package As String) As String

		Dim pkg As DTS.Package
		Dim message As String = "Export ok"

		Try
			pkg = New DTS.Package
			'Debut - set up events sink
			Dim cpContainer As UCOMIConnectionPointContainer
			cpContainer = CType(pkg, UCOMIConnectionPointContainer)
			Dim cpPoint As UCOMIConnectionPoint
			Dim PES As PackageEventsSink = New PackageEventsSink
			Dim guid As Guid = _
			 New Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5")
			cpContainer.FindConnectionPoint(guid, cpPoint)
			Dim intCookie As Integer
			cpPoint.Advise(PES, intCookie)
			'Fin - set up events sink
			pkg.LoadFromSQLServer("Nom du server", "User", "Password", _
			 DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _
			 "", "", "", package, Nothing)
			pkg.Execute()
			pkg.UnInitialize()
			pkg = Nothing
			cpPoint.Unadvise(intCookie)
			cpPoint = Nothing
			cpContainer = Nothing
			PES = Nothing
		Catch exc As System.Runtime.InteropServices.COMException
			message = exc.Message()
		Catch exc As Exception
			message = exc.Message()
		Finally
		End Try

		Execute = message

    End Function


End Class

'Permet de suivre l'execution du lot DTS
Public Class PackageEventsSink
    Implements DTS.PackageEvents

    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
        Console.WriteLine(" 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
        Console.WriteLine(" 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
        Console.WriteLine(" 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
        If EventSource.Length > 0 Then
            Console.WriteLine(" OnQueryCancel in {0}; pbCancel = {1}", _
                EventSource, pbCancel.ToString)
        Else
            Console.WriteLine(" OnQueryCancel; pbCancel = {0}", pbCancel.ToString)
        End If
        pbCancel = False
    End Sub
    Overridable Overloads Sub OnStart(ByVal EventSource As String) _
            Implements DTS.PackageEvents.OnStart
        Console.WriteLine(" OnStart in {0}", EventSource)
    End Sub

End Class


'-----------------------------------------------------------
'Exemple d'utilisation de ces class dans un fichier ASPX.VB
'Penser à créer le control hyperlink dans l'aspx nommer "HL_Export"
'---------------------------------------------------------------------------------------

Imports System.IO

Public Class export
    Inherits System.Web.UI.Page

'Execution de l'export via un lot DTS
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

		'Nom du fichier final
		Dim final As String = "final.mdb"

		'Nom du fichier d'export temporaire
		Dim file As String = "model.mdb"
		
		'Execution du lot DTS
		Dim export As New ExportDTS
		export.Execute("ExportAccess"))
		
		'Renommage du fichier d'export
                                File.copy("D:\TempFile\" & file, D:\tempFile\" & final)
                                File.delete("D:\tempFile\" & file)

		'Téléchargement du fichier
		HL_Export.NavigateUrl = "http://localhost/TempFile/" & final
		HL_Export.Text = "Télécharger"

End Sub

End Class