Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Extract multiple files from multiple directories with a data flow task Expand / Collapse
Author
Message
Posted Sunday, October 30, 2011 3:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:44 AM
Points: 36,706, Visits: 31,157
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1197839
Posted Sunday, October 30, 2011 7:14 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 123, Visits: 880
What is your actual concern?

Can you not get it to run?

It would help if I knew what the problem was.
Post #1197862
Posted Sunday, October 30, 2011 8:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:44 AM
Points: 36,706, Visits: 31,157
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1197873
Posted Sunday, October 30, 2011 8:41 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 123, Visits: 880
Are you going to be running it from Development Studio?

How do you usually run your packages?
Post #1197880
Posted Monday, November 21, 2011 4:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 7:34 AM
Points: 4, Visits: 150
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


  Post Attachments 
DataLoading.zip (3 views, 96.78 KB)
Post #1209164
Posted Monday, November 21, 2011 6:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 123, Visits: 880
I suspect that the path name is not making it to your mini-package.

Since the error message says "The system cannot find the path specified," I would put a popup in your mini-package that displays the path it is using so you can confirm that it is being passed the correct path.

Here is the code to pass the global variable to the mini-package:

Dim PathVariable As String = Dts.Variables("PathVariable").Value.ToString

Here is the code for the popup to display the path:

Dim button As DialogResult = MessageBox.Show(PathVariable, "PathVariable", MessageBoxButtons.OK)
Post #1209246
Posted Tuesday, November 22, 2011 6:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 7:34 AM
Points: 4, Visits: 150
Thank you so much Stan for your reply. After adding your script to the mini package, I got the same error. However, I have populated the Variable Mapping node of the Foreach loop with the variables vPKName, vTableName, vTempTableName and PkgName, and assigned index values accordingly. When I run it, I get a different error this time:

Error: Error 0xC00CE556 while loading package file "E:\Extracts\RLT_GE_RF_DPRUL_01DYS_20110503_0000_V01.csv" into an XML document. Invalid at the top level of the document.
Line 1, Column 1.


It seems to be reading the data file but what I dont understand is why the error says it is loading it into an XML document. Any ideas?

Thank you for your time.

Ray
Post #1210074
Posted Tuesday, November 22, 2011 6:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 123, Visits: 880
I have never passed variable-values to another package. I believe that you are not doing it right, but I can't tell you what you are doing wrong because I have never done it.

I would suggest adding all the components from the main package to your mini-package containing the data flow task to make a combined single package. That would circumvent the variable problem. I know from personal experience that it's easier to add components to the package with the data flow task than to move the data flow task to another package.
Post #1210099
Posted Tuesday, November 22, 2011 6:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 7:34 AM
Points: 4, Visits: 150
Thank you Stan. I will try that and see what happens.

Appreciated
Post #1210111
Posted Tuesday, November 22, 2011 9:27 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 123, Visits: 880
FYI

Making SSIS Dynamic: Passing variables between packages
Post #1210312
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse