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)
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)
Do more with
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.
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.
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
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.
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.
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."
The second column's data is a bit more complicated because you need to do the following:
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?
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
?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial