none
How to get part of file name parsed to the columnns in SSIS

    Question

  • Hello All,

    I have a variable with filename which is dynamically set in ssis , the filename is not always same which means it can be

    EX14 ABC_20130125.csv
    EX2014 ABC_20130125.csv  so I want to get the year after EX which is sometimes 14 and sometimes 2014 into one of my columns, how do I get this in SSIS ? I know I can use my filename variable to parse this but not sure how. Can anyone please help me out in this regards?         Thank You in advance.

    Thursday, March 28, 2013 9:37 PM

Answers

  • I would use a script task and use the RegEx object. 

    http://msdn.microsoft.com/en-us/library/hs600312.aspx

    System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex (@"EX(?<year>\d{2,2}|\d{4,4})\s");
     string wrd = "EX2014 DO";
     string results = string.Empty;
     if (reg.IsMatch(wrd))
         {
                results = reg.Matches(wrd)[0].Groups["year"].Value ;
          }


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, March 28, 2013 9:59 PM

All replies

  • I would use a script task and use the RegEx object. 

    http://msdn.microsoft.com/en-us/library/hs600312.aspx

    System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex (@"EX(?<year>\d{2,2}|\d{4,4})\s");
     string wrd = "EX2014 DO";
     string results = string.Empty;
     if (reg.IsMatch(wrd))
         {
                results = reg.Matches(wrd)[0].Groups["year"].Value ;
          }


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, March 28, 2013 9:59 PM
  • public void Main()
            {
                // TODO: Add your code here
                string file = Dts.Variables["User::New_file"].Value.ToString();
                // Split on one or more non-digit characters.
                string[] numbers = Regex.Split(file, @"\D+");
                foreach (string value in numbers)
                {
                    if (!string.IsNullOrEmpty(value))
                    {
                        int i = int.Parse(value);
                        //Console.WriteLine("Number: {0}", i);
                        MessageBox.Show(i.ToString());
                        return;
                    }
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }

    // use this in script task

    Thursday, March 28, 2013 10:21 PM
  • Thank you all.
    Sunday, March 31, 2013 10:20 PM