SSIS Package with parameters / configuration file

0
votes

Just wondering what the best approach would be for achieve something like this:

I have a table I want to interrogate for data and e-mail out the outcome but the parameters will vary. The string I'd be looking for is subject to change as is the date/time range being search and some other configurable items will vary too.

What I was thinking about doing would be to create an SSIS package which would work in conjunction with some sort of text files which contained the variables such as search string, range, etc. and then using dtexec I would pass the filename (which contains the variable items) as a parameter. Since it's an SSIS package I should be able to run it with a schedule or ad-hoc which would be preferable and I would have one SSIS package instead of many.

Then in the end if I want to run CustomSearch1 I would run: dtexec /file MyPackage.dtsx /Set \Package.Variables[User::INIfile].Properties[Value];\"CustomSearch1\"

So for example if I want to run CustomSearch2 I would run: dtexec /file MyPackage.dtsx /Set \Package.Variables[User::INIfile].Properties[Value];\"CustomSearch2\"

Is this a good way of doing it or is there a better way?

1
sqlssissql-server-2008-r2
Can you give us a bit more step-by-step of the whole process? Is it just "apply variables, filter data, email out results"? Is something else going on? – Kyle Hale
That's pretty much it. The table holds an "audit" of sorts so I'm checking to see if things have completed or errored. So an example of the one of the strings it would check for is "Job1 has completed" and I would check for this string between certain times. Perhaps this string needs to appear multiple times throughout the day to consider it a "success". – totalfreakingnoob

1 Answers

1
votes

It's certainly one way to do it, and perfectly valid.

You could also consider placing your search conditions in their own database table labeled with your search name in another field ("CustomSearch1", etc.) and then pass that field like you're doing. In my mind this seems easier to maintain than text files, but you can go with what you're comfortable with.

More generally, why SSIS? Why not, for example, a very simple C# console app that runs a stored procedure against the table with your parameters passed in, returns the set, and emails it? SSIS is generally more well suited for actually moving data from place to place, or processing it in some way, or deep statistical sampling, et cetera.

Or perhaps SSRS? Write a stored procedure that takes a custom search name and returns the correct data. Parameterize the search name. SSRS reports can be scheduled and emailed or run ad-hoc and the contents emailed out.