SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 SSIS Package Sequential Execution ..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 02/11/2008 :  03:54:41  Show Profile  Reply with Quote
Hi Friends,


I need help from you.
I am working on SSIS packages for ETL purpose.
The version of SQL Server i am using is SQL Server 2005.

In Brief , the working of current ETL is as follows.

In ODS database i have 2 tables i.e Table_A & Table_B which gets loaded from another 2 staging tables A & B.
And using this 2 tables data will be loaded into a target table i.e Trg_A.

The ETL packages are executed by stored procedures by creating a job within the stored procedure.

The loading of the trg table is little tricky.
Before that loading of Table_A is implemented in a single SSIS package.
and loading of Table_B is been implemented in another SSIS package.

In the trg table there are two columns which will be getting updated as and when each table is loaded.
so for the first time if i run the package which is resposible for loading Table_A, it loads values into Table_A and once done it will updates (col1) in the target table.


Once after the complete of the execution of Package1.
Now i will kick off the second ssis package which loads the data into Table_B and updates the trg table's columns (col2).

Now the actual problem what i am facing is:

For loading Table_A and updating the col1 in Trg table i will be receving more than 5 excel file every month on weekly basis.
I cannot even gather all the files and run using a For-Loop counter.
So presently i am loading data excel file per week .

Similarly loading of table_B.

For a week if i am executing both the packages which loads the Table_A and updates the Trg(col1) and Table_B and updates Trg(col2), then i am getting a Deadlock Error and the entire ETL is getting messed up.


Now my requirement is , Eventhough the 2 packages are run in parallel , there could certain milli seconds time difference while start of the execution in Job Monitor.
I need to implement a Queing Mechanism which takes care of running the packages in a sequential manner rather than in parallel. i .e i need to ensure only one SSIS package is running in Job Monitor. Only after successful execution of either one the package, then only the second package should start its execution.

If we can implement such a queing mechanism , then my problem is solvedl.

I need some suggestions on this regard in implementing the Queing mechanism in a programatic approach using SQL Server Job Related MetaData Tables.
or else is there in server parameter or initialization parameters which can be set at Database level which suffice my requirement.

Any suggestions would be greatly appreciated.
Looking for sincere comments on this regards.


Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/11/2008 :  04:05:02  Show Profile  Reply with Quote
I think you can use a parent package which includes two execute package tasks one after other and each of these will calls the existing two packages so that they will be always executed sequentially. You can schedule your job to call your parent package.
Go to Top of Page

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 02/12/2008 :  09:05:58  Show Profile  Reply with Quote
Any reference links to this topic?
Go to Top of Page

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 02/13/2008 :  04:01:52  Show Profile  Reply with Quote
http://msdn2.microsoft.com/en-us/library/ms137609.aspx

Mark
Go to Top of Page

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 02/13/2008 :  07:54:56  Show Profile  Reply with Quote
Thank You very much.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000