none
How do i extract date from file name and save it in my destination table using derived column transformation in ssis RRS feed

  • Question

  • i have an excel file that i do extract the date from the file name and save it to my table but the file name keeps changing most times. example of the file name is below: 3445_WorkForceReport 11272017-{1}.xls   and sometimes to the file will come in as this: 3445_WorkForceReport 11272017.xls

    below is the expression i used in the derived column transformation but since the file name keeps changing is making my package error out.

    this is what i used for this file name 3445_WorkForceReport 11272017.xls

    RIGHT(LEFT(RIGHT(@[User::Fullpath],12),8),4) + "-" + LEFT(RIGHT(@[User::Fullpath],12),2) + "-" + RIGHT(LEFT(RIGHT(@[User::Fullpath],12),4),2)

    Now sometimes the filename will come as this: 3445_WorkForceReport 11272017-{1}.xls

    Please how can i solve this.

    Wednesday, November 29, 2017 5:42 PM

Answers

  • If the "3445_WorkForceReport" is always consistent, get the filename portion and then substring(<filename>, 22, 8) - may need some adjustment to start position, I think it's 22 but can never remember if it's 0 based or not.
    • Marked as answer by pizaro87 Thursday, December 7, 2017 8:22 PM
    Wednesday, November 29, 2017 6:51 PM
  • Hi pizaro87,

    If possible, personally, the most appropriate method is to use Regular expression to extract the date. Please refer to following script snippet:

    public void Main()
    		{
                // TODO: Add your code here
    
                string filename = "3445_WorkForceReport 11272017-{1}.xls";
    
                string pat= @"[0-9]{2}[0-9]{2}[0-9]{4}";
    
                Regex r = new Regex(pat, RegexOptions.IgnoreCase);
    
                // Match the regular expression pattern against a text string.
                Match m = r.Match(filename);
                MessageBox.Show(m.ToString());
                Dts.TaskResult = (int)ScriptResults.Success;
    		}
    

    You can read the file name dynamically and assign the result to a variable so you can reuse it in other components.

    Check if this helps.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Pirlo ZhangModerator Tuesday, December 5, 2017 7:51 AM
    • Marked as answer by pizaro87 Thursday, December 7, 2017 8:22 PM
    Thursday, November 30, 2017 7:19 AM
    Moderator

All replies

  • Hi pizaro87,

    Then use the Script Task as described in this answer https://stackoverflow.com/questions/37339916/excel-file-source-ssis-excel-file-name-retrieval


    Arthur

    MyBlog


    Twitter

    • Marked as answer by pizaro87 Thursday, December 7, 2017 8:21 PM
    • Unmarked as answer by pizaro87 Thursday, December 7, 2017 8:22 PM
    Wednesday, November 29, 2017 6:08 PM
    Moderator
  • If the "3445_WorkForceReport" is always consistent, get the filename portion and then substring(<filename>, 22, 8) - may need some adjustment to start position, I think it's 22 but can never remember if it's 0 based or not.
    • Marked as answer by pizaro87 Thursday, December 7, 2017 8:22 PM
    Wednesday, November 29, 2017 6:51 PM
  • Hi pizaro87,

    If possible, personally, the most appropriate method is to use Regular expression to extract the date. Please refer to following script snippet:

    public void Main()
    		{
                // TODO: Add your code here
    
                string filename = "3445_WorkForceReport 11272017-{1}.xls";
    
                string pat= @"[0-9]{2}[0-9]{2}[0-9]{4}";
    
                Regex r = new Regex(pat, RegexOptions.IgnoreCase);
    
                // Match the regular expression pattern against a text string.
                Match m = r.Match(filename);
                MessageBox.Show(m.ToString());
                Dts.TaskResult = (int)ScriptResults.Success;
    		}
    

    You can read the file name dynamically and assign the result to a variable so you can reuse it in other components.

    Check if this helps.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Pirlo ZhangModerator Tuesday, December 5, 2017 7:51 AM
    • Marked as answer by pizaro87 Thursday, December 7, 2017 8:22 PM
    Thursday, November 30, 2017 7:19 AM
    Moderator