April 2, 2008 at 11:10 am
I need to convert a column with datatype String [DT_STR] to DB TimeStamp . I tried data conversion and derived column, but nothing is working. The problem is most of the rows with orderdate is NULL, so the problem arise here when its trying to convert Null values here .
Thnaks
April 2, 2008 at 11:28 am
Put a conditional split in there. If you use the conditional split - you can set up different rules as to what should happen when that column is null and what happens when it isn't.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 2, 2008 at 12:21 pm
Dear Matt, SOrry If i sounded bir Hrash!!!
The problem is i cannot do conditional split becoaz he wants the data in the same order as it appears on flat file.
I am having problems with rows that have null values in date column. Now this is string data type of 50 length. in my destination it is a Db timestamp data type in sql table and its treating Null different way. Its trying to convert Null value into timestamp. If my column has value then i can easily convert with data conversitions.
Thanks
April 2, 2008 at 12:42 pm
Just for giggles - I just set a quick one up. If I click on the "keep nulls" in the final transform (to the SQL table), it keeps nulls in the date fields. Of course - that also entails that the table you're putting them in will ALSO take nulls in that column.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 2, 2008 at 1:18 pm
Dear Matt, Looks like u have lots of Knowledge. I tried all possibles that i can find inside the box. I believe i need to look outside of the box.
U know that in DTS , uneven rows are handle easily. Things are different in SSIS.
here is the example of my flat file----
01|2341390|03/30/2008|123yorga@comcast.net|Y|SFX2DP||0
05|98741H
01|2341458|03/30/2008|B255055@aol.com|Y|SFX3DP||0
05|LCS4800
05|HOL4900
01|2341478|03/30/2008|ir231aples@yahoo.com|N|SFX3DP||0
02|TKAHM10001
03|$40 Unlimited Text Talk and Unlimited Picture Talk
01|2341485|03/30/2008|he2hkjeiss@earthlink.net|N|SFX3DP||0
02|CDM1450M
03|$35 Local Plan
01|2341501|03/30/2008|tanasiamicjfjfaajflhinel@hotmail.com|Y|SFX3DP||0
SO next step i have a script component that handles that file based on Vertical bar in each row
Here is the sample of the script----------
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.Col1 = Tokenise(Row.Column0, "|", 1)
Row.Col2 = Tokenise(Row.Column0, "|", 2)
Row.Col3 = CStr(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
I tried Redirect error row just to see how it looks. All the rows that have nule value in Col3 which gets loaded in orderdate column in sql table are the cause of problmes. I think the problem lies in the datatype timestamp where its treating NULL something special and throws erroe that says cannot convert the data type.
PLZ feel free to advice me. Thanks in advance
April 2, 2008 at 2:01 pm
That's an entirely different issue. You're dealing with a ragged file.
Don't go custom. The data you gave me fits through a derived column calculation (as long as you deal with it correctly.) You have to use the conditional, and you can import what you need.
Try this on.
Col1
SUBSTRING([Column 0],1,FINDSTRING([Column 0],"|",1) - 1)
col2
SUBSTRING([Column 0],FINDSTRING([Column 0],"|",1) + 1,(FINDSTRING([Column 0],"|",2) == 0) ? (LEN([Column 0]) - FINDSTRING([Column 0],"|",1)) : (FINDSTRING([Column 0],"|",2) - FINDSTRING([Column 0],"|",1)))
col3
SUBSTRING([Column 0],FINDSTRING([Column 0],"|",2) + 1,FINDSTRING([Column 0],"|",3) - FINDSTRING([Column 0],"|",2) - 1)
...(5, 6, and 7 look like 3)
col7
SUBSTRING([Column 0],FINDSTRING([Column 0],"|",6) + 1,FINDSTRING([Column 0],"|",6) == 0 ? 0 : 20)
Using this - it will create non-error values, which can then dump directly in. You may have to adjust a little one way or another to get rid of the "|" - I didn'tpay 100% attention to that, but it will do what you need.
Just to be double sure - I instructed mine to "ignore errors" on those derived columns.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply