April 8, 2008 at 12:50 pm
As we know things are different in DTS and SSIS, specially when it comes to flat file connections.
Here is the example of flat file that we might encounter one day....
------------------------------------------------
01|2340611|03/28/2008|nbgmnelly_myvbners@yahoo.com|N|SFX3DP||0
02|SCH-R410ZKAMTR
03|$50 Long Distance Plan
01|2342615|03/31/2008|tifffaanywngamb102etriston@yahoo.com|Y|SFX3DP|V_71gyRdbd8-ns1VIuR0Y5.vQWs2O0XPXQ|10300
02|SCH-R210LSAMTR
03|$45 Unlimited MetroWEB and Unlimited Text Talk Global
01|2342720|04/01/2008|NowDeathwilnlcome243@aol.com|Y|SFX2DP||0
02|TKAGK10030
03|$45 Long Distance Plan
01|2342727|04/01/2008|srtybndonbfmaasi@domasi.com|N|SFX3DP||0
02|CDM1450M
03|$40 Unlimited Text Talk and Unlimited Picture Talk
01|2342732|04/01/2008|Iannb.Taackfgett@hotmail.com|N|SFX01P||0
02|CDM1450M
03|$50 Long Distance Plan
04|MetroGuard Handset Insurance
01|2342756|04/01/2008|NOSUGARFLOUR@COMCAST.NET|Y|SFX2DP||0
02|SCH-R300ZRAMTR
03|$40 Unlimited Text Talk and Unlimited Picture Talk
01|2342770|04/01/2008|CRZCVBNOMadAN@ABARGAINAUTO.COM|Y|SFX3DP||0
------------------------------------------------
This file has valid column delimeter "|" and CR-LF row delimeter. But the problem is uneven column..
Now , What i have done is In my flat file conn, i have no column delimeter, so everything comes in one column as single input from each row.
next step I have this script compomnent in my data flow to handle this uneven column...here's teh code--
----------------------------------------------------
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.Col1 = Tokenise(Row.Column0, "|", 1)
Row.Col2 = Tokenise(Row.Column0, "|", 2)
Row.Col3 = Tokenise(Row.Column0, "|", 3)
Row.Col4 = Tokenise(Row.Column0, "|", 4)
Row.Col5 = Tokenise(Row.Column0, "|", 5)
Row.Col6 = Tokenise(Row.Column0, "|", 6)
Row.Col7 = Tokenise(Row.Column0, "|", 7)
Row.Col8 = Tokenise(Row.Column0, "|", 8)
End Sub
'Private function that parses out the columns of the whole row
Private Function Tokenise(ByVal input As String, ByVal delimiter As String, ByVal token As Integer) As String
Dim tokenArray As String()
tokenArray = input.Split(delimiter.ToCharArray) 'Split the string by the delimiter
If tokenArray.Length < token Then 'Protect against a request for a token that doesn't exist
Return ""
Else
Return tokenArray(token - 1)
End If
End Function
End Class
what does this do is based on no of "|" , it will divide each row into columns....
SO in my script trnasformation editor in inputs and output tab i have
column 0 as input and col 1 to col 8 as output paramters...
Now, all the output colmns are dt string datatype of varying lenght...
(*****th eproblem giving col is col3 which is dt string of lenght 20) i will tell u why this problme below.
Next, I have SQL destination...
here is the script of the table for easy workout for you all
--------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tBPWebOrders_Daily](
[Idx] [int] IDENTITY(1,1) NOT NULL,
[LineID] [char](2) NOT NULL,
[TransactionID] [varchar](100) NULL,
[OrderDate] [smalldatetime] NULL,
[EmailAddress] [varchar](50) NULL,
[EmailOptinFlag] [char](1) NULL,
[ShippingSCACCode] [char](6) NULL,
[LinkShareAffilID] [varchar](50) NULL,
[TrackingID] [varchar](12) NULL
) ON [PRIMARY]
Look at the column "Orderdate" which is smalldatetime.
when i am trying to import , it gives me error saying data type cannot be converted becoaz of potential loss of data for rows that have no orderdate from flat file.
When i throw data conversation task inbweteen this two, gives me error saying data cannot be converted...
( i gave up ....) my manager is write something in perl or other language to add "|" bar in flat file before i can import it..
( OTHER BIG PROBLEM: DATA MUST BE INSERTED INTO TABLE IN THE SAME ORDER THEY ARE IN THE FLAT FILE)
Other things i tried was Derived column, conditional split, error output rows.
If anyone of you need more info or things , let me know
JUST TRY THIS FOR URSELF..
Edit: Go ahead and change the table orderdate datatype from smalldatetime to char or varchar or string..It works.
My analysis prevails that all the rows in the falt file that don't have any orderdate data for SQL table is causing the problem. And when u insert these datatype into table they are not empty ( i tried , but when u do select from table where orderdate is not null, give me all rows, rows that looks empty, but have some empty string or something in it.
Thnaks, Kumar
**** A PROBLME AIN't A "problme" , If it can be SOLved."
April 23, 2008 at 4:03 am
You could use the Data Conversion task. Have you considered that?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
April 23, 2008 at 7:29 am
Yes Throws me error, becoz those empty strings gets truncated and sql server thinks some data are being lost. tried every possible cobinations that I could think of
April 23, 2008 at 7:59 am
I see. I think you should verify that the data can be converted in the Script Task, and possibly do the conversion there.
If the source contains rows with variable data types and there's a pattern to how individual row variants are placed in the source then you could also return the data in more than one output result set, which could help you later in the process with redirecting different data sets to different destinations.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply