transform data based on field value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

transform data based on field value

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2011-01-27 : 09:39:30

Hi,

I have a tab delimited flat file that has records grouped by batch. Each batch 'section' has a header and then multiple records for that batch. I need to look at a field in the header section of each batch and based on the value, either load the records from that batch into a table or skip the batch entirely.

Here is an example of the file:

D493 03 6 012511CL08RILABO0LAB053
48000152246203 01241172735640 00001
48000152246203 01241172737828 00001
48000152246278 01241176029321 00001

D493 03 6 012511CL08RILABO0PSY053
48000152246203 01241172735640 00001
48000152246203 01241172737828 00001
48000152246278 01241176029321 00001

D493 03 6 012511CL08RILABO0LAB053
48000152246203 01241172735640 00001
48000152246203 01241172737828 00001
48000152246278 01241176029321 00001

So the first line will be the header of the first batch. I need to look at the 4th column, position 13-15 and if it is 'LAB' then load the records that follow until the next header section. Then check the 4th field again for 'LAB' and either load the records that follow or skip them.

So in the example above, i would need to load the first section, skip the second, and load the third.

Is there a way to do it using SSIS?

Thanks

Scott

Stamey
Starting Member

14 Posts

Posted - 2011-02-02 : 08:21:54

Yes, but you'll need to use a script in SSIS to do it.
I'd create my file connection and feed it into a script object. In the script object I would parse the lines, and if the first line of the batch had the necessary info in it, I'd pass the succeeding lines out of the script object to the next object in the package, perhaps a DB table or whatever. When the script came to the next batch header, parse it and decide whether to send the next lines out of the script.
Example:
'Line comes in:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim intBlobLen As Integer = Convert.ToInt32(Row.RawData.Length)
Dim bytBlob(intBlobLen) As Byte
bytBlob = Row.RawData.GetBlobData(0, intBlobLen)
Dim strRawData As String = System.Text.Encoding.Default.GetString(bytBlob)

Now we have the row in a variable. Now, look at the string and see if the necessary pattern is in it.
If strRawData.SubString(0,1) = "D" Then 'We identify a header record.
If strRawData.SubString(13,3) = "LAB" Then 'Necessary pattern is identified.
'Here you want to set a variable that is at the package level to tell the script that you want the data that comes in to be passed out of the script while the package-level variable is True.
End If
End If
If Package Variable = True
'Then send the line back out of the script.
End If
You still parse every line when it comes in, and set your package variable accordingly, to either disregard the line and following lines until the necessary pattern is found again.

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources