Solved

SSIS: Flat File transformation question

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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