CODEWITHSUNDEEP
sqlssissql-server-2008-r2
totalfreakingnoob
totalfreakingnoob

Reputation: 423

SSIS Package with parameters / configuration file

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?

Upvotes: 0

Views: 2028

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

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.

Upvotes: 1

Related Questions