August 18, 2009 at 9:53 am
Hi
I have two packages running...
Now I want when package runs it logs into sysdtslog90.
When first package runs I want to lock this table sysdtslog90 so that the if the second pacakge runs at the time, it doesnt do the logging at the same time, then after the 1st package get the logging finished the second package begins the logging...
August 18, 2009 at 10:20 am
You could write logic into the beginning of both packages to check if the other is running. This is stored in MSDB, and you can query to determine if the status of a package is running. From there, you could loop and waitfor the status to say complete, and at that time let the rest of the package run.
Is there some reason for trying to separate the logging? Perhaps we have other ideas.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 18, 2009 at 10:25 am
And where in asp.net code should I check whether any other package is running or not... I dint got this point...
And What are the other possiblities
August 18, 2009 at 10:27 am
Ok You mean MSDB....
Can you please expalin me how that can be done
August 18, 2009 at 10:59 am
If you run the package as a job, then it will be recorded. If you run it some other way, you'd need to note that. The easiest way is insert a value in a tracking table when the package starts and then update it (or delete it) at the end.
On that note, do you always run these two packages together? Can you just scheduled them to run one at a time?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 18, 2009 at 2:12 pm
Actually I have N no of packages... So I cant decide like when they would be started... many of them can start at the same time... So I am trying to do scheduling...
If you can provide me some links would be helpful
August 18, 2009 at 3:12 pm
I am still not clear what you mean. A package cannot run without being scheduled or called from some other program, so you can typically arrange a workflow.
I'm also not clear why the logging is an issue. It sounds like you might be trying to solve something you don't need to. Perhaps you can provide more detail about what you want to accomplish rather than just the details of what you have.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 18, 2009 at 11:15 pm
This is what is Have :
I have a dtsx package which does logging in to sql server 2005, like this I have many packages.
Now I am executing these packages from asp.net application.
What I want to do :
When there are more than one packages executing at the same time, I want one package to be executed at a single time while rest of the packages waiting for the first one to get over with its operation on database.
August 19, 2009 at 12:25 am
When there are more than one packages executing at the same time, I want one package to be executed at a single time while rest of the packages waiting for the first one to get over with its operation on database.
You say your packages are executed by an asp.net application? Well then why don't you get your asp.net application to control what it is you want? Surely if you can execute a package in asp.net then when the package is finished running you can check that as well, and then only execute the next package?
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 19, 2009 at 12:38 am
Yes I can in Asp.net but the requirement is to be done is Sql server
August 19, 2009 at 12:51 am
What requirement needs to be done in SQL server?
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 19, 2009 at 4:18 am
amit (8/18/2009)
This is what is Have :I have a dtsx package which does logging in to sql server 2005, like this I have many packages.
Now I am executing these packages from asp.net application.
What I want to do :
When there are more than one packages executing at the same time, I want one package to be executed at a single time while rest of the packages waiting for the first one to get over with its operation on database.
How are you executing the packages? Using the API from .NET code? Calling a proc? Something else? If you are executing it directly from the asp.net app, note that you will need to have SSIS running on the web server which also means you need a license for that server.
One way you could accomplish your "only a single package running at a time" requirement is to use the SQL Server Agent. Could you call a proc from asp.net which would "queue" the ssis package to run. The proc would then schedule a job. You will need some sort of wrapper around the job system to ensure that a single package can run at a time.
August 19, 2009 at 4:45 am
A few thoughts.
1.All your packages log to Sysdtslog90
2. Every time a package starts a row is inserted into Sysdtslog90 and the column 'event' shows the value 'packagestart'.
3. Every time a package finishes a row is inserted into Sysdtslog90 and the column 'event' shows the value 'packageend'.
4. As Steve said there is a SQL method to check to see if SSIS packages are still running.
take advantage of this and only execute the packages when a package is not running.
Could even create a view jobruning that shos 1 for active job and 0 for no active jobs.
HTH
Ells.
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply