June 9, 2011 at 5:26 pm
Alright, head to wall impact is getting on my nerves.
I've got a DTS package that I'm upgrading to SSIS. It worked perfectly happy in DTS.
Here's a sample of the datafile that it was pulling from:
33.03|SUBS|xxxxxxxxxxxxxxxxxx|mfus|wsta.x|aaaaaaaaaaaaaa| 22,046.336| 763,094.85| 728,190.48| -34,904.37
1.00|SUBS|xxxxxxxxxxxxxxxxxx|oaus|particip|bbbbbbbbbbbbbbbbbbbb| 30,936,257.16| 30,936,257.16
0.09|SUBS|xxxxxxxxxxxxxxxxxx|psus|52522l459|ccccccccccccccccccccccccc|50,000.000| 500,000.00| 4,500.00| -495,500.00
Proprietary information masked.
Now, of note, you'll see line two there has two less pipe delimited columns then its local friends.
In DTS, these final columns were simply NULLS, it detected end of row correctly, and moved on with the next row.
In SSIS, It's not detecting the {CR}{LF} properly, and its chopping to bring the row below it up into the missing two positions, then leaves the rest of the next row (the other 9 columns) on the final column of the one with the missing positions.
The report itself is off a mainframe equivalent app, and is painful at best to work with, nevermind modify.
What in heck's name do I need to do to get this to behave itself?
I've attached a few images of the settings in case they can help. It almost appears that SSIS and DTS reversed when the detect the row.
EDIT: I deleted the attachments as they weren't helping and one of them I hadn't fully masked the proprietary information.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 10, 2011 at 2:30 am
You sure the EOL is CRLF and not just CR or LF?
Try either option on its own.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 10, 2011 at 2:38 am
I'm interested to know how you avoided getting a meta-data error as a result of the expected columns being missing on the dodgy row?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 10, 2011 at 11:14 am
Craig Farrell (6/9/2011)
In DTS, these final columns were simply NULLS, it detected end of row correctly, and moved on with the next row.In SSIS, It's not detecting the {CR}{LF} properly, and its chopping to bring the row below it up into the missing two positions, then leaves the rest of the next row (the other 9 columns) on the final column of the one with the missing positions.
Craig, I still have the scar from hitting my head against my desk when I had this exact issue 2 years ago...it's a backhanded nicety that DTS offered via poor data management principles (IMHO) that was removed from SSIS.
Unfortunately the way to handle it is to pre-process the file to add the missing delimiters. Script Task or something before it hits SSIS.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 11:16 am
Phil Parkin (6/10/2011)
I'm interested to know how you avoided getting a meta-data error as a result of the expected columns being missing on the dodgy row?
Guessing a little but it could be because SSIS only scans the first n rows when building up the dialog for us to setup the flat file metadata.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 11:44 am
opc.three (6/10/2011)
Phil Parkin (6/10/2011)
I'm interested to know how you avoided getting a meta-data error as a result of the expected columns being missing on the dodgy row?Guessing a little but it could be because SSIS only scans the first n rows when building up the dialog for us to setup the flat file metadata.
Because it's not doing row detection first, so the next row fills in the 'missing spot', with the CRLF considered as just part of the string field.
It's not just the scan, I can see the exact issue in the preview of the data in the file.
I've ended up turning those last 3 columns into a single column as far as metadata is concerned and shoving the entire process through a synchronous script component.
It's ugly, but it works.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 10, 2011 at 11:46 am
Crispin Proctor (6/10/2011)
You sure the EOL is CRLF and not just CR or LF?Try either option on its own.
Yeah, because it works fine for the full rows, so I know it's got a CR/LF at end of row.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 10, 2011 at 11:50 am
Craig Farrell (6/10/2011)
I've ended up turning those last 3 columns into a single column as far as metadata is concerned and shoving the entire process through a synchronous script component.It's ugly, but it works.
Yuck. A necessary and warranted approach, but still yuck!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 12:05 pm
opc.three (6/10/2011)
Craig Farrell (6/10/2011)
I've ended up turning those last 3 columns into a single column as far as metadata is concerned and shoving the entire process through a synchronous script component.It's ugly, but it works.
Yuck. A necessary and warranted approach, but still yuck!
Yeah, it had been a while since I used an asynchronous method as synchronous. Spent half my afternoon researching the code and settings again. Forgot you had to override two of the subs... which of course I really only know VB.NET and the sites I found with the best information were C#, so translating was fun, too...
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 10, 2011 at 12:16 pm
Oh, for anyone interested, the code below. MainStreamBuffer is an output buffer that is NOT synchronized to Input 0 in properties. Because of this, ProcessInput must be overridden, or you just sit there with SSIS on the first stream batch staring at each other. This code runs pretty quick and barely slows down the stream.
The "?" means this system coughed up a NULL in that field. You'd think they could just do that for the missing columns, right?
Can this code be cleaned up? I'm quite sure it can, I'm a SQL dude, not a .NET dev. But for the rest of y'all like me who'd like an example...
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_ProcessInput(ByVal Buffer As Input0Buffer)
While (Buffer.NextRow())
Input0_ProcessInputRow(Buffer)
End While
If Buffer.EndOfRowset Then
MainStreamBuffer.SetEndOfRowset()
End If
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim FirstPipe As Integer
Dim SecondPipe As Integer
Dim ModPrice As String
Dim ModQuantity As String
Dim ModMultiColumn As String
Dim ModCost As String
Dim ModMarket As String
Dim ModUnrealizedGL As String
ModPrice = Row.Price.Replace(",", "").Trim
ModQuantity = Row.Quantity.Replace(",", "").Trim
ModMultiColumn = Row.MultiColumn.Replace(",", "").Replace(" ", "")
With MainStreamBuffer
.AddRow()
If InStr(ModPrice, "?") > 0 Then
.Price_IsNull = True
Else
.Price = CDec(ModPrice)
End If
.Portfolio = Row.Portfolio
.Name = Row.Name
.Symbol = Row.Symbol
.SecurityDescription = Row.SecurityDescription
.Type = Row.Type
If InStr(ModQuantity, "?") > 0 Then
.Quantity_IsNull = True
Else
.Quantity = CDec(ModQuantity)
End If
If InStr(Row.MultiColumn, "|", CompareMethod.Text) = 0 Then
If Left(ModMultiColumn, 1) = "?" Then
.Cost_IsNull = True
Else
.Cost = CDec(ModMultiColumn)
End If
.Market_IsNull = True
.UnrealizedGL_IsNull = True
Else
FirstPipe = InStr(ModMultiColumn, "|")
SecondPipe = InStr(FirstPipe + 1, ModMultiColumn, "|", CompareMethod.Text)
ModCost = Left(ModMultiColumn, FirstPipe - 1)
ModMarket = ModMultiColumn.Substring(FirstPipe, SecondPipe - FirstPipe - 1)
ModUnrealizedGL = ModMultiColumn.Substring(SecondPipe, ModMultiColumn.Length - SecondPipe - 1)
If ModCost = "?" Or ModCost.Length = 0 Then
.Cost_IsNull = True
Else
.Cost = CDec(ModCost)
End If
If ModMarket = "?" Or ModMarket.Length = 0 Then
.Market_IsNull = True
Else
.Market = CDec(ModMarket)
End If
If ModUnrealizedGL = "?" Or ModUnrealizedGL.Length = 0 Then
.UnrealizedGL_IsNull = True
Else
.UnrealizedGL = CDec(ModUnrealizedGL)
End If
End If
End With
End Sub
End Class
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply