?
Solved

Need help creating a custom SSIS script component

Posted on 2008-10-13
13
Medium Priority
?
1,213 Views
Last Modified: 2013-11-10
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
Comment
Question by:CoastalData
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
13 Comments
 
LVL 4

Expert Comment

by:Maxi84
ID: 22709721
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
 
LVL 3

Author Comment

by:CoastalData
ID: 22711320
Hmmm, that's pretty cool; not a script component, but this might work better in the long run...
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22711445
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Author Comment

by:CoastalData
ID: 22711607
Ahhh, there we go, getting close now, can almost taste it!
0
 
LVL 3

Author Comment

by:CoastalData
ID: 22713363
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
 
LVL 3

Author Comment

by:CoastalData
ID: 22714805
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
 
LVL 3

Author Comment

by:CoastalData
ID: 22717585
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22717717
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
 
LVL 3

Author Comment

by:CoastalData
ID: 22717827
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22717890
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
 
LVL 3

Author Comment

by:CoastalData
ID: 22717940
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
 
LVL 3

Author Comment

by:CoastalData
ID: 22719898
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
 
LVL 3

Accepted Solution

by:
CoastalData earned 0 total points
ID: 22875887
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question