March 19, 2010 at 9:48 am
I have a rather odd question . I'm new to SSIS.
A txt file that is like the one below
ENAME, DIET; BRAND NAME
ENUMBER, 223 ; BRAND CODE
ELOT, 65 ; LOT
EPACK, 6 ; CANS PER PACK
ECARTON, 4 ; CARTON
ERCASE, 23 ; CASE
A TABLE like this one : Column names
[brand_name]
,[brand_code]
,[lot]
,[cans_per_pack]
,[carton]
,[case]
I've tried the using DTSWizard.exe and formatting different ways but no luck. I want the middle values in the string to insert into the table, for instance the value DIET in this string " ENAME, DIET; BRAND NAME ".
Thanks for any help offered.
March 19, 2010 at 11:21 am
I think you will need to load each line into a single column in a staging area, then split or parse out what you need from each column.
Normally, ETL type programs like a header row listing each column, followed by the data 1 record per row in the same order as the header and using a consistant separator (like '|' or a comma).
March 19, 2010 at 11:27 am
Thank you I appreciate your advice. I've looked up a VBS function and will try and use this with SSIS .
Dim strAryWrd
Dim strValue
Dim strValue1
strValue = "EBRND, DIET ; BRAND"
strAryWrd = Split(strValue, ",")
Dim i
For i= 0 to Ubound(strAryWrd)
msgbox strAryWrd(i)
strValue1 = strAryWrd(i)
Next
Dim strAryWrd1
strAryWrd1 = Split(strValue1, ";")
Dim x
For x= 0 to Ubound(strAryWrd1)
If x = 0 THEN
msgbox strAryWrd1(x)
ELSE EXIT FOR
END IF
Next
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply