September 16, 2014 at 11:59 am
Hi, I'm hoping someone can give me some tips on how to create an SSIS package that takes two date variables (start and end dates) as parameters in the where clause of a SQL statement created during run time. This package should then use the created SQL to pull data from another database environment (user's choice - but should be either Oracle, MS SQL or Teradata) and load it into a new table called <PatientEncounter> in the local SQL Server environment. After the data is pulled, execute a local batch file called FastLoad.bat. Also, loop through the result set and send an email to each of the selected email addresses based on a template - pre-filled with values in the select columns.
I have attached some sample data and output.
September 16, 2014 at 12:25 pm
I don't mean to be a complete schmuck, but that's the kind of thing I get hired for, not volunteer on a board to help folks do their work and learn the software.
It also means knowing a lot about your environment.
However, I can give you a few pointers to get you in the right direction. Caveat: You're writing a small application, not an SSIS package. This is not where I would do this work and I'd speak with the app devs directly about alternatives.
1) You don't want to set this up as a configuration, especially if it's user driven. What you'll want to do is setup a queue-ing table, and pick off (probably via a loop in the package) each row, perform your tasks, and set the row as completed. This is most easily done via the ExecuteSQL task, or using a local recordset as a feed for the For Each loop object.
2) If the user's choosing the source of the data, you're going to need to setup branching paths with basically the same processes but controlled via "Expression and Constraint" in the Precedence Constraints to only go into the correct data flows.
3) Patient Encounter needs a report identifier of some kind or different users will step on each other. I recommend you simplify this by using an ID from the queue table.
4) Execute Process Task will let you execute your batch file.
5) There is an email send task as well. You'll be using another recordset and another for each object to get your emails done.
6) You'll want to schedule this ssis package (since you're not firing it from the front end) to run every 5 minutes or so, or at whatever rate you really need it. I'd try to get 1 hour passes on this.
Hopefully that helps. To repeat: I would not be using SSIS for user driven content like this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 13, 2014 at 9:25 am
Hey thanks very much for the advice. Sorry I didn't get back sooner, been tied up.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply