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

Question

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

chadlau11

SSC Veteran

Points: 237
More actions
January 2, 2015 at 3:42 am

#302068

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.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246961 More actions · Answer 2

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

chadlau11 SSC Veteran Points: 237 More actions · Answer 4

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.

chadlau11 SSC Veteran Points: 237 More actions · Answer 5

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.

chadlau11 SSC Veteran Points: 237 More actions · Answer 6

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.

Phil Parkin SSC Guru Points: 246961 More actions · Answer 7

You cannot configure the connection directly like that.

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

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

chadlau11 SSC Veteran Points: 237 More actions · Answer 9

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.

steve the dba SSCrazy Points: 2929 More actions · Answer 10

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. 😉

Polymorphist SSCommitted Points: 1909 More actions · Answer 11

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.

chadlau11 SSC Veteran Points: 237 More actions · Answer 12

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