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.