So you’re going to SQLRally in Orlando next week? Want to take advantage of ALL of the awesome opportunities presented? In addition to all of the amazing training content you’ll see at the event itself, we’re holding some great stuff after-hours on Thursday. We’re calling this !
These are three different panel events being hosted by members of the SQL community. You have your choice of three cool events to choose from:
Welcome back, class! In our last class, we setup a parent-child package configuration and showed how you can pass variables between the two in order to complete a unit of work. In today鈥檚 class we鈥檙e going to continue exploring that data load package and take a look at another critical piece of SSIS that you should know about: precedence constraints.
So what exactly are precedence constraints? They are the connectors that link together tasks in the Control Flow, and they define the workflow of your package. When two tasks are tied together with a constraint, the destination task will execute based on two things: The final state of the task that precedes it and special rules you can define on the constraint using specialized expressions.
You can have different types of constraints between tasks. You can read more about constraints in detail from MSDN article (). I鈥檒l briefly cover each of the constraint types in an abbreviated list and then we鈥檒l discuss how we used these constraints in our parent-child package from our previous lesson.
Note: In these screenshots there are labels next to the precedence constraints indicating the type of constraint chosen. This is not a default behavior. To enable this click on Tools menu, go to Options. Under Business Intelligence Designers, go to Integration Services Designers and under the Accessibility section in the General menu, check the box for 鈥楽how precedence constraint labels鈥�. This is helpful for folks who are color blind and are not able to differentiate between green/red/blue lines in designer. Big thanks to .
Now that we鈥檝e seen the different constraint types, let鈥檚 examine how they鈥檙e used in conjunction with parent-child package setup. Our first use of constraints comes at the top of the child package from the script task to the sequence containers. We鈥檝e used an empty script task as an 鈥渁nchor鈥� task. This is used as a starting point to continue on to the corresponding workflow. As we learned in our last class, we have a variable being passed from our parent package with the value of the name of the file we are currently processing.
In this first example, we鈥檙e going to look at the constraint leading to the sequence containers for the Supplier table. We鈥檝e used and Expression and Constraint here and chosen the value for Success. We鈥檙e also looking at the value of the variable being passed to the child package. For this particular workflow, we are waiting until the value of the variable 鈥楶arent_TblName鈥� is set to 鈥渟upplier鈥�. Once both of these situations evaluate as being true, we will execute this container.
Now that we鈥檙e inside our sequence container, we have another set of constraints. Once again we鈥檙e using an empty script task as an 鈥渁nchor鈥� for our precedence constraints. This time we鈥檝e got two possible paths to go down. The first is to execute an Execute SQL task. This task checks for the existence of the table (in this case, the supplier table). If it exists it will drop the table and then recreate it. The other path leads directly to a data flow task which simply loads the table specified from the flat file.
I鈥檝e created another variable on this package called 鈥楢ppendFlag鈥� which is a boolean value. The purpose of this flag is so that you can choose to load the tables with a fresh load from the flat file (the Execute SQL task path) or you can simply append an already existing table鈥檚 data with data (data flow path). The default value of the variable is false.
The first path to the Execute SQL task uses an expression and constraint which is looking at the value of the 鈥楢ppendFlag鈥� variable. In order for us to go down this workflow both value of 鈥楢ppendFlag鈥� must be false AND the previous component executed successfully. The other path from the script task leads directly to the data flow task which actually loads the table. For this path, I鈥檝e set the precedence constraint to look for the value of 鈥楢ppendFlag鈥� to be true. In this path, however, we鈥檝e chosen to use a logical OR. The reason for this being that the Execute SQL task, once complete, also leads to the data flow task. Due to the data flow having two different input paths, we must use the logical OR (if you try to choose logical AND, BIDS will quickly yell at you).
When we bring it all together, we now have a parent-child package that passes variable values. These values are used to execute specific workflows based on the value of the variable passed. Precedence constraints are an extremely helpful and invaluable tool in your SSIS toolkit. Using precedence constraints can help you create very dynamic workflows within your packages.
This post is based on an interesting question/situation that was . The user asked how to create a policy condition that enforces local sql accounts to adhere to password expiration policies.
First off, to create the condition itself is relatively easy. Below I鈥檝e provided the T-SQL code so that you can create the condition that way. I鈥檝e also included a quick list on how to create it via SSMS GUI.
T-SQL method:
Declare @condition_id int EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Password Policy Enforced', @description=N'', @facet=N'Login', @expression=N'<Operator> <TypeClass>Bool</TypeClass> <OpType>AND</OpType> <Count>2</Count> <Operator> <TypeClass>Bool</TypeClass> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <TypeClass>Bool</TypeClass> <Name>PasswordExpirationEnabled</Name> </Attribute> <Function> <TypeClass>Bool</TypeClass> <FunctionType>True</FunctionType> <ReturnType>Bool</ReturnType> <Count>0</Count> </Function> </Operator> <Operator> <TypeClass>Bool</TypeClass> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <TypeClass>Bool</TypeClass> <Name>PasswordPolicyEnforced</Name> </Attribute> <Function> <TypeClass>Bool</TypeClass> <FunctionType>True</FunctionType> <ReturnType>Bool</ReturnType> <Count>0</Count> </Function> </Operator> </Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT Select @condition_id GO
SSMS method:
Now we鈥檙e left with another question: Where does this password policy come from? For details on that you can refer to the Books Online article about it (). If your box is on a domain that has Active Directory policies regarding password expiration, when you select the box for 鈥楨nforce password policy鈥� as well as 鈥楨nforce password expiration鈥�, these settings will come from that policy. Don鈥檛 have an Active Directory policy? No problem! If a policy isn鈥檛 provided from Active Directory, Windows then looks to its local security policies for these values.
To see the local values, click on your Start button, then type in 鈥榮ecpol.msc鈥� (don鈥檛 type type the single-quotes). This will open up the Local Security Policy MMC Snap-in. Expand the Account Policies folder and then click on the Password Policy folder. In the right side pane you will see the various password-related options you can set such as Maximum password age or password length.
While policy-based management can help you check whether or not the accounts have the option enabled to enforce the policy checks, Policy-based management itself has not bearing on the Local Security Policy settings. This is something you, as an administrator, will have to set and configure outside of SQL Server.
Welcome to the second week of SSIS this semester at SQL University. Today we’re going to talk about the relationship between children and parents. Ever had communication issues with your kids when you ask them to complete聽a chore? When they’re done, wouldn’t it be nice if they always came back and let you know they took what you said, applied it, and completed the job? What does that have to do with SSIS? Read on and find out!
Do you have a data warehouse initiative in your current organization and looking for a way to learn how to properly build and support it? Would you like to learn how to do this straight from the Masters of the BIverse themselves? Well you’re in luck! Next month at SQLRally there’s going to be a great pre-conference session held by the following:
Mike Davis (| )
Devin Knight (| )
Adam Jorgensen (| )
Patrick LeBlanc (| )
In this full-day workshop, you鈥檒l learn from the author team of Mike Davis, Adam Jorgensen, Devin Knight, and Patrick LeBlanc how to build a data warehouse for your company and support it with the Microsoft business intelligence platform. We鈥檒l start with how to design and data model a data warehouse including the system preparation. Then, we鈥檒l jump into loading a data warehouse with SSIS. After SSIS, you鈥檙e ready to roll the data up and provide the slice and dice reporting with SSAS. The team will walk through cube development and data enrichment with things like key performance indicators, which are essential for your future dashboards.聽 Lastly, we will cover how to report against the data warehouse with SSRS, including a primer in how to write MDX queries against the SSAS cube.
What you can expect to take away from this session:
You can and pre-con fee is $199 (which includes lunch). This is a great deal so what are you waiting for? Sign up today as slots are filling up fast! See you at SQLRally!
This is just a quick post in regards to a conversation I just had via Twitter. If you don’t already use Twitter, the SQL Community has setup a great resource on there using .
Today a conversation came up due to a forum question over at SQLServerCentral regarding . If you’re not familiar with the term, a heap is a table that has no clustered index on it. This can be problematic from a performance stand point so it might benefit you to find a way to identify these potential problem children. Enter Policy-Based Management.
This is a simple policy that you can run against your servers and it will identify your tables that are heaps. Just to clarify this policy identifies if your table has a clustered index on it. If it doesn’t then it will fail policy check. I’ve provided two ways to get the policy.
OR (Updated 4/15/11 to include creation script for condition)
--CREATE CONDITION
Declare @condition_id intEXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Find heaps', @description=N'', @facet=N'Table', @expression=N'<Operator>聽 <TypeClass>Bool</TypeClass>聽 <OpType>EQ</OpType>聽 <Count>2</Count>聽 <Attribute>聽 聽 <TypeClass>Bool</TypeClass>聽 聽 <Name>HasClusteredIndex</Name>聽 </Attribute>聽 <Function>聽 聽 <TypeClass>Bool</TypeClass>聽 聽 <FunctionType>True</FunctionType>聽 聽 <ReturnType>Bool</ReturnType>聽 聽 <Count>0</Count>聽 </Function></Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUTSelect @condition_id GO --CREATE POLICY Declare @object_set_id int EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Find Table Heaps_ObjectSet', @facet=N'Table', @object_set_id=@object_set_id OUTPUT Select @object_set_id Declare @target_set_id int EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Find Table Heaps_ObjectSet', @type_skeleton=N'Server/Database/Table', @type=N'TABLE', @enabled=True, @target_set_id=@target_set_id OUTPUT Select @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Table', @level_name=N'Table', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 GO Declare @policy_id int EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Find Table Heaps', @condition_name=N'Find heaps', @policy_category=N'', @description=N'Heaps are tables without clustered indexes. Read the link below to learn more about heaps.', @help_text=N'Fragmentation (part 4):what are heaps? by Paul Randal', @help_link=N'http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/09/19/761437.aspx', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'Find Table Heaps_ObjectSet' Select @policy_id GO
Looks like time for another meme, this time from Tom LaRock ( | Twitter). He challenged us to write a blog post in 11 words or less, so here’s my entry:
With a hammer, everything looks like a nail. Use tools appropriately.
If you’re reading this then I’m tagging you! If you don’t blog then add you 11 word post in comments below!
This week SQL University is on but we’ve lined up some activities to help keep students busy (you know what they say about idle hands and whatnot!). In continuation of Women’s History Month, and properly coming off of the heels of the 24 Hours of PASS event, we’re proud to have our next talks in our Lecture Series this Friday at 1pm EST featuring the ladies of WIT Week here at SQL University.
Our Live Lecture will be happening over at SQLLunch, and as always it’s free, so make sure you go . If you enjoyed reading all of the fantastic posts from WIT week you’ll love this event. The session is going to be a round table discussion about what WIT means to them as well as discussing some of the issues they’ve faced and would like to address in the field. Audience participation is encouraged via Q&A in LiveMeeting so come join the panel. See you at the !
Yesterday聽was the first day of PASS’ . For those not familiar, 24 Hours of PASS is an event that brings together 24 different presenters and they present on various topics on SQL Server ranging from performance tuning, internals聽to business intelligence and previews at vNext of SQL Server. This month’s event is quite special since March is Women’s History Month, PASS is celebrating it by having this event all delivered by women!
So far the event has been absolutely awesome and the awesomeness continues today with the last 12 hours of the event, starting at 8am EST. If you missed yesterday, don’t fret, all of the sessions are being recorded and will be available on the PASS website within a month. Yesterday’s sessions聽went well, we had some sessions that actually had over 750 attendees (or as Tom LaRock refers to it as, )! There were a few surprises as well, such as in Isabel de la Barra’s session, where we were treated to a presentation in Spanish (and translated by moderator Jesus Gil). At first we thought it was going to be a big issue but it turns out that over 300 attendees stuck around for the session and feedback from the Twitter stream seemed positive.
Speaking of Twitter, if you wish to follow along with the event you can do so by following the . We are also using #sqlpass as well as #passwit to help promote and discuss the event. Day one is in the book and day two is looking to be fantastic as well, see you in the sessions!
It’s March and this month sees a lot of celebration, amongst the parties are Mardi Gras and St. Patrick’s Day. This month we also celebrate women as it is as well! If you’ve spent any time in the SQL Community you may have noticed that we have an especially strong support for and so this month there are some great things happening to help celebrate that fact.
I was going to do a rundown of all the cool WIT stuff going on this month but it looks like Wendy Pastrick (| ) beat me to the punch! Check out , including next week’s SQL University WIT week which will feature our second Live Lecture Series via . We’re having the ladies of SQLU WIT week hosting a WIT panel so make sure to join us for that!
Update: Also, for those on Twitter, check out my WIT list:聽
And since the title of this post probably got the song in your head, as a bonus, here’s the video:聽