Using pgsqlloader in SSIS as a data destination

[MrRimmer]

Hi, I'm fairly new to SSIS, and have been trying to set up a data flow to extract records from a flat file source and then transform and dump the data into a database. Going to SQL Server is of course easy and extremely fast (using OLE.DB and 'fast load') but for Postgres so far I've only managed to use the normal ADO.NET connection manager.

I'd like to use the loader functionality but there doesn't seem to be a GUI way of doing it. Will I have to write a Script Destination task? If so, has anyone done this?

Many thanks,

Arnold

[MrRimmer]

Just to move things along a bit, I've managed to get the loader working within a destination script but it seems that if I open the loader in the PreExecute method and close it in PostExecute (as shown in the sample code) I get an error about more columns than expected being found. By opening and closing in ProcessInputRow it works but is extremely slow (of course).
Is there a limit to the number of rows that can be added (calling NextRow after Setvalue for each column)?

[StanislavK]

As I can understand, you want to import the flat file content to a PostgreSQL database. Am I correct?

Basically, you need to perform the following steps with SQL Server Import and Export Wizard:
- configure reading from the file;
- open a connection to the PostgreSQL server using dotConnect for PostgreSQL;
- configure the type and column mappings from the file to the database table;
- import data.

Please see the corresponding topic of our documentation for the details:
http://www.devart.com/dotconnect/postgr ... _SSIS.html
In the sample, a table is exported from the PostgreSQL database to SQL Server, but the same approach can be used to import the file content to a PostgreSQL server.

Please specify at which of the steps you are encountering a problem, and, if you use some other method of importing data, please describe the actions you are performing in more details.

[MrRimmer]

Thanks for the info. Most of the problems I am having stemmed from input data that contains control characters, which has now been fixed by using a script task to filter out the offending data.
My next problem is that the data flow task does not complete successfully. All records are written to the destination table but then I get the following error right at the end:

Error: 0xC0047062 at Data Flow Task, TR01 [631]: System.InvalidOperationException: This type has a ComVisible(false) parent in its hierarchy, therefore QueryInterface calls for IDispatch or class interfaces are disallowed.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ReleaseConnections()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostReleaseConnections(IDTSManagedComponentWrapper100 wrapper)
Error: 0xC0047018 at Data Flow Task, SSIS.Pipeline: component "TR01" (631) failed the post-execute phase and returned error code 0x80131509.

The script destination looks like this:

Code: Select all

' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Devart.Data.PostgreSql

 _
 _
Public Class ScriptMain
    Inherits UserComponent

    Private WithEvents loader As Devart.Data.PostgreSql.PgSqlLoader
    Dim connMgr As IDTSConnectionManager100
    Dim sqlConn As PgSqlConnection
    Dim iRowCount As Long

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        MyBase.AcquireConnections(Transaction)

        connMgr = Me.Connections.cnPostgres
        sqlConn = DirectCast(connMgr.AcquireConnection(Nothing), PgSqlConnection)

        Me.loader = New Devart.Data.PostgreSql.PgSqlLoader("ssis.tr01", sqlConn)

    End Sub

    Public Overrides Sub ReleaseConnections()
        MyBase.ReleaseConnections()

        connMgr.ReleaseConnection(sqlConn)
        '        sqlConn.Close()

    End Sub

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        '
        ' Add your code here for preprocessing or remove if not needed
        '
        Me.loader.CreateColumns()   ' Build columns form target table
        Me.loader.Open()

    End Sub

    Public Overrides Sub PostExecute()
        MyBase.PostExecute()
        '
        ' Add your code here for postprocessing or remove if not needed
        ' You can set read/write variables here, for example:
        ' Me.Variables.MyIntVar = 100
        ''
        Me.loader.Close()

    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        '
        ' Add your code here
        '

        Me.loader.SetValue("regnum", Row.REGNUM)
        Me.loader.SetValue("tradd1", Row.TRADD1)
        Me.loader.SetValue("tradd2", Row.TRADD2)
        Me.loader.SetValue("tradd3", Row.TRADD3)
        Me.loader.SetValue("tradd4", Row.TRADD4)
        Me.loader.SetValue("tradd5", Row.TRADD5)
        Me.loader.SetValue("traddp", Row.TRADDP)
        Me.loader.SetValue("pstcde", Row.PSTCDE)
        Me.loader.SetValue("std", Row.STD)
        Me.loader.SetValue("teleno", Row.TELENO)
        Me.loader.SetValue("hoind", Row.HOIND)
        Me.loader.SetValue("roind", Row.ROIND)
        Me.loader.SetValue("srctyp", Row.SRCTYP)
        Me.loader.SetValue("ctps", Row.CTPS)
        Me.loader.SetValue("addno", Row.ADDNO)
        Me.loader.NextRow()

    End Sub
End Class

[StanislavK]

Please specify the versions of PostgreSQL server and dotConnect for PostgreSQL you are using.

Also, I send you a sample project in a letter, please specify what should be changed in the sample or send us your test project so that we are able to reproduce the issue. We couldn't reproduce it in our environment.

The table used in the sample is defined as

Code: Select all

CREATE TABLE dept
(
  deptno integer NOT NULL,
  dname character varying(14),
  loc character varying(13),
  CONSTRAINT dept_pkey PRIMARY KEY (deptno)
)

[MrRimmer]

Hi,
Problem has been solved through a process of trial and error with the following change:

Public Overrides Sub ReleaseConnections()
MyBase.ReleaseConnections()

' connMgr.ReleaseConnection(sqlConn)
' sqlConn.Close()

End Sub

Unlike in all the examples I have seen, removing the call to ReleaseConnection seems to have removed the error.
FYI, this is using postgres 8.4.4.0 and 5.00 beta of the driver.

Thanks for your help,

Arnold

[StanislavK]

Glad to see that the problem was resolved.