How to capture subfolders' path as variable and import them into database

  • Hi I am a beginner of SSIS 2008 with VB code.

    I have a folder with subfolders as below:

    C:\Data\20150101\Target.dbf

    C:\Data\20150102\Target.dbf

    Directory Folder, Subfolder haven't Target.dbf file

    All Target.dbf are same structure but different data.

    My idea is to:

    1. Write a script task to capture all subfolder paths

    2. Store all subfolder paths as variable

    3. Use "Foreach Loop Container" with "Data Task Flow" to import the Target.dbf files from different subfolders into Database.

    There are many C# script available but not VB, so would you please advise how to

    1) write a script task to capture all subfolder paths

    2) setup the "Data Task Flow" with variable as the Connection Manager

    Thank you.

  • There are probably newer-fangled ways to do this, but I would stick to what I know, and import the list of files into a staging table so that you can loop through it. Assuming xp_cmdshell is enabled on the server and the SQL Server service account has access to the folder in question, you can run a command such as [font="Courier New"]dir c:\data\Target.dbf /s /b[/font] and bring the results straight into your staging table. You can then select from that table and use the recordset in a ForEach Loop container.

    John

  • Why not just configure the Foreach Loop to crawl through all the subfolders? No scripting required.

    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.

  • if you include System.IO, you can use the FileInfo object:

    --this is vb.Net code

    Dim MyFileInfo As System.IO.FileInfo = New FileInfo("C:\Data\20150101\Target.dbf")

    Dim FilePath As String = MyFileInfo.DirectoryName

    Dim FileName As String = MyFileInfo.Name

    also, whenever you encounter some C3# code, you canuse this free web page utility to convert c# --> VB.Net or vice versa, so any example you find will typically work right away for you, once converted

    http://converter.telerik.com/

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • John Mitchell-245523 (1/2/2015)


    There are probably newer-fangled ways to do this, but I would stick to what I know, and import the list of files into a staging table so that you can loop through it. Assuming xp_cmdshell is enabled on the server and the SQL Server service account has access to the folder in question, you can run a command such as [font="Courier New"]dir c:\data\Target.dbf /s /b[/font] and bring the results straight into your staging table. You can then select from that table and use the recordset in a ForEach Loop container.

    John

    Thanks for your reply, John.

    To enable the xp_cmdshell, I run the following scripts:

    ---------------Scripts---------------

    EXEC sp_configure 'show advanced options', 1

    GO

    RECONFIGURE

    GO

    EXEC sp_configure 'xp_cmdshell', 1

    GO

    RECONFIGURE

    GO

    ---------------Scripts---------------

    and the message are below:

    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

    Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.

    I think the xp_cmdshell is enable, right?

    For running a command such as dir c:\data\Target.dbf /s /b and bring the results straight into my staging table, I still don't know how to do so. Please kindly advise. Thank you.

  • Phil Parkin (1/2/2015)


    Why not just configure the Foreach Loop to crawl through all the subfolders? No scripting required.

    Thank you for your reply Phil.

    I find MS Visual Studio not allow me to directly configure it.

    Could you advise how to setup the Connection Manager?

    I have tried to simply select the dbf file in the subfolder 20150101 to import into database successfully.

    But it is one file only. It is not apply on Foreach Loop with multiple subfolders.

    When I setup the Foreach Loop with Data Flow Task, I setup the OLE DB Source and there are nothing for me to select in "Name of the table or the view:"

    Please kindly advise. Thank you.

  • Lowell (1/2/2015)


    if you include System.IO, you can use the FileInfo object:

    --this is vb.Net code

    Dim MyFileInfo As System.IO.FileInfo = New FileInfo("C:\Data\20150101\Target.dbf")

    Dim FilePath As String = MyFileInfo.DirectoryName

    Dim FileName As String = MyFileInfo.Name

    also, whenever you encounter some C3# code, you canuse this free web page utility to convert c# --> VB.Net or vice versa, so any example you find will typically work right away for you, once converted

    http://converter.telerik.com/

    Thanks for your reply Lowell.

    The script seems only import one subfolder directory, if I have 365 subfolder (i.e. 365 days), the script should be changed?

    Please kindly advise. Thank you.

  • You cannot configure the connection directly like that.

    Please read through this [/url]article and see whether it helps you.

    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.

  • chadlau11 (1/4/2015)


    John Mitchell-245523 (1/2/2015)


    There are probably newer-fangled ways to do this, but I would stick to what I know, and import the list of files into a staging table so that you can loop through it. Assuming xp_cmdshell is enabled on the server and the SQL Server service account has access to the folder in question, you can run a command such as [font="Courier New"]dir c:\data\Target.dbf /s /b[/font] and bring the results straight into your staging table. You can then select from that table and use the recordset in a ForEach Loop container.

    John

    Thanks for your reply, John.

    To enable the xp_cmdshell, I run the following scripts:

    ---------------Scripts---------------

    EXEC sp_configure 'show advanced options', 1

    GO

    RECONFIGURE

    GO

    EXEC sp_configure 'xp_cmdshell', 1

    GO

    RECONFIGURE

    GO

    ---------------Scripts---------------

    and the message are below:

    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

    Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.

    I think the xp_cmdshell is enable, right?

    For running a command such as dir c:\data\Target.dbf /s /b and bring the results straight into my staging table, I still don't know how to do so. Please kindly advise. Thank you.

    Yes, that means it was already enabled. Enabling it again doesn't do any harm, though.

    Do something like this:

    CREATE TABLE #DirList (Folder varchar(max))

    INSERT INTO #DirList

    EXEC xp_cmdshell 'dir c:\data\Target.dbf /s /b'

    Beware that if you attempt to use the results in a different task, you may find that the temp table has been lost. If that happens, consider using a permanent staging table instead.

    John

  • Phil Parkin (1/5/2015)


    You cannot configure the connection directly like that.

    Please read through this [/url]article and see whether it helps you.

    Hi Phil, I have modified it by myself according to the article[/url] you shared.

    I use the article to copy the target files from subfolders to a target folder.

    Then set the connection configuration to import the dbf file from the target folder to the database one by one.

    Thank you.

  • If you haven't already you may want to disable xp_cmdshell, since your solution doesn't seem to require it. It can be a bit of a security risk. 😉

  • chadlau11 (1/5/2015)


    I use the article to copy the target files from subfolders to a target folder.

    Then set the connection configuration to import the dbf file from the target folder to the database one by one.

    I'm trying and trying to do this on my package to capture the file name but nothing seems to work. Can you be more specific regarding the connection configuration? What are you setting it to and how are you passing that into your table? This is what i'm confused about.

  • Polymorphist (1/9/2015)


    chadlau11 (1/5/2015)


    I use the article to copy the target files from subfolders to a target folder.

    Then set the connection configuration to import the dbf file from the target folder to the database one by one.

    I'm trying and trying to do this on my package to capture the file name but nothing seems to work. Can you be more specific regarding the connection configuration? What are you setting it to and how are you passing that into your table? This is what i'm confused about.

    Hi Polymorphist,

    What is the source file format? Mine is .dbf file and they are stored in a folder with subfolders as below:

    C:\Data\20150101\Target.dbf

    C:\Data\20150102\Target.dbf

    For Connection Manager, I am using:

    Provider: Native OLE DB/Microsoft Jet 4.0 OLE DB Provider

    Please provide more information to discuss

Viewing 13 posts - 1 through 12 (of 12 total)

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