SQL Kit

This is first of my series on SSIS of Sybase ASE 15 .

1.  Create table to hold this data

--Create a typed and Untyped table
--Typed Table
create table foo (
       book_id                         int                              not null  ,
       book_code                       varchar(16)                      not null  ,
       desk_id                         int                              not null
)
--Untyped Table
Create table bar (
       book_id                         varchar(50)                      not null  ,
       book_code                       varchar(50)                      not null  ,
       desk_id                         varchar(50)                      not null
)
--Error Table
Create table error (
       book_id                         varchar(50)                      not null  ,
       book_code                       varchar(50)                      not null  ,
       desk_id                         varchar(50)                      not null  ,
       error_code                      varchar(256)                         null
)

1. Use these 3 records as your test.csv file

1,AMSTMM,23

2,CBOND,9

CMD,CBOND,DBO

1. Add a new connection to the connection Manager of the type Native OLEDB\Sybase ILEDB Provider
2. Configure the Connection Provider’s Property

1. Add a new Connection Manager for Flat File Source for the CSV file test.csv. Name it CSV

That’s all you need in set up

1. Drag and drop a Data Flow Task and Open it
2. Add a Flat file source component and configure it to point to the CSV connection
3. Drag a OLEDB Destination and configure it to use the Sybase connection and use dbo.bar as the destination table

1. Run the task and it has succeeded your data will be in the table

1. Ok So far so good. Now I want to load it to Typed Table. Drag another dataflow task for this and Open it.
2. Drag a OLEDB Source and Point it to dbo.bar and Drag a OLEDB destination and point it to dbo.foo and connect it and Map the columns. Done !! IS it ? Watch this..

When You execute this task this is what will happen

1. Task will look like this

So it failed !! Ya I expected it to because of the Bad typed row in the untyped. But that is not what I want to show you. This is what I want to show you

1. Log will be saying

    An OLE DB error has occurred. Error code: 0×80020005.

The ProcessInput method on component "typed" (42) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Thread "WorkThread0" has exited with error code 0xC0202009.

1. The Error Message clearly is not really helpful from the Sybase Adapter. Grrrr!!
2. 2.And further down the line you will see this
3. OnInformation,FMDX2622,MARKETS\c001334,Package,{40CB9E75-B873-45C7-8129-D74D85F567FC},{5B329EA9-3798-44A0-B3DC-1EAF61F204B7},07/09/10 11:10:45,07/09/10 11:10:45,1074016267,0x,"component "typed" (42)" wrote 2 rows.

1. Now that it is saying that it loaded 2 rows lets look at the Database.

Huh!! How did the Row -3 with first and last column casted to 0 came here. I did not do any type casting and also log said only 2 rows are inserted.

Well that is either a problem with Sybase Adapter for Oledb for returning and SSIS and the way it treats Sybase data type.

Lets look more deep in the Sybase OLEDB Destination Component.(Right Click à Show Advance Editor)

Book_id is INTEGER column so that’s what the External Column says .. No problem there.

Now look at book_id in  Input Columns

How is that chaged to DT_STR. This is the reason why the load it doing implicit cast. The Bug is with the Sybase Adapter as well which then commits another mistake of returning the wrong count of inserted rows.

Ok So how do I fix it. Well by explicit typing using Data Conversion task. Here is the solution.

1. Add a data conversion task in between Source(bar) and destination(foo) . If you want you can add a error redirect to error

1. Data conversion task basically takes the column from the UnType and casts them to explicit types. On Error while casting it redirects it to error table which is untyped as given above. Correct rows are loaded to Typed.
2. This is what the Data Conversion Task looks like

1. Run this and verify the results. Mine looked my fav color GREEN

1. Logs also return what has actually been done

OnInformation,ash-pc,Foo\c001334,Package,{40CB9E75-B873-45C7-8129-D74D85F567FC},{16316E6B-94DA-4235-B27E-10EF3E472E38},07/09/10 11:31:35,07/09/10 11:31:35,1074016267,0x,"component "typed" (42)" wrote 2 rows.

OnInformation,ash-pc,Foo\c001334,Package,{40CB9E75-B873-45C7-8129-D74D85F567FC},{16316E6B-94DA-4235-B27E-10EF3E472E38},07/09/10 11:31:35,07/09/10 11:31:35,1074016267,0x,"component "Error" (1004)" wrote 1 rows.

1. Database looks like this

Foo

Error

Job Done!!

Loading UnTyped To Typed is a very common pattern and perhaps this will help someone to do this for SYBASE ASE-15 and SSIS. (Both 2005 and 200