Link to home
Start Free TrialLog in
Avatar of harry88
harry88

asked on

SSIS package to To load a text file( with different delimiters) :

Below is the sample record of  the text file.

BrackleyDE  CCND819BXRJ  CCND819BXRJ (v2.000) (CVCV0FL21/27005 201), start Thu 4/14 11:13 (linger: 2514180) -  adskflex - 64400AMECH_PP_F:  (Total of 64 licenses issued;  Total of 30 licenses in use)

--highighed fields required

The above is the row in text file.

1st delimiter is comma(see above: bolded )
Now, we have
Col1: BdrakeEX CMUNX9BXRJ  CCN41RGJLK (v2.000) (CVCV0FL21/2700501)
Col2 -- start Thu 4/14 11:13 (linger: 2514180) -  akmadas - AASDASMECH_AA_C:  (Total of 4 licenses issued;  Total of 3 licenses in use)
- required fields bolded.
I have the above record in a flat file and i need to parse the required fields into a table.I have to further split column using space delimited for COL1 --as shown above, I only want few fields from COL1. which I bolded above.
The same with the COL2. here it is dash delimited and colon Delimited
I know this can be done by adding a SCRIPT TASK in DATAFLOW of  SSIS package.

Can any one give me        C# code or .vb code to parse this into different columns of script task…which I will eventually put into a sql destination tables.
 OR else can anyone give me the script (using string functions in SSIS derived column tsak)
Col1:  BdrakeEX  CMUNX9BXRJ  CCN41RGJLK (v2.000) (CVCV0FL21/2700501)

To extract above three bolded text fields individually      

Col2 -- start Thu 4/14 11:13 (linger: 2514180) -  akmadas - AASDASMECH_AA_C:  (Total of 4 licenses issued;  Total of 3 licenses in use)

To extract above two bolded text fields individually      
OR please suggest transformations in tsql to do this.(If I stage this data as a single record in to a table on sql server)

Avatar of anillucky31
anillucky31
Flag of India image

See this project on codeproject.

http://www.codeproject.com/KB/database/Cs_CSV_import_export.aspx

You can modify code from this project and parse your file into comma delimited value and pass them to dataset then to your database.

If you need further help on this let us know.
Avatar of Reza Rad
use Script Component as Source and fetch data as you want from flat file with any delimiter type you need.
this is a sample of script component as source:
http://www.rad.pasfu.com/index.php?/archives/38-Script-Component-as-Source-SSIS.html
Hi,

Use DataFlow task and in that use FlatFile Data source.

First create file connection and put the parameter i.e col delimiter is {TAB} and row delimiter is {CR}{LF}.
Set columns in file source as you desire.
Avatar of harry88
harry88

ASKER

I tried using derived column transformations...thanks for ur suggestions
ASKER CERTIFIED SOLUTION
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial