February 11, 2008 at 1:58 am
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.
February 13, 2008 at 2:24 am
First question..having trouble using the for loop container against the excel spreadsheets. Could you provide more detail like an error it's throwing? Not enough info to go on there.
As for your second question..keeping the jobs from dead locking.
Well there are two easy methods for doing this.
I assuming that you have each package scheduled as a Job in SQL Agent.
Soultion 1:
Schedule both packages to run under a single job with the Table_A load and update to occur first, then on success it starts the next package.
Solution 2:
Effectively does the same thing. Make the last step of the TABLE_A etl package to launch the TABLE_B SSIS package.
Solution 3:
Create a job for TABLE_B but don't schedule it. You can put in an execute sql task as your last step of the TABLE_A job where you execute the job via T-SQL.
In the end each of these effectively does the same thing, let's the TABLE_A job tell the TABLE_B job when to begin.
Hope this helps...Eric
February 13, 2008 at 5:48 am
Thank you very much!
September 26, 2008 at 3:36 am
am i right in thinking that it's impossible to run two data flows into the same table then?
is there no way of getting SSIS to kill the deadlock?
bit annoying to line them up in series...
hold on...i just found the "lock table" option on the source OLE component 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply