September 8, 2011 at 3:11 pm
Hi All,
I am newbie to SSIS and got one requirement. I have a column with the name of email and it looks like the following
Now I need an putput like
Email1 Email 2
I have many delimiters with in these emails. I need C# code or .Net code to get rid of these delimiters. Can any body suggest me some links or code.
Thanks in advance,
Tarak.
September 8, 2011 at 5:58 pm
Read this article by Jeff Moden and adapt the code to your delimiter
September 8, 2011 at 7:13 pm
Hi Ron,
Thanks for quick reply.
September 9, 2011 at 6:54 am
Alternatively, if you're sure there will only be two emails, you could always use the Script Component of the Data Flow Task. Have it be a Transform type Script Component, and create two output columns, then do a string.Split() function against the email input, feeding the two parts to the two output columns.
September 9, 2011 at 10:35 am
I written like this based on online links. I don't know how to parse a Column values to a variable. Any help would appreciated.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
private int i = 0;
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void CreateNewOutputRows(Output0Buffer Row)
{
string sp;
string[] columns;
string delimstr = ",:;',/|";
char[] delimiter = delimstr.ToCharArray();
sp = Row.EmailAddr;
while (sp!= null)
{
columns = sp.Split(delimiter);
if (i > 0 && sp.Length >= 0)
{
Row.Primary_EmailAddr = columns[0];
Row.Secondary_EmailAddr = columns[1];
}
}
i++;
}
}
September 9, 2011 at 11:55 am
Make sure you specify that your Script Component is of type Transform when given the choice.
In the Input Columns tab, choose the email from your Flat File Source
In the Inputs and Outputs tab, select the default Output 0 output, and make sure that the SynchronousInputID value is the same as the ID of the Input. Then, click on the Output Columns folder within the Output 0 output, then choose Add Column. Add two columns, give them whatever names you want.
Next, go in to your script, and Design Script. Paste the code you have there. Replace "Row.EmailAddr" with whatever you called the input, and "Row.Primary_EmailAddr" and "Row.Secondary_EmailAddr" with whatever you called the outputs. Then you're done!
September 9, 2011 at 12:30 pm
If you have a set number of emails, wouldn't it be easier to do this in a Derived Column Transformation?
I suspect that the Derived Column Transformation may also be more efficient, but have not tested it to be sure.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 9, 2011 at 12:52 pm
I was thinking Derived Column Transformation, but there's no built-in split function, so you'd have to have one very, very long script to go through all the different components. Something using SUBSTRING and FINDSTRING and a whole bunch of ? and : statements.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy