CODEWITHSUNDEEP
sqlsql-server-2005ssisdata-conversion
Greg
Greg

Reputation: 3522

SSIS User-Defined Types

We are trying to import data into our SQL 2005 database from flat files using SSIS packages.

Our database has a CLR User-Defined Type that we use to hold some related information, lets call it dbo.CustomType.

To create an object of dbo.CustomType in SQL, I can do the following:

DECLARE @Decimal1   DECIMAL(18,6),
        @Int1       INT,
        @Decimal2   DECIMAL(18,6),
        @Int2       INT,
        @Decimal3   DECIMAL(18,6)


SELECT  @Decimal1   = 1.5,
        @Int1       = 1,
        @Decimal2   = 2.5,
        @Int2       = 2,
        @Decimal3   = 3



SELECT 
    CONVERT(
                dbo.CustomType,
                CONVERT(VARCHAR(20), @Decimal1) + ';' + 
                CONVERT(VARCHAR(20), @Decimal2) + ':' +
                CONVERT(VARCHAR(2), @Int1) + ';' +
                CONVERT(VARCHAR(20), @Decimal3) + ':' +
                CONVERT(VARCHAR(2),  @Int2)
            )

In SSIS I have a dataset that has all of the columns represetend by the variables, but how can I combine them and convert them to dbo.CustomType?

====

UPDATE:

I was able to insert the custom type when using an insert statement inside a OLE DB Command object instead of the OLE DB Destination.

SO:

INSERT INTO Greg
VALUES (CONVERT(dbo.CustomType, '1;1:1;1:1;'))

works, BUT

INSERT INTO Greg
VALUES (Convert(dbo.Customtype, ?))

gives an error

[OLE DB Command [3103]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".

It seems to be trying to convert my string column to byte stream, but whenever I try to change Param_0 to String in the advanced editor it keeps changing back to byte stream

Upvotes: 2

Views: 807

Answers (1)

Piotr Sobiegraj
Piotr Sobiegraj

Reputation: 1783

You can try do it in two steps:

  1. In Data Flow Task insert this data to stagging table to separate columns,
  2. Then in Execute SQL Task insert data from stagging table to destination with convert

Example:

insert into DestinationTable
select convert(dbo.Customtype, DecimalColumn1, DecimalColumn2, IntColumn1, DecimalColumn3, IntColumn2)
from StaggingTable

Upvotes: 2

Related Questions