June 14, 2016 at 8:17 am
Good Day
This is somewhat straightforward. A number of our projects have start and end dates as package parameters. These are passed to set a date range with which to pull data.
Im trying to figure out the best way to automate this process for both manual and auto runs.
So the idea is set up some int variable if you will. If int is say 1 then run with manual dates else, run with start and end date of current month. Environment variables would have been great only i cant write some type of script for these values to be read from which mean i have to keep changing the environ variable start and end date every month.
I can add the TSQL to pull the dates automatically but this means the step has to be written into every package (which by the way is probably going to be my last resort).
Im interested to see how you have solved for this.
June 14, 2016 at 12:15 pm
I find the use of three project parameters the easiest way to implement the option of default or manual dates. The project parameters can then be changed for the SQL agent job when you want to run it manually.
One parameter to imply default or override behavior, and two date parameters where you can manually edit your date range. Since you are using package parameters, create a master package to control the date values. I’m using project deployment, so each package has variable logic within them to decide whether to use the project parameter or use the default value.
In case you are using the package deployment model, use either a database table or xml input file to simulate the project parameters.
June 14, 2016 at 12:20 pm
I have a generic parameter table that I use to store anything that I need to check later (what parameters were used for the last run) or manually entered ones. There's a flag to show if it's a manual parameter or not.
In your case I would have the start and end date parameters and the manual flag as 0. Fine and dandy, it all runs.
When I need to gap fill or do a special run I use a stored procedure to manually update the necessary parameters which sets the manual flag to 1.
This way the DBAs don't have to mess around if I need a special run. I set the parameters I need and ask them to run the job.
Of course there's additional steps to make sure the parameter isn't overwritten in the beginning and to flip the flag after the data has been extracted.
I think I covered that in a coherent manner.
June 14, 2016 at 12:39 pm
Thanks JustMarie
So this is sounding like a two step process. Have a sproc update the start and end dates then call the SSIS package to run with the updated dates. Question - how are you linking package to specific row in parameter table?
I imagine a filter field that the packages uses to retrieve its "own" start and end dates no?
June 14, 2016 at 12:41 pm
One parameter to imply default or override behavior, and two date parameters where you can manually edit your date range. Since you are using package parameters,
I like the idea but worry about managing a new package which solely controls dates.
June 20, 2016 at 8:35 am
Yes it is 2 packages. My process is done in bite size steps. I could have probably combined them in this case since I put in a constraint with an expression and the way my company runs SSIS doesn't allow for 'run from step X' things.
As long as you put in the step to determine if it's a manually entered value or not then you could put it all in the extract package. One container to handle the parameters and then on to the rest of the extract.
I link things using an Event_Name column. Then I have variables that handle the various names of what I need.
My ETL is very tightly integrated in that there are scoped variables all over the place to contain table names. Those table names are then used to keep things together. So I don't have to worry much about what parameter is getting pulled.
Here's the DDL for my table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Parameter_Value](
[Parameter_ID] [int] IDENTITY(1,1) NOT NULL,
[Process_Name] [varchar](128) NOT NULL,
[Event_Name] [varchar](128) NOT NULL,
[Event_Value] [datetime] NULL,
[Extended_Value] [varchar](128) NULL,
[Parameter_Date] [datetime] NOT NULL,
[Manual_Entry] [bit] NOT NULL,
CONSTRAINT [PK_Parameter_Value] PRIMARY KEY CLUSTERED
(
[Parameter_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Parameter_Value] ADD CONSTRAINT [DF_Parameter_Date] DEFAULT (getdate()) FOR [Parameter_Date]
GO
ALTER TABLE [dbo].[Parameter_Value] ADD CONSTRAINT [DF_Parameter_Value_Manual_Entry] DEFAULT ((0)) FOR [Manual_Entry]
GO
A couple of things - there's two potential columns for parameter values because doing text conversions on datetimes causes rounding and then it screws up using the parameter again. Having two columns means I can use this table for all kinds of things and I do.
One final thing. Forget about updating parameters. Do a delete and insert. That way you don't have to worry about whether or not the parameter exists when you're running your code. Just make sure your initial manual check can handle not finding a matching parameter value.
June 22, 2016 at 4:25 pm
Awesome!! MY team and I will review this concept. We are planning something similar and I like how you have this done. Ill reach out if I have any other questions.
Thanks again.
August 1, 2016 at 2:16 pm
Been a while I know but I just wanted to give feedback. I essentially created a table (SSIS_Mapping) with various columns one being the infamous auto_flag INT column.
All the packages have a name and entry in this table and based on the auto_flag will know what start and end dates to run with. The concept is ingenious. Thank you.
I came back to this post because my team of in training developers are having a somewhat challenging time having to write update statements every time they require a manual run.
I am about to write a sproc where the name of their load will be passed in as a parameter as well as the start and end dates they need run and the sproc will handle the updates to the table. Passing a zero as a manual run is easily achievable with the EVs.
Thank you so much.
Have a good one.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply