September 22, 2011 at 10:43 am
I have to import a bunch of spreadsheets (example below) that all have the same format into a SQL 2005 table. Some records have only one SEQ_NUM while others may have as many as twelve; it might be possible to end up with more than that.
Record SeqNum Text RanData1 RanData2 RanData3
10548 1 This is a 16548 LR37A 19534.54
10548 2 screwy way NULL NULL NULL
10548 3 to mainta NULL NULL NULL
10548 4 in a recor NULL NULL NULL
10548 5 d. NULL NULL NULL
I need the final table to look like this:
Record NewText RanData1 RanData2 RanData3
10548 This is a screwy way to maintain a record. 16548 LR37A 19534.54
I've figured out how to do this manually with a temp table and a cursor, but now I want to automate the process in SSIS and the function won't work the way I'm trying to use it. Anyone out there have an idea how to accomplish this in a single Data Flow Task without cursors or temp tables?
September 22, 2011 at 10:54 am
You'll be able to do it with an asynchronous Script Component - will take a bit of coding but if you're familiar with VB or C# it's nothing too onerous.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 22, 2011 at 11:01 am
Another possible approach would be to do this in two steps. First, import all your records into the table exactly as you have them in the files - so you'll have the messed up text / etc.
Then, once they are all there, you can run a stored proc against that table and output the records into a new table, in the right way.
The best way to do that would be using the "Quirky" Update approach that Jeff Moden wrote about - see the article here:
An example, with your code setup, can be seen below:
CREATE TABLE #Temp
(
Record INT,
SeqNum INT,
[Text] VARCHAR(20),
[FullText] VARCHAR(MAX) DEFAULT '',
RanData1 VARCHAR(20),
RanData2 VARCHAR(20),
RanData3 VARCHAR(20)
)
INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)
VALUES(10548, 1, 'This is a ', '16548', 'LR37A', '19534.54')
INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)
VALUES(10548, 2, 'screwy way', NULL, NULL, NULL)
INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)
VALUES(10548, 3, ' to mainta', NULL, NULL, NULL)
INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)
VALUES(10548, 4, 'in a recor', NULL, NULL, NULL)
INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)
VALUES(10548, 5, 'd.', NULL, NULL, NULL)
INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)
VALUES(10549, 1, 'A New Test ', '11248', 'sdafas', 's23525')
INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)
VALUES(10549, 2, 'Line As AN', NULL, NULL, NULL)
INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)
VALUES(10549, 3, ' Example.', NULL, NULL, NULL)
DECLARE @RunningTotalString VARCHAR(MAX)
SET @RunningTotalString = ''
DECLARE @Record INT
SET @Record = 0
UPDATE #Temp
SET @RunningTotalString = [FullText] = (CASE WHEN Record = @Record THEN @RunningTotalString + [Text] ELSE [Text] END),
@Record = Record
FROM #Temp WITH (TABLOCKX)
OPTION (MAXDOP 1)
SELECT Record, MAX([FullText]), MAX(RanData1), MAX(RanData2), MAX(RanData3) FROM #Temp
GROUP BY Record
DROP TABLE #Temp
So, all in all, to do this in SSIS, you'd have a FOREACH loop that would loop through all your spreadsheets, and for each spreadsheet, write the data into a table.
Then, you'd have an Execute SQL Command, in which you'd run a stored proc with code similar to the above, and at the last step, you would do an INSERT INTO into your final table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply