• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1216
  • Last Modified:

Need help creating a custom SSIS script component

Hello,

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!

--J
0
CoastalData
Asked:
CoastalData
  • 9
  • 3
1 Solution
 
Maxi84Commented:
There's a comprehensive hands-on lab that walks you through the procedure here:

http://www.microsoft.com/downloads/details.aspx?familyid=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&displaylang=en
0
 
CoastalDataAuthor Commented:
Hmmm, that's pretty cool; not a script component, but this might work better in the long run...
0
 
HoggZillaCommented:
Be sure to create an ADO connection for Script use, not the standard OLE DB. Simple example here:

http://msdn.microsoft.com/en-us/library/ms135939(SQL.90).aspx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CoastalDataAuthor Commented:
Ahhh, there we go, getting close now, can almost taste it!
0
 
CoastalDataAuthor 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!

Help!
0
 
CoastalDataAuthor 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.
0
 
CoastalDataAuthor 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!
0
 
HoggZillaCommented:
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.
0
 
CoastalDataAuthor 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()),
 CONSTRAINT [PK_tblKeysLookup] PRIMARY KEY CLUSTERED
(
      [TableName] ASC,
      [NewValue] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

' 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)
    Else
        rsLocal.Fields(fld.Name) = fld.Value
    End If
Next

Open in new window

Control-Flow.gif
Upsert-Clients.gif
0
 
HoggZillaCommented:
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.
0
 
CoastalDataAuthor 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! :)
0
 
CoastalDataAuthor 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!
0
 
CoastalDataAuthor 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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now