0

Control Flow - SSIS

The following block of code is within a script task (perform dump) in a SSIS package. The script is recursively called for a list of tables in the database:

Function ImportSQLServerToAccess(ByVal sMDBFile As String, ByVal sSource As String, ByVal sDestination As String, ByVal sServer As String, ByVal sDatabase As String) As Boolean
    Dim AccessConn As System.Data.OleDb.OleDbConnection
    If System.Environment.OSVersion.Version.Major >= 6 Then
        AccessConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sMDBFile + "")
    Else
        AccessConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sMDBFile + "")
    End If

    AccessConn.Open()

    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO " + sDestination + "  FROM [" + sSource + "] IN '' [ODBC;Driver={SQL Server};Server=" + sServer + ";Database=" + sDatabase + ";Trusted_Connection=yes];", AccessConn)

    AccessCommand.ExecuteNonQuery()
    AccessCommand.Dispose()
    AccessConn.Close()
End Function

The first time the perform dump script task is called, the query executes fine and dumps the data into mdb, but fails every other time after that with the exception 'ODBC--connection to '{SQL Server}DEV01' failed'. I am not sure why the connection fails the second time but succeeds the first time. Is there any suggested workaround for this?

enter image description here

Aravindh
  • 137
  • 3
  • 13
  • Have you tried setting the `RetainSameConnection` property of the OLE DB Connection to `true`? I am thinking since the initial connection attempt succeeds, retain that connection for the remainder of your processing. – Mike Henderson Jul 29 '13 at 12:17
  • Hi Mike, Thanks for your reply. This function creates a new oledb connection for each mdb file in a list, the file gets created in a previous step, thats why I am not creating a new connection each time since the destination mdb file changes for each dump. – Aravindh Jul 29 '13 at 17:43
  • Your illustration indicates a break you may have been set. Were you able to isolate the line where it complains? – Mike Henderson Jul 29 '13 at 19:09
  • I have added a screenshot of the point where it actually fails. This happens every other time except the first time. – Aravindh Jul 30 '13 at 13:38
  • I am not all that convinced this is the problem, but maybe look at the `Close` and `Dispose` calls. [reference to Close and Dispose question](http://stackoverflow.com/questions/61092/close-and-dispose-which-to-call). – Mike Henderson Jul 30 '13 at 14:24
  • Doesnt help..but thanks anyways – Aravindh Jul 30 '13 at 18:15
  • Have you ruled out permissions or locking issues? When Access is giving me heartburn it often ends up related to locks. – Mike Henderson Jul 30 '13 at 18:23
  • The exception says connection to sql server failed, so I guess the issue is the other way round. – Aravindh Jul 30 '13 at 19:09
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/34482/discussion-between-mike-henderson-and-aravindh) – Mike Henderson Jul 30 '13 at 19:14

0 Answers0