Post reply

How To Store details from FileName Into Variable using SSIS

  • October 19, 2015 at 12:43 am

    #149132

    Hi all

    I have one package through for each loop container I am loading all flat files into target table and its working fine.Now I my requirement is to capture the .txt file and loading into sqlserver table.Please check below file name.

    CCCLOC_DDDLOC_LOC_20151203_240000_trigger.txt

    from above file name I have to derived below detail.

    I have variable @File_Name which is used to stored the file name at rune time.

    Now I want to derived 5 values from the file name as below

    Clientname=CCCLOC

    Partnername=DDDLOC

    Entity=LOC

    Creationdate=20151203

    Creationtime=240000

    how to write expression for above situation.

  • October 29, 2015 at 6:56 am

    #1836344

    You can use script task(c#) to do this.

    String sFileName = "CCCLOC_DDDLOC_LOC_20151203_240000_trigger.txt";

    String[] ls = sFileName.Split('_');

    String sClientName = ls[0];

    String sPartnerName = ls[1];

    String sLoc = ls[2];

    String sCreationDate = ls[3];

    String sCreationTime = ls[4];

    Thanks,

  • November 20, 2015 at 8:11 am

    #1841376

    Use the variable which is used by your for each loop container in a Derived Column Transformation.

    Set Derived Column Name to Clientname, leave Derived Column as <add as new column> and set Expression to:

    SUBSTRING (@YourVariable, 1, FINDSTRING(@YourVariable,"_",1)-1)

    Add a New line for Derived Column PartnerName with expression SUBSTRING (@YourVariable, FINDSTRING(@YourVariable,"_",1)+1, FINDSTRING(@YourVariable,"_",2)-1)

    Add a new line for Derived Column Entity with expression SUBSTRING (@YourVariable, FINDSTRING(@YourVariable,"_",2)+1, FINDSTRING(@YourVariable,"_",3)-1)

    (For SSIS 2012 and above you can use the TOKEN function instead of the SUBSTRING/FINDSTRING combination)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply