Extract File name from Flat File Destination

  • abhilashdk2013 52858

    Valued Member

    Points: 59

    Hi All,

    I am new to SSIS. I created a simple package that extracts data from a table and copies that data to a text file. It works well.

    Now my requirement is, as soon as this process is complete I want to create a Execute Process Task and open the text file. How to get the name of the file from Flat File Destination.

    For Example Say My Table name is Demo with ID and Name as columns. I have flat file destination pointing to C:\\Demo.txt. As soon as the data is written into C:\\Demo.txt I need to create Execute Process task and open this particular file in notepad. i.e First I need to extract that file name from Flat File Destination and pass it to Execute Process task.

    Can I do this. If So How?

    Thanks and Regards,

    Abhilash D K:-)

  • Phil Parkin

    SSC Guru

    Points: 243910

    abhilashdk2013 52858 (10/19/2015)


    Hi All,

    I am new to SSIS. I created a simple package that extracts data from a table and copies that data to a text file. It works well.

    Now my requirement is, as soon as this process is complete I want to create a Execute Process Task and open the text file. How to get the name of the file from Flat File Destination.

    For Example Say My Table name is Demo with ID and Name as columns. I have flat file destination pointing to C:\\Demo.txt. As soon as the data is written into C:\\Demo.txt I need to create Execute Process task and open this particular file in notepad. i.e First I need to extract that file name from Flat File Destination and pass it to Execute Process task.

    Can I do this. If So How?

    Thanks and Regards,

    Abhilash D K:-)

    SSIS is a server-side technology & therefore opening files in Notepad is pointless.

    Having said that, your flat file destination should be using a connection manager. You can get the file name details from the CM using a script task (I think – never had to do this myself).

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • abhilashdk2013 52858

    Valued Member

    Points: 59

    Hi,

    Thank you for the reply. I was just trying out as I am just learning. I will learn Script Task and try to get the file name from Connection manager.

    Thanks and Regards,

    Abhilash D K:-)

  • Phil Parkin

    SSC Guru

    Points: 243910

    abhilashdk2013 52858 (10/19/2015)


    Hi,

    Thank you for the reply. I was just trying out as I am just learning. I will learn Script Task and try to get the file name from Connection manager.

    Thanks and Regards,

    Abhilash D K:-)

    A more likely scenario is that you would move the file to another folder after processing it.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Shawn Melton

    SSC-Insane

    Points: 24675

    You will generally design your packages with your connections parametized (SSIS 2012+) or using variables to build your connection string, which will provide your path to that file accessible in an expression.

    In an execute process task you can build an expression for your arguments property and therefore just reference that parameter or variable to pass the file name to the notepad.exe process. In most cases a process like Notepad.exe or even PowerShell.exe can handle the whole path for the file being passed to it, so there is no reason to try and just get the file name.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • abhilashdk2013 52858

    Valued Member

    Points: 59

    Hi,

    Thank you for your reply. As I said I am still learning and there is no specific reason for this scenario. I did not know how to extract the File Name or File Path from the Connection Manager.

    As you guys suggested I will use a Script Task to get the File Path and Use it in Execute Process Task and will also try to move the File to a different location.

    To Move the file to a different location I should use File System Task by specifying the operation as Move To, right?

    But to do this also I need the Path to the output file(the file which I would like to move) which I should get from Connection Manager using Script Task, right?

    And as Shawn Melton specified the connection string will be parameterised as of 2012+. But I have not got until there. I am still a newbie for SSIS ;-). But thank you for giving me this info.

    Thanks and Regards,

    Abhilash D K:-)

  • Phil Parkin

    SSC Guru

    Points: 243910

    abhilashdk2013 52858 (10/19/2015)


    Hi,

    Thank you for your reply. As I said I am still learning and there is no specific reason for this scenario. I did not know how to extract the File Name or File Path from the Connection Manager.

    As you guys suggested I will use a Script Task to get the File Path and Use it in Execute Process Task and will also try to move the File to a different location.

    To Move the file to a different location I should use File System Task by specifying the operation as Move To, right?

    But to do this also I need the Path to the output file(the file which I would like to move) which I should get from Connection Manager using Script Task, right?

    And as Shawn Melton specified the connection string will be parameterised as of 2012+. But I have not got until there. I am still a newbie for SSIS ;-). But thank you for giving me this info.

    Thanks and Regards,

    Abhilash D K:-)

    Shawn's suggestion is simpler than mine, as it does not require a Script Task. What version of SSIS are you using?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • abhilashdk2013 52858

    Valued Member

    Points: 59

    Hi,

    I am using SQL Server 2012.

  • Krishh

    SSC Eights!

    Points: 929

    Hi,

    You can pass the file name through expression builder of the execute process task. You should already have a variable in SSIS that would store the filename.

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

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