Solved

Need help creating a custom SSIS script component

Posted on 2008-10-13
13
1,200 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
  • 9
  • 3
13 Comments
 
LVL 4

Expert Comment

by:Maxi84
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Author Comment

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

Author Comment

by:CoastalData
Comment Utility
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
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 3

Author Comment

by:CoastalData
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now