Solved

SSIS: Flat File transformation question

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

939 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now