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

harry88
harry88 used Ask the Experts™
on
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)

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Reza RadConsultant, Trainer

Commented:
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
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
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.

Author

Commented:
I tried using derived column transformations...thanks for ur suggestions
I am trying to follow your original question; however, your example data and the explanation of the break out of data don't appear to be working with the same data.

However, assuming that the data has a consistent format that looks (basically) like the example data, I would use the following logic to break the data out into what you are calling the "required fields."

First, load the data (as a CSV, which will handle the comma delimited columns) into a staging table with two columns (and I would include an identity column to provide a sequencing/sorting capability);

Then, extract the desired portion of the first column, using the LEFT function and the CHARINDEX function to find the ( (of the  (v2.000) in the string);

The second column's data is a bit more complicated because you need to do the following:
Extract the characters after the first ) (i.e., from your example data, -  akmadas - AASDASMECH_AA_C:  (Total of 4 licenses issued;  Total of 3 licenses in use);
Then extract the characters before the ( in the string you extracted;
Then extract the part you really want by taking the RIGHTmost (Length(string) - 3) characters.

Now, once you have those changes made to the staging table contents, you apparently want the various bits of information in the first column broken out based upon space delimiters, which, honstly, should be rather easy (especially compared to the extraction of the second column ;-).

So, now the question is, how do you want to do this?
T-SQL
?
A CLR UDF
?
A C# app
?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial