Need help creating a custom SSIS script component


I need help creating a custom script component (Destination) which will handle a record update. This component would be used in place of the Ole DB Command, because the command object cannot deal with blob data (this includes text and ntext columns).

I'm using a conditional split to easily identify modified records, so all I need the object to do is to take all fields from the input, and copy them to the destination table.

I'm not concerned about the performance of this, because updates will only be in the 10's and not 100's or more.

Any help would be greatly appreciated!

Jon JaquesInformation TechnologistAsked:
Who is Participating?
Jon JaquesConnect With a Mentor Information TechnologistAuthor Commented:
Okay, I abandoned the whole "update" scheme, performed a full successful import once, and the committed myself to babysitting it until all the bugs were worked out. Apparently, I did a good job of worrying about this project because it only took one day of on-site work to get the fixed system working as desired.
There's a comprehensive hands-on lab that walks you through the procedure here:
Jon JaquesInformation TechnologistAuthor Commented:
Hmmm, that's pretty cool; not a script component, but this might work better in the long run...
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Be sure to create an ADO connection for Script use, not the standard OLE DB. Simple example here:
Jon JaquesInformation TechnologistAuthor Commented:
Ahhh, there we go, getting close now, can almost taste it!
Jon JaquesInformation TechnologistAuthor Commented:
Dangit, why is every one so focused in on Sql statements??? Finding gazillions of examples of sql statement-based methods that JUST WON'T WORK because they cannot handle long text or blob fields.

I've GOT to have one that amounts to this:

ds("ClientName") = row.ClientName

I've now wasted more time on this than if I'd just looped forever using VBA! This is killing me!

I've done this a hundred times in VB.Net, but my weakness is that I've always used strongly typed datasets. :(

I just need to get this Upsert finished so that I can go back to debugging the database interface!

Jon JaquesInformation TechnologistAuthor Commented:
Okay, screw it, the answer is that SSIS is just not appropriate, and cannot handle blob data. Back to RBAR it is, I cannot waste anymore time on this.
Jon JaquesInformation TechnologistAuthor Commented:
Okay, fine, RBAR sucks, too, so I now have a NEW solution, half way inbetween both worlds... SSIS will handle all inserts, and just LOG the updates to a table that is short and sweet, and then a simple RBAR solution built in VBA will loop through and handle the updates.

As I said, those updates will only be in the 10's, so that will go quickly.

Imagine that, MS Access, in it's own way, can perform updates better than the mighty SSIS. Pffft!
OK, here is another direction. Since it is jut a few records ... write the select from destination in an Execute SQL Task. Set the Return to Recordset. Put the result into an variable with dataype of Object. Now use a For Each container and pass the Object variable in as a recordset. Put a Script Task in the For Each container to read and process the returned rows in the Object. The string datatype in the Script Task has virtually no limit to size. Read each row and perform an insert tasks.

SSIS can be a real pain, but most anything is possible.
Jon JaquesInformation TechnologistAuthor Commented:
Wow, that's pretty complex... I did already try to return to a RecordSet, but it also complained about the blobs. And, I'd have to set this up for each of my tables... I think my VBA RBAR could be written and run more quickly then I could set all that up. Check out my setup screens, below.

My Lookup Table goes like this:
CREATE TABLE [dbo].[tblKeysLookup](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [TableName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [NewValue] [int] NOT NULL,
      [OldValue] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FailureReason] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [IsSuccess] [bit] NULL,
      [DEDate] [datetime] NULL CONSTRAINT [DF_tblKeysLookup_DEDate]  DEFAULT (getdate()),
      [TableName] ASC,
      [NewValue] ASC

' My RBAR will avoid embedded sql something like this (VBA form of reflection):
For Each fld In rs.Fields
    If fld.Name = "RecordStamp" Then
        ' Do nothing
    ElseIf fld.Name = "RecNo" Then
        ' Do nothing
    ElseIf fld.Name = strKeyColumn Then
        ' This is the key column... save it's original value
        strOldValue = fld.Value
        rsLocal.Fields(fld.Name) = fld.Value
    ElseIf fld.Name = "TripClient" Then
        rsLocal.Fields(fld.Name) = objTripClients(fld.Value)
    ElseIf fld.Name = "ClientNo" Then
        rsLocal.Fields(fld.Name) = objClients(fld.Value)
        rsLocal.Fields(fld.Name) = fld.Value
    End If

Open in new window

I do like the approach to avoiding embedded SQL, is it working? BTW, I did read somewhere that SSIS will convert BLOB to DT_IMAGE and read it like text. I have not done that, but you might keep that in your pocket in case you need to try something different. I think that might only be valuable in an Expression though. Hope this works, seems sound. If you hit a specific snag, post it - we will try to help.

A Select Case might run faster than the If Thens, but probably not worth the change.
Jon JaquesInformation TechnologistAuthor Commented:
Good point about the select case, I'll do that. That is actually just a snippet from my original effort at RBAR'ing the whole thing... it does work, but I'll rewrite it almost completely tomorrow -- I can crank the lines of code in VBA without any restrictions. Once I do that, I'll post it verbatim. But yes, it works, albeit relatively slowly.

BTW, thanks for the confirmation and moral support... I'm starting to feel better already! :)
Jon JaquesInformation TechnologistAuthor Commented:
Yeah, Okay, we're off of the original topic, but now making progress on the actual problem, so might as well keep the thread going!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.