SSIS- Need File Name which i am loading in Variable

  • I Need File Name which i am loading via Excel. Any Idea

  • More information as to what you're trying to do, please. That's rather vague.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • vax09 (10/9/2012)


    I Need File Name which i am loading via Excel. Any Idea

    Are you using a For Each Loop?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi

    If you are trying to loop through files and load the data and the file name to the target table?

    In that case Inside "derived column" toolbox you have Variables and Columns. Just add a derived column, lets say FileName, with the Variable value. When loading table, just map derived column FileName to the table column sourcefileName.

    That way it worked for me, If this is not what you are looking for like Evil Kraig F said please elaborate your scenario so you can have better help.

    Good Luck!!!

  • NO i am just importing excel sheet and i need to use that excel sheet name in one column

  • i am importing excel file and i want to use the name of excel file in my table

  • Does the name of the excel sheet change? I guess not, so you can just use a derived column component that adds a column with the name of the Excel file.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • If the name doesn't change then I would just hardcode the name of the file into a variable and feed that through with a derived column. But I prefer to add a loop around the file extraction data flow and map the result set of your loop to a variable - just in case the name changes.

  • Done this before. used a controlling/audit table. Loop through the folder and add the file names to the table. You can have other audit info you might need in the table as well. date, loaded flag etc.

    then use this table to load the data.

    Ian Cockcroft
    MCITP BI Specialist

Viewing 9 posts - 1 through 8 (of 8 total)

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