Solved

SSIS: Flat File transformation question

Posted on 2008-06-24
1
547 Views
Last Modified: 2008-07-07
Hello Experts,

I'm relatively new to SSIS so please be gentle...however I'm quite familiar with t-SQL and DTS.

We recently (and finally) upgraded our SQL Server to 2k5 and I'm trying to update old stored procedures and DTS packages to take advantage of the power of SSIS. However that power often confuses me:

take a flat file example that needs to be split off into two different db (results and summary) tables.

File::TEST00.JT5


PART#     79-5350-0063
LOT#      TEST00
OPER#     TEST
TIME      23:06:08
DATE      17 Jun 2008
LAST TIME 12:00
LAST DATE 16 Jun 2008
AQL MINCAP 3069.88
AQL MAXCAP 6139.77
SIZE       0
============================================================
 1, .123684, 43.3553,FAILED - NOT PRESENT
 2, .0843582, 1.17911,FAILED - NOT PRESENT
 3, .132345, 13.7484,FAILED - NOT PRESENT
 4, .159249, 21.4962,FAILED - NOT PRESENT



perviously I had a dts package that would on a regular interval run a sp and the use fso to move processed files to archive directories. It is my assumption that I can streamline this process by using SSIS. So the question is: what exactly do I need to do to successfully process a file like this?

I assume I need  to Multicast the file, but how do I split the top 10 lines off to be processed for summary data and the rest for results (and then apply summary statistics on the results and add it in with the summary data)

further, how do I say:

(SELECT LTRIM(RTRIM(SUBSTRING(Data, 6, 1000))) FROM #Temp1 WHERE LEFT(Data, 5) = 'PART#') AS PartNo

or more distinctly, what transformation object should I be using to do stuff like this? below id the original SP I used to process the files previously (and as you can see, it is less than ideal...but at least it shows kind of what I'm after)


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

---------------------------------------------------------------------------------------------------------

CREATE        proc [dbo].[spProcessLotFile]( @file nvarchar(1000) ) as
set nocount on

---------------------------------------------------------------------------------------------------------

DECLARE @lot nvarchar(50), @mach nvarchar(1), @SQL nvarchar(1000)

IF object_id('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
CREATE TABLE #Temp (Data varchar(1000))
SET @SQL = 'BULK INSERT #Temp FROM '''+ @file +''''
EXECUTE sp_executesql @SQL
ALTER TABLE #Temp ADD keyID int IDENTITY(1,1)
IF object_id('tempdb..#Temp1') IS NOT NULL DROP TABLE #Temp1
SELECT * INTO #Temp1 FROM #Temp
DROP TABLE #Temp


SET @lot = (SELECT LTRIM(RTRIM(SUBSTRING(Data, 5, 1000))) FROM #Temp1 WHERE LEFT(Data, 4) = 'LOT#')
SET @mach = (RIGHT(RTRIM(@file), 1))
IF @mach = 'N' SET @mach = 0


IF (@lot) IS NOT NULL AND (SELECT Count(*) FROM #Temp1 WHERE keyID > 11) > 0
BEGIN  
--------------------

DELETE FROM [tblLotResults] WHERE lot = @lot AND MachineNo = @mach
INSERT [tblLotResults] (lot, MachineNo, Test, Cap, DF, Result)
SELECT
    @lot AS lot
  , @mach AS MachineNo
  , LTRIM(RTRIM(dbo.fnSplitText(Data, 1))) AS Test
  , CONVERT(float, LTRIM(RTRIM(dbo.fnSplitText(Data, 2)))) AS Cap
  , CONVERT(float, LTRIM(RTRIM(dbo.fnSplitText(Data, 3)))) AS DF
  , LTRIM(RTRIM(dbo.fnSplitText(Data, 4))) AS Result
FROM #Temp1 WHERE keyID > 11
    AND ISNULL(dbo.fnSplitText(Data, 4) , '') <> ''
    AND ISNULL(dbo.fnSplitText(Data, 3) , '') <> ''
    AND ISNULL(dbo.fnSplitText(Data, 2) , '') <> ''
    AND ISNULL(dbo.fnSplitText(Data, 1) , '') <> ''



DELETE FROM [tblLotSummary] WHERE LotNo = @lot AND MachineNo = @mach
INSERT [tblLotSummary] (PartNo, LotNo, MachineNo, OperNo, [Time], [Date], LastTime, LastDate, AQLMin, AQLMax, [Size],
                  pcsTested, pcsGood, pcsFailLow, pcsFailHi, pcsFailDF)
SELECT
    (SELECT LTRIM(RTRIM(SUBSTRING(Data, 6, 1000))) FROM #Temp1 WHERE LEFT(Data, 5) = 'PART#') AS PartNo
  , (@lot) AS LotNo
  , (@mach) AS MachineNo
  , (SELECT LTRIM(RTRIM(SUBSTRING(Data, 6, 1000))) FROM #Temp1 WHERE LEFT(Data, 5) = 'OPER#') AS OperNo
  , (SELECT LTRIM(RTRIM(SUBSTRING(Data, 5, 1000))) FROM #Temp1 WHERE LEFT(Data, 4) = 'TIME') AS [Time]
  , (SELECT LTRIM(RTRIM(SUBSTRING(Data, 5, 1000))) FROM #Temp1 WHERE LEFT(Data, 4) = 'DATE') AS [Date]
  , (SELECT LTRIM(RTRIM(SUBSTRING(Data, 10, 1000))) FROM #Temp1 WHERE LEFT(Data, 9) = 'LAST TIME') AS LastTime
  , (SELECT LTRIM(RTRIM(SUBSTRING(Data, 10, 1000))) FROM #Temp1 WHERE LEFT(Data, 9) = 'LAST DATE') AS LastDate
  , (SELECT LTRIM(RTRIM(SUBSTRING(Data, 11, 1000))) FROM #Temp1 WHERE LEFT(Data, 10) = 'AQL MINCAP') AS AQLMin
  , (SELECT LTRIM(RTRIM(SUBSTRING(Data, 11, 1000))) FROM #Temp1 WHERE LEFT(Data, 10) = 'AQL MAXCAP') AS AQLMax
  , (SELECT LTRIM(RTRIM(SUBSTRING(Data, 5, 1000))) FROM #Temp1 WHERE LEFT(Data, 4) = 'SIZE') AS [Size]
  , (SELECT Count(*) FROM tblLotResults WHERE lot = @lot AND Result <> 'FAILED - NOT PRESENT') AS pcsTested
  , (SELECT Count(*) FROM tblLotResults WHERE lot = @lot AND Result = 'PASSED') AS pcsGood
  , (SELECT Count(*) FROM tblLotResults WHERE lot = @lot AND Result = 'FAILED - LOW') AS pcsFailLow
  , (SELECT Count(*) FROM tblLotResults WHERE lot = @lot AND Result = 'FAILED - HI') AS pcsFailHi
  , (SELECT Count(*) FROM tblLotResults WHERE lot = @lot AND Result = 'FAILED - DF') AS pcsFailDF

--------------------
END

DROP TABLE #Temp1

---------------------------------------------------------------------------------------------------------
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO



any help would be most appreciated!

0
Comment
Question by:longb
1 Comment
 

Accepted Solution

by:
longb earned 0 total points
ID: 21944587
reposted as a new question since this one never got any feedback
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

792 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