November 29, 2006 at 8:30 am
Hello,
I am making my first attempt at creating a script for a Script Task. The script needs to do the following;
1. find the length of each record in a single fixed width flat file
-file location; C:\Learning\SettlementDataTest\SC15_Copies\SingleFile
-file name; CDNSC.CDNSC.SC0015.111062006 (no file extension)
2. if a record is found that is longer than 384 characters;
a. copy the record out to a text file
-location;C:\Learning\SettlementDataTest\SC15_Copies\ErrantRecords
-file name; ErrantRecords.txt
b. delete record from the flat file where the record length is > 384.
If I can get this to work on a single file, I want to implement it with multiple files. I would imagine that using a ForEachLoop container with the script task 'inside' would be the way to go for multiple files. I have a connection manager set up for the single file and a MultiFlatFile connection manager set up for the whole collection of files. All of the files have the same schema. I don't know if the connection managers are going to be useful to me with what I'm trying to do, but I have them set up.
If you have some input on where I can find resources on how to do this, or have some code to pass along, please share.
Thank you for your help!
CSDunn
December 4, 2006 at 8:00 am
This was removed by the editor as SPAM
January 21, 2007 at 10:00 pm
You can use a Script Task with the following code. When you set up the task you need to define two outputs and columns, then set the ID field in the Common Properties to 0 for each output.
Imports
System
Imports
System.Data
Imports
System.Math
Imports
Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports
Microsoft.SqlServer.Dts.Runtime.Wrapper
Public
Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Len(Row.Column0) > 6 Then
Output0Buffer.AddRow()
Output0Buffer.ColumnOut1 = Row.Column0
Output0Buffer.Len1 = Len(Row.Column0)
Else
Output1Buffer.AddRow()
Output1Buffer.ColumnOut2 = Row.Column0
Output1Buffer.len2 = Len(Row.Column0)
End If
End Sub
End
Class
[font="Comic Sans MS"]Ira Warren Whiteside[/font]
January 22, 2007 at 11:53 am
Thanks for your input. I was actually able to do this with a Conditional Split Transformation.
cdun2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply