Extract multiple files from multiple directories with a data flow task

Question

Extract multiple files from multiple directories with a data flow task

Stan Kulp-439977

SSChampion

Points: 10062
More actions
October 27, 2011 at 12:02 am

#250728

Comments posted to this topic are about the item Extract multiple files from multiple directories with a data flow task

Viewing 15 posts - 1 through 15 (of 19 total)

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

wlblee38 Old Hand Points: 386 More actions · Answer 1

Good job, very well written and demonstrated. Awesome!

Do you have something for connecting to a remote sever and downloading files? I think that's the next thing you should add to this.

Thanks again, Super!

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 2

That's what this job actually does. I just used local directories for demonstration purposes.

You would simply change the directory path from c:\folder\ to \\server\folder\, if I understand your question correctly.

deepak.a SSCertifiable Points: 5330 More actions · Answer 3

deepak.a

SSCertifiable

Points: 5330

October 27, 2011 at 10:50 pm

#1400489

Thanks For this article excellent explanation 🙂

e.t.anicet SSC Enthusiast Points: 151 More actions · Answer 4

Hi,

You have done a very good demonstration.

I did try what you have done in my visual studio 2005 but it doesn't work. It seems like the vb script code doesn't work well.

After I have configurated your package (MultipleFileExtraction2005.dtsx) in my 2005 environment, when I run it just the "Get list of data files" (Script task) and the "Loop through list of data files" (ForEach Loop task) what are became green and the other tasks not

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 5

Sounds to me like it is not finding the data files. Make sure you have the paths set correctly, and files actually in the folders.

e.t.anicet SSC Enthusiast Points: 151 More actions · Answer 6

Yes you right, my data files were not into the correct location.

Thanks 😉

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

1. What kind of privs does the SSIS job need to be able to run the VB.Net scripts for this job?

2. What kind of privs does someone need to run this job?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 8

Great package and article. Thank you! I am saving this one 😉

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 9

It is the connection manager used by the SQL statement in the script task that determines whether the SQL statement can be executed.

If your connection manager is using authentication that would allow you to execute the SQL Statement in SQL Server Management Studio, it will allow you to execute that same statement from an SSIS script task.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Stan Kulp-439977 (10/30/2011)
It is the connection manager used by the SQL statement in the script task that determines whether the SQL statement can be executed.
If your connection manager is using authentication that would allow you to execute the SQL Statement in SQL Server Management Studio, it will allow you to execute that same statement from an SSIS script task.

Understood. But what level of privs does it actually need to work?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 11

What is your actual concern?

Can you not get it to run?

It would help if I knew what the problem was.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Nope... not having problems getting it to run because I haven't actually tried it. 😉 What I want to know is just exactly what I asked but I'll change the questions to a single question...

You've built this very cool package. I'm a user. I want to run your package. What privs do I need as a user to run your package?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 13

Are you going to be running it from Development Studio?

How do you usually run your packages?

ray.abak-1147615 Valued Member Points: 60 More actions · Answer 14

I hope my posting is not too late. I have 140 data file extracts being generated on a daily basis to load into SQL tables. Each data file has different number of columns and names, and so needs to be loaded into its own separate table.

If the automated loading process fails to run overnight, I end up with 280 files in the folder the following day. If it doesn’t work over the weekend, I end with three days worth of data to load in on Monday morning after resolving the problem.

I have modified Stan’s method (Extract multiple files from multiple directories with a data flow task) to suit my particular needs but when I run the package, I get the following error:

Error: 0xC00220DE at Call SSIS package to load each data file: Error 0x80070003 while loading package file "". The system cannot find the path specified.

What I want the package to do is load all data files into the tables, one by one. If there is more than one day worth of data files in the folder, I want the package to load them in date order. For example, if one set of data files (140) was generated two days ago, I want the package to load them in first, followed by those generated yesterday and lastly by those generated today. The dates on which the files were generated form part of the file names.

I have added some steps to Stan’s SSIS package (see MultipleFileExtraction2008.dtsx). My package appears as follows:

1) A master package named MFE_MASTER.dtxs, from which each mini package (Data Flow Task) is called to load a data file.

2) An Execute SQL Task to create and populate a parameter table called tbl_ProgramParameter if doesn’t exist already.

3) An Execute SQL Task to truncate the staging table. This is populated by a variable which passes the name of the staging table to the package.

4) An Execute Package Task which calls the Data Flow Task (mini package) designed to load each data file. The expression tab of the Execute Package Task is configured to PackageName Parameter to a variable called @[User::PkgName]. The variable is defined in each Data Flow Task.

5) An Execute SQL Task that calls a stored procedure to load the data into the staging table and transfer it to the storage table. The Parameter Mapping tab of the Execute SQL Task is populated with variables to pass the names of the staging and storage tables and a key field to the package. These variables are defined in each Data Flow Task (mini package).

6) A file System Task to move files from the source to archive folder.

Since it is failing at step (4) above, I have no way of knowing whether steps (5) and (6) will work or not. I have tried to resolve the problem several times but it just keeps throwing up the same error. I am a novice in creating SSIS package / Script Task, so am wondering if another variable needs to be defined in the package or something added to the script task. I would appreciate it if someone could help please and share the solution with me.

The following information is attached in the zipped file:

1) Sample data files to be loaded into the tables

2) SQL script to create tables for loading the data

3) SQL script to create a ProgramParameter table

4) SQL script to create two stored procedures, one for truncating the staging table and the other for loading data.

5) SSIS packages (includes a master package and six mini packages being called from the master package).

Path where SSIS pacakages were created is E:\GEH_SSIS\MR5\MR5

Thank you so much in advance for your help.

Ray

ray.abak@geh.nhs.uk