Post reply

Extract File name from Flat File Destination

  • October 19, 2015 at 12:48 am

    #305527

    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:-)

  • October 19, 2015 at 12:54 am

    #1833989

    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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 19, 2015 at 1:19 am

    #1833990

    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:-)

  • October 19, 2015 at 1:54 am

    #1833997

    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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 19, 2015 at 2:03 am

    #1833999

    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

  • October 19, 2015 at 3:01 am

    #1834003

    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:-)

  • October 19, 2015 at 3:06 am

    #1834005

    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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 19, 2015 at 3:13 am

    #1834007

    Hi,

    I am using SQL Server 2012.

  • November 1, 2015 at 11:47 pm

    #1836983

    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 8 (of 8 total)

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