August 16, 2011 at 1:40 pm
Is there a way to send a message to SQL Server 2008 to have it execute a SSIS package and send that package a variable? The variable would be a number - like a 1 or something.
What would be super ideal is for users to be able to send an email to an account just for SQL Server, SQL Server could check the email to get the number and then feed that number to a SSIS package which would then execute a certain way based on that number.
I'm probably asking for way to much here....
JamesNT
August 16, 2011 at 1:47 pm
Maybe we could have sql server make dinner right after it gets done using Outlook? 😛
In all seriousness what exactly are you trying to do?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2011 at 1:58 pm
LOL!
We have an issue where after data entry, certain fields need to be verified. I was hoping to create a SSIS package that end users could invoke that would check certain fields for valid data and email users which Patient has the invalid fields. Making the SSIS package is easy enough - I've already done that. I just need a way end users can invoke it and send it a number as to which database they want it to check.
JamesNT
August 16, 2011 at 2:01 pm
jamesnt (8/16/2011)
LOL!We have an issue where after data entry, certain fields need to be verified. I was hoping to create a SSIS package that end users could invoke that would check certain fields for valid data and email users which Patient has the invalid fields. Making the SSIS package is easy enough - I've already done that. I just need a way end users can invoke it and send it a number as to which database they want it to check.
JamesNT
why involve end users to start the job?
you could have a regularly schedule job that checks newly entered data every x minutes, and sends an email to someone if an exception is found.
or create a seperate web service or a web page , and they can use that to validate data or something on demand.
you cannot validate the data at teh data entry point in the Application layer? it has to be done after the data entry occurred?
Lowell
August 16, 2011 at 4:37 pm
The way I've usually done this is create a table to store a message of some sort. Could be a bit, could include a user, time, etc. Then what I have is a job scheduled to check this table for a value. If it's found, it then executes xx, which could be a package.
That way you haven't created rights out of the ordinary for users to run a package, and you're not as open as email. The user has to have rights to insert or update the table to cause the execution.
However if you can automate this, I might do that as Lowell suggested. Read the data and look for a condition to trigger a package.
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 16, 2011 at 4:50 pm
Service Broker could be another option, though validation at an earlier point in the process might be good too.
August 17, 2011 at 2:23 pm
Lowell,
why involve end users to start the job?
you could have a regularly schedule job that checks newly entered data every x minutes, and sends an email to someone if an exception is found.
or create a seperate web service or a web page , and they can use that to validate data or something on demand.
you cannot validate the data at teh data entry point in the Application layer? it has to be done after the data entry occurred?
1. Because that would make the job efficient. I have 80 databases to poll through. I would think it best to have it on-demand.
2. The application in question is an old VB6 app that we do not have the source code for. Yes, this is one of those situations.
JamesNT
August 17, 2011 at 2:24 pm
Chuck,
Can you offer a possible scenario on how Service Broker would do this?
JamesNT
August 17, 2011 at 7:14 pm
For service broker, you'd essentially run a piece of code in SQL Server, which would put a message in a queue. I think that might be overkill for this. If you have run a piece of code, even off a web page, then let that insert a value in a table and let a job read the table and run the package(s)
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, 2011 at 11:36 am
I'm leaning pretty heaving towards having the table and a web page so users can update it. That seems to be the way to go. Poling a table every minute looking for what will be no more than 2 - 3 entries at the most is far preferrable to polling 80 Access databases every hour.
I asked about Service Broker as I haven't used that yet.
Thank you all for your help!
JamesNT
August 19, 2011 at 5:45 am
Hi,
not sure how much coding you want to do, but we create an agent job "on the fly" using SMO and through the creation change the user parameter.
Take a look at the following example:
http://msdn.microsoft.com/en-us/library/ms162162.aspx
Basically you create a one time run agent job to invoke your SSIS package.
Hope this helps.
Best regards
Peter
I work for 1st Consulting, a small ISV based in the UK. We build web and desktop applications using the MSFT stack. Find us at www.1stconsulting.biz
August 19, 2011 at 9:11 am
peterban (8/19/2011)
Hi,not sure how much coding you want to do, but we create an agent job "on the fly" using SMO and through the creation change the user parameter.
Take a look at the following example:
http://msdn.microsoft.com/en-us/library/ms162162.aspx
Basically you create a one time run agent job to invoke your SSIS package.
Hope this helps.
Best regards
Peter
Do you then add have to delete the job? I would think this might fill up your agent screen without regular pruning.
Is this better than a regularly scheduled job that checks a semaphore in a table?
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 19, 2011 at 10:11 am
Hi Steve,
nope ! There is a nice little flag which says "delete on sucess" 🙂
So the only ones that are left are the ones that are a problem and need to be looked at. What I found was that these evaporated after final systems testing and so the agent jobs view ONLY had te relevant ones.
A by product of this is that you can open up the agent from the front end for user scheduling, so IMHO this is better than just an entry in a table
Cheers
Peter
I work for 1st Consulting, a small ISV based in the UK. We build web and desktop applications using the MSFT stack. Find us at www.1stconsulting.biz
August 19, 2011 at 10:46 am
Nice, I wasn't sure if you could flag that and a glance at the code didn't see one. I'll recheck.
I'm not sure I like the idea of open ended scheduling for end users, but perhaps for limited use. I worry if I allow devs to do backups, they'll eat up disk space, or I'll 25 users all trying to schedule that last minute updated sales update/report for a meeting.
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 19, 2011 at 11:25 am
Hi Steve,
The following is the code snippet in C#
"myJob.DeleteLevel = CompletionAction.OnSuccess;"
In terms of the wider picture, you are right the actual usage depends on the type of operations the site has and more generally the nature of the business.
I would not advocate general usage without knowing the "nature of the beast" ! 🙂
Cheers
Peter
I work for 1st Consulting, a small ISV based in the UK. We build web and desktop applications using the MSFT stack. Find us at www.1stconsulting.biz
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply