Solved

SSIS: Flat File transformation question

Posted on 2008-06-24
1
550 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
[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
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

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