Solved

SSIS: Flat File transformation question

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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