June 6, 2011 at 10:20 am
Hello Everyone :-D,
I'm trying to figure out the best way to pass parameters to an SSIS package in SQL 2005. Currently we have a "common configuration" file that we pass the start date and end date for data loading to all our packages and that works great, but it has to be manually modified everytime we want to change the date range. The requirement now is to just pass the start date and end date range to an SQL Server Agent job that will invoke the package. I cannot figure out how to do this as I've scoured the internet reading articles about using the "set values" tab in the SQL Agent job properties. Eventually we might want to pass the current day and all dates in the last 2 months. Please help as I'm going nutts over here! Thanks to the guru who knows the light here!!!
-Dave 😎
June 6, 2011 at 10:23 am
Have you thought about storing the configuration in SQL Server instead of in an XML File? Then you can just update the configuration values via T-SQL procedure before kicking off the packages(s).
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
June 6, 2011 at 10:25 am
From what you say, it sounds like you want a completely flexible number of parameters (from your "..all dates in the last 2 months" comment).
Have you thought about setting up a physical SQL Server table to contain the dates and modifying your package to pick up the information from there instead?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 6, 2011 at 10:26 am
Aargh, Farrelled again! 😀
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 6, 2011 at 10:27 am
Chris,
I'm not sure how to go about the solution your speaking of. If you could enlighten me on this approach I would greatly appreciate it. Maybe list some steps and hopefully the light will turn on for me.
Thanks a bunch,
Dave
June 6, 2011 at 10:29 am
davidsalazar01 (6/6/2011)
Chris,--
Is that Craig Chris or Phil Chris? 🙂
We need to know what you will be doing with the dates before we suggest the best solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 6, 2011 at 10:47 am
I have not thought about that approach, but it seems like a good solution. Can you list the detailed steps to do this? Thank you
June 6, 2011 at 10:51 am
So we would do something like this with the dates as shown below:
Day 1 (Monday):
Select getdate() and the last 2 months
Day 2 (Tuesday):
Select getdate() and the last 2 months
And so on. This logic would be passed to a scheduled SQL Agent job that invokes and SSIS package.
Does that make sense? Sorry if I'm not communicating this well. I'm struggling trying to figure out how to get my message across.
June 6, 2011 at 10:55 am
davidsalazar01 (6/6/2011)
So we would do something like this with the dates as shown below:Day 1 (Monday):
Select getdate() and the last 2 months
Day 2 (Tuesday):
Select getdate() and the last 2 months
And so on. This logic would be passed to a scheduled SQL Agent job that invokes and SSIS package.
Does that make sense? Sorry if I'm not communicating this well. I'm struggling trying to figure out how to get my message across.
If it's two variables, which it sounds like, you've got one variable for 'getdate()' and another for Getdate() - 60 (2 months). If you're setting them currently via XML configuration, simply alter that to use a table in the ssis configurations component from the tool bar in design mode and redeploy the package. Then you can simply update the results in that table via T-SQL.
If you're trying to feed it 60 parameters, this may get painful.
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
June 6, 2011 at 10:55 am
Phil Parkin (6/6/2011)
Aargh, Farrelled again! 😀
ROFL! I don't think I've ever heard my name used as a verb before...
... well, not for anything good. 😉
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
June 6, 2011 at 11:31 am
My bad on the Chris, I meant Craig.
June 7, 2011 at 9:40 am
Craig or Phil,
Your suggestions are great. I wanted to know if either one had some time today or this morning and would be willing to connect to shared session via "Go 2 Meeting" to assist with this task on my dev environment. I'm even willing to be billed for it as long as it get's done correctly. I also need to do other tasks such as invoke PL/SQL packages from the SSIS packages in addition to passing parameters to the SSIS packages. I need an SSIS stud because I need to get this done ASAP, so please let me know and I'll send the invite for the shared session shortly after.
Thanks in advance,
Dave 😎
June 7, 2011 at 11:29 am
davidsalazar01 (6/7/2011)
Craig or Phil,Your suggestions are great. I wanted to know if either one had some time today or this morning and would be willing to connect to shared session via "Go 2 Meeting" to assist with this task on my dev environment. I'm even willing to be billed for it as long as it get's done correctly. I also need to do other tasks such as invoke PL/SQL packages from the SSIS packages in addition to passing parameters to the SSIS packages. I need an SSIS stud because I need to get this done ASAP, so please let me know and I'll send the invite for the shared session shortly after.
Thanks in advance,
Dave 😎
Sorry I'm going to have to say no to that, particularly during working hours. Conflict with the current position and all that. Others may chime in though, but I'll be willing to help you via postings from here. That and one-off billings are a real pain to deal with, just an FYI.
That, and I'm not sure I'm the right guy for the job, I've never done this: " invoke PL/SQL packages from the SSIS packages ", although I assume it's just running a command line structure.
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
June 7, 2011 at 12:28 pm
Craig,
No problem I understand your position and thanks for letting me know. I actually have a collegue that is going to assist me so I think I should be able to get the requirements done in time.
Thanks again,
David
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply