December 7, 2006 at 2:32 pm
Hello,
I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.
In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.
What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.
Thank you for your help!
cdun2
December 8, 2006 at 8:19 am
Hello,
Sounds like what you need is to create an asynchronous transformation.
http://msdn2.microsoft.com/en-us/library/ms136133.aspx
Donald Farmer's Rational Guide to SSIS is also a good reference.
Hope this helps.
December 8, 2006 at 8:47 am
Environment tested on: Windows XP Pro and SQL Server 2000
Step 1
======
Create the following text file c:\whatever\SourceTextFile.txt
Da123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890----------1234567890123456
78901234567890123456789012345678901234567890123456789012345678901234567890----------1234567890123456789012345678901234
56789012345678901234567890123456789012345678901234567890----------1234567890123456789012345678901234567890123456789012
345678901234567890********
Db123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890----------1234567890123456
78901234567890123456789012345678901234567890123456789012345678901234567890-D1-------1234567890123456789012345678901234
56789012345678901234567890123456789012345678901234567890----------1234567890123456789012345678901234567890123456789012
345678901234567890********
Dc123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890----------1234567890123456
78901234567890123456789012345678901234567890123456789012345678901234567890----------1234567890123456789012345678901234
56789012345678901234567890123456789012345678901234567890----------1234567890123456789012345678901234567890123456789012
345678901234567890********
Dd123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890----------1234567890123456
78901234567890123456789012345678901234567890123456789012345678901234567890-D1-------1234567890123456789012345678901234
56789012345678901234567890123456789012345678901234567890----------1234567890123456789012345678901234567890123456789012
345678901234567890********
De123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890----------1234567890123456
78901234567890123456789012345678901234567890123456789012345678901234567890----------1234567890123456789012345678901234
56789012345678901234567890123456789012345678901234567890----------1234567890123456789012345678901234567890123456789012
345678901234567890********
Note there are 5 records of 380 chars length
record 2 and 4 have the 'D1' at char 194-195
The output table should contain 7 records, split at the appropriate place.
Step 2
======
Drop a "Text File (Source)" connection onto the DTS designer and
for FileName specify this file name C:\whatever\SourceTextFile.txt
Click on 'Properties'
Select the 'Fixed field' radio button
FileType: ANSI
Row deliter: {LF}
Click On Next, Click on Finish
Step 3
======
Drop a "Microsoft OLE DB Provider for SQL Server" connection and select your database
Step 3
======
Select a "Transform Data Task" and connect the TextFile Source and the DB Connection
Double the Task (arrow)
On the Destination tab, click on 'Create' and then on OK
On the Transformations tab, delete all existing Transformations
Click on 'New'
Select ActiveX script and click on Ok
Select All Source and Destination columns
Click on Properties and enter the following ActiveX script.
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
temp = mid(DTSSource("Col001"),194,2)
if temp = "D1" then
if DTSGlobalVariables("firstIteration").Value = true then
DTSGlobalVariables("firstIteration").Value = false
temp = left(DTSSource("Col001"),193)
DTSDestination("Col001") = temp
Main = DTSTransformStat_SkipFetch
else
DTSGlobalVariables("firstIteration").Value = true
temp = mid(DTSSource("Col001"),194)
DTSDestination("Col001") = temp
Main = DTSTransformStat_OK
end if
else
DTSDestination("Col001") = DTSSource("Col001")
Main = DTSTransformStat_OK
end if
End Function
Click on OK trhree times.
Step 4
======
That's it
Now Execute the package and you should see the desired transformation in your table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply