01 Jan

Sticky: The SSIS Section Disclaimer

I wanted to make sure that anyone reading this section understands what it is.  This is my study process for SSIS.  I take a topic, write about it, and learn it through that process.   These posts are written very fast and cover a lot of ground in a very short time frame (about 5 of these a day).  In many ways, this is like writing a book at an incredibly fast pace, without an editor.  I guarantee that there are errors and grammar issues because there simply isn’t enough time to keep up my pace.

Finally, this is test preparation, so I’m not attempting to delve into every detail of a topic.  If it helps you, great, I hope it does, but, if it’s not as deep as you’d like, there are other options available.

Note: Test passed on 03/19.  However, the backlog of content will run through 04/27 which was the last post (SSIS: Package Deployment) that I wrote as test preparation.  I’m now non-disclosed so I won’t be writing posts that say things like “I think this will be on the test” going forward.  Nor, will I say if any of it was, or wasn’t, on the test.

27 Apr

SSIS: Package Deployment

These are the steps for deploying a package from development to a production server.

Step 1: Create a configuration file.

These are actually created at run time and as such are optional.  It’s here because Microsoft has in here.

Step 2: Create a deployment utility.

A deployment utility is a folder that contains the files that you will need to run the package.

The following steps create the utility:

  1. Make sure that the project has been converted to the package deployment model.
  2. Right-click on the package and select properties.
  3. Select the Deployment tab.
  4. Set AllowConfigurationChanges to True.
  5. Set CreateDeploymentUtility to True.
  6. Click OK to close.
  7. Right-click the project and select Build.


Step 3: Copy packages to the target computer.

Exactly what it sounds like.

Step 4: Run the “Package Installation Wizard” on the target computer.

Perform the following steps:

  1. Open the folder.
  2. Double-click the manifest file (.SSISDeploymentManifest) to start the process.
  3. Follow the steps.

Yeah, this is kind of light on detail.


The main MSDN for package deployment:


26 Apr

SSIS: View Code

I’d completely forgotten you could right-click on a package, click “View Code” and change the package’s XML configuration.  But, you can, and we will.

I’m going to create a very simple package.

Step 1:  Create a new project and/or a new package.

You know how to do this so do it and name them whatever you would like.

Step 2: Create the Data Flow task.

  1. Create a Data Flow task (drag and drop or click the Data Flow tab).
  2. Drag a OLE DB Source to the Data Flow.
  3. Configure the connection manager to point to AdventureWorks2012, set “Data access mode” to “Table or view” and pick a table.  I used [Production].[Location].
  4. Drag a Flat File destination to the Data Flow and connect the OLE DB source to it.
  5. Configure it to point to a file of your choosing.  I used C:\testData\junk.txt.  Make sure this is a new file, and not an existing file.  It may work but I didn’t test if it did.
View Code.

View Code.

Step 3: Use “View Code” to change the package.

In the Solution explorer, right-click the package, select “View Code” and do the following:

  1. Hit CTL-f to bring up the “Find and Replace” dialogue window.
  2. I searched for junk.txt but if you named your file something else search for that.
  3. Rename the file.  In my case, I changed junk.txt to notMyJunk.txt.

If you run the package you will see that the process ran, and it renamed the destination file to notMyJunk.txt.

Anyways, one more way to configure a SSIS package and I sincerely hope it’s the last one I write about.

25 Apr




The parameterize option, is found by right-clicking a Control Flow task or a connection manager, allows you to create a parameter associated with a property.  For example, you can tie a connectionString property to a parameter.  When an object has a property that has been parameterized it announces it with a “fx” icon either in it or nearby it.

To parameterize an object simply right-click it, and select Paramaterize.  The Parameterize window opens and you will have the following options:

  • Property: The property you want to associate with the parameter.
  • Do not use parameter: Removes the reference to a parameter.  The paramater is not deleted.
  • Use existing parameter: Associates the property with an existing parameter.
  • Create new parameter: Creates a new parameter.
  • Scope: A package or project.
  • Sensitive: Sensitive parameters are encrypted in the catalog and appear as a NULL value in SSMS or T-SQL.
  • Required: Specifies that the parameter must have a value.

Here are some parameterized properties for a connection manager set at the project level.

Parameterized Connection Manager.

Parameterized Connection Manager.

24 Apr

SSISDB: The Right-Click

rightI just wanted to touch on these very quickly.  I’d completely ignored them, and, of course, got tagged by the measureUp practice test.  This will be a quick overview of the options that you may, or at least I had, forgotten.

Active Operations

Actively running operations such as deployment, validation, and package execution.



Allows you to run reports.  There are two-kinds, default and custom.  The default reports are:

  • Integration Services Dashboard
  • All Executions
  • All Validations
  • All Operations
  • All Connections

I would be stunned if this is asked on the test, but, who knows.


  • Clean Logs Periodically:  If it’s set to True logs that are older than the retention period are deleted.
  • Retention Period (days):  If the “Clean Logs Periodically” property is True, this is the number of days that logs are saved.
  • Server-wide Default Logging Level: Details that are logged.
    • None: No logging.
    • Basic: All SSIS event types except OnProgress and OnCustomEvent are captured.
    • Performance: OnError events and component phases are captured.
    • Verbose: All SSIS events.
  • Maximum Number Of Version per Project: Number of versions per project that are retained.
  • Periodically Remove old Versions: Removes versions when the total number exceeds “Maximum Number Of Version per Project”.
23 Apr

SSIS Toolbox: Sort – Remove rows with duplicate sort values

This is something I missed in the Sort transformation so I thought I’d make sure I nailed it.

At the bottom-left of the Sort transformation is a check box that reads “Remove rows with duplicate sort values”.  Because it behaves in a way that could be slightly unexpected I thought I’d write a post.

Here’s a table and some data.

create table junk
id int,
myval char(1));

SELECT id, myval
FROM junk;
id	myval
1	A
1	B
1	C
2	A
2	A
3	C

Now, I’ll run some queries and set up the Sort to match them.

FROM junk;

id	myval
1	A
1	B
1	C
2	A
3	C

This matches this Sort transformation configuration.



However, the Sort transformation can do something a bit weird.  If the configuration is the following:



You’ll wind up with this:

id	myval
1	C
2	A
3	C

And that is probably not what you want.  The Sort transformation will run a DISTINCT on an individual column and apply that across the entire result set.

Anyways, that’s really all I have to say on this. It’s just something I missed going through the study process and it’s exactly the kind of thing they might ask.

22 Apr


There are two kinds of spurs, my friend. Those that come in by the door; those that come in by the window.
Tuco – The Good, the Bad, and the Ugly (1966)

Once again, something that popped up in the MeasureUp practice test, that I missed, and so, a post.

The MaxConcurrentExecutables is set by right-clicking the Control Flow and selecting properties.  The following are valid values for this property:

  • -1: Sets the maximum numbers of concurrently running executables to the number of processors, plus 2.
  • 1 or greater: The number of threads that a package can execute.

Any value of 0 or less, except -1, will result in an error.

What I missed wasn’t that it set the number of threads, rather, that -1 is equal to the number of processors plus 2.


21 Apr



DTEXEC is a command line utility that allows you to configure and execute SISS packages.  It provides access to all package and execution options.

You can load packages from the following sources:

  • Integration Services server
  • .ispac project file
  • SQL Server database
  • SSIS Package Store
  • File system

You can use DTEXEC to run a package created in earlier versions, which are temporarily upgraded, but you cannot permanently save those packages.

64-bit vs 32-bit

On a 64-bit computer, the 64-bit version of DTEXEC is installed.  If you want to use the 32-bit version you will need to install it.

When both are installed SSIS will attempt to run 32-bit version because the directory appears in the PATH variable.  SQL Agent, however, will run the 64-bit version.

You can run DTEXEC from the xp_cmdshell

/SET allows you to set package variables and properties.

/isserver sets the package path.



Allows you to run SSIS packages in a graphical interface.

You can run packages in one of the following locations

  • SQL Server
  • SSIS Package Store
  • File system

It is a 32-bit utility.

There are so many things in this tool that I’m not even going to try to list them.  I hope, that the two things I’ve listed above are enough.



This utility is used to copy, move, delete or verify a package in any of the following locations:

  • SQL Server
  • SSIS Package Store
  • File system

On a 64-bit computer, the 64-bit version of DTUTIL is insntalled.  If you need the 32-bit version you will need to install it.  By default, if both are installed, the 32-bit version will run.


The following table might help for comparison purposes as well.

Overview:Allows you to configure and execute packages.A graphical UI for running packages.Allows you to copy, move, delete or verify a package.
Runs from:Command prompt.Command prompt or SSMS.Command prompt
Usable for packages store in:Integration Services server
.ispac project file
SQL Server database
SSIS Package Store
File system
SQL Server
SSIS Package Store
File system
SQL Server
SSIS Package Store
File system
32-bit vs 64-bitOn a 64-bit computer, the 64-bit version is installed. If both are installed, then the 32-bit will be the default. SQL Agent will run the 64-bit version.32-bit.On a 64-bit computer, the 64-bit version is installed. If both are installed, then the 32-bit will be the default.


20 Apr

Master Data Services: Security

Because, if the model doesn’t confuse you, the security model surely will.

Model Administrators

These have update permissions on a specific model, or models.   They only have access to areas they are assigned to.  There can be multiple model administrators.

System Administrator

They have update permissions to all models and they can add, update or delete them and there is only a single System Administrator account.

You cannot change the system administrator account using MDS.  You must use SSMS and T-SQL.   The following link explains the process: http://msdn.microsoft.com/en-us/library/ff487048.aspx.


Users and Groups

In order to get access to MDS a user must have either a Windows domain account or an account on the server where MDS is installed.

To give a user access to MDS you either ad their account  to the list of users in MDS or add them to a group that is in the list of groups in MDS.

To give a user, or group,  the ability to work in the explorer they must be given access to the explorer and model objects.

Functional Area Permissions

There are 5 functional areas:

  • Explorer
  • Version Management
  • Integration Management
  • System Administration
  • User and Group Permissions

Within the Explorer, permissions assigned to models and hierarchical objects, determine access.  In the other areas a user must be a model administrator to view and work on a model.


Object Permissions

  • Model: Apply to all entities, and collections within the model.  Permissions assigned to a model can be overridden by object permissions.
  • Entity: Apply to the entity’s attributes, collections and explicit hierarchies.
  • Leaf: Apply to all attribute values for leaf members.
  • Consolidated: Apply to all attribute values for consolidated members of an entity.
  • Collections: Apply to all collections for an entity.

Permissions can be broken down into 3 groups:

  • Read-only: Can’t add, remove or update.
  • Update: Can add, remove and update.
  • Deny:  Can do nothing.

There is so variance amongst the different objects but I’m going to take my chances on those.

It is considered a best practice to grant the update permission to the model object and then explicitly permissions beneath it.  If you do not apply these permissions then the objects inherit permissions.


Navigational Access

This was, to me, a confusing concept.  In essence, what they are doing here is saying, if you get access at a given level, you also get access to some things further up the tree.  These work as follows:

  • Entities: You can read or update the code and name for all members, and read the model name.
  • Attributes: You can read or update the code and name for all members in the entity, and read the model name.
  • Collections: You can read or update the name, code, description and ownerID.  You can also read the model name.


Hierarchy Permissions

Microsoft say’s they’re optional.  I’m going with that.  Here’s a link.


Overlapping User And Group Permissions

  • Deny overrides all other permissions.
  • Update overrides Read-only.

Overlapping Model And Member Permissions

  • Deny overrides all other permissions.
  • Read-only overrides Update.

Here’s to hoping that’s most, if not everything, and that I didn’t blow anything too terribly.

19 Apr

Master Data Services: The Stuff

Don’t worry, I love that title too.  The truth is I don’t really know how to name this section.  I find MDS confusing.  Maybe not the product, so much, OK, yes I admit it, the product too, but so many of the explanations I’ve read have left me scratching the side of my head.  Since I know there will be questions on this, and the test is so broad, I’m going to try to pile a bit more into my brain.

Here goes.


This is the top, or highest, level in MDS.  In a sense, they are the container for everything else, except, by everything else, I only mean the area that you are trying to model.

Examples include:

  • Customers
  • Products
  • Salespeople



Entities are groupings in the model.  For example, at Higgins Lumber we had a department > class > fineline (D-C-F) categorization to our products.  These would all be an entity.  At Higgins, the dept group was at the top, while the class and fineline categories were beneath it, and the fineline group was a further refinement of the class group.

Examples include:

  • Customer categories
  • Product categories

I’ve read an entity description that compared an entity to a table.  Look, despite all the writing I’m doing, I’m not claiming to be an expert, so maybe I’m wrong on this, but I wouldn’t compare an entity to a table.  It really looks to me more like a group.  The reason I see it that way is that you can have entities that are subcategories of other categories.  On the other hand, Microsoft proceeds to draw up a diagram in its data that is pretty much exactly like a table.  And you could certainly model MDS to match your table structure in this way.

I guess another way of putting it is, entities are flexible, so you can be flexible when modeling.

Entities can also be constrained.  For instance, at Higgins you could only assign a SKU to a predefined list of departments.  You can do the same thing with entities.

An entity can also be defined as a base-entity, or a starting point for navigation in the model.



Attributes define the members of an entity.  Here, I do think it’s fair to think of them, loosely, as a column in a database.  If you have a model named products, and an entity named flooring, then your attributes would be things like width, thickness, species, etc.

All attributes must have a Name and Code value.  The code value must be unique.

There are 3 types of attributes:

  • Free-form: similar to a text box on a web form.
  • Domain-based: You populate them based on another entity.  An easier way of describing this is as referential integrity.
  • File Attributes: Can be used to store files, documents or images.

Numeric free-form attributes use the double data type so beware the floating point number.


 Attribute Groups

Attributes within an entity can be grouped.  This is  to make attributes easier to view and manage in MDS.  Specifically, attribute groups do the following:

  • Attribute groups always include the Name and Code attributes.
  • Each attribute for an entity can belong to one or more attribute groups.
  • All attributes are automatically included on the All Attributes tab in Explorer.
  • There is no way to hide the All Attributes tab.



Well, here we go, it’s time to confuse the issue, especially if you had a table structure in your head.

Actually, this is the single most clarifying thing I’ve read on MDS.

You can think of members as rows in a table. Related members are contained in an entity, and each member is defined by attribute values.

I bet you wish I’d written that first.  I know I wish I had read it first.  So, lets repeat, because it’s important,

You can think of members as rows in a table. Related members are contained in an entity, and each member is defined by attribute values.

My guess is that if all you do is remember that you are better off than everything else I’ve read.

There are three types of members:

  • Leaf: These are the default members of an entity.  In a derived hierarchy (hierarchies coming soon), they are the only type of member.  In an explicit hierarchy they are the lowest member and cannot have children.
  • Consolidated: Used when explicit hierarchies and collections are enabled.  It’s a parent node for an explicit hierarchy.
  • Collection: A group of leaf and consolidated members of an entity.  They are used to group members for reporting purposes.

Per the MSDN, use leaf members when you are importing data but not using staging tables or the Add-In for Excel.


Explicit Hierarchy

An explicit hierarchy is,

In Master Data Services, an explicit hierarchy organizes members from a single entity in any way you specify.

There are two types:

  • Mandatory Explicit: All leaf members are included in the hierarchy tree.
  • Non-mandatory Explicit: You do not have to include all members in the hierarchy tree.

Explicit hierarchies have the following rules:

Explicit hierarchies have the following rules:

  • Each leaf member can be included in the hierarchy only once.
  • All consolidated members must be included in a hierarchy.
  • Consolidated members cannot be in more than one explicit hierarchy.
  • Consolidated members in the hierarchy tree do not have to contain leaf members underneath them.
  • If you delete an explicit hierarchy, all consolidated members that were used in the hierarchy are deleted.
  • If you delete a consolidated member that was in an explicit hierarchy, all leaf members that were grouped by that consolidated member are moved to the root.


Derived Hierarchy

A derived hierarchy is,

A Master Data Services derived hierarchy is derived from the domain-based attribute relationships that already exist between entities in a model.

In a derived hierarchy, leaf members from one entity are used to group another entity.


That’s a lot.  Even writing it, some of it didn’t sink all the way in.  Especially the part about hierarchies.

18 Apr

SSIS Parameters And Variables

This is another of those, get my head clear, overview posts.  There’s really nothing here that probably isn’t clear with a bit of study.  The only hang up I had, and really why I wrote this, is that values that can be assigned to a parameter came up in the practice test.


Can be assigned at either the project or package level.  They are used to assign values at run-time.  They can also be used in scripts and precedence constraints.

There are 3 types of values that can be assigned to a parameter:

  • Execution Value: Is assigned to a specific instance of an execution value.  It only applies to that specific execution.  It overrides all other values.
  • Server Value: This is the value assigned within the scope of the project.  It overrides the design value.
  • Design Value: This is the value assigned when the project is created.

A single parameter can assign values to multiple package properties.


Variables store values that a package, container, task or event handler can use at run time.  They can also be used in scripts and precedence constraints.

There are two-types of variables:

  • System: Defined by integration services.
  • User: Defined by developers.

Variables are used in other ways as well.

  • Set package properties at run time.
  • To store data values.  As an example, a Execute SQL Task can store its results in a variable.
  • As a condition in a package.
  • As a condition in a container, for instance, a For Loop task.
  • As an expression.

And, from the 10,000 foot level, that’s pretty much that.