25 Apr

Parameterize

Parameterized!

Parameterized!

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.

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.

Parameters

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

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.

05 Apr

SSIS: Parameters With Expressions (it got messy)

I wanted to get a better grip on Parameters so I decided to set up a simple example using parameters to configure a File System Task.  My thinking was that it would be a clean, and simple, process that would lead to a happy place and a couple of really easy examples.  But, no chance of that.

So, here was the plan, set up 8 parameters for a File System Task and run it in a couple of different places.  The process would consist of

  1. Creating a file and folders for the demo.
  2. Configuring parameters.
  3. Using expressions to map the parameters to the task.
  4. Run the task.
  5. Call it a day.

I’m skipping the create a package / project instructions.  I’m going to assume that you know how to do that, by now.

Step 1: Create the files to move.

This is what I did, you are welcome to do whatever else you like.  I created the following 2 folders and a file.

  • C:\testData
  • C:\testData\backup
  • c:\testData\junk.txt

This went just fine.

Step 2: Create parameters.

My first attempt looked like the following.  We’ll be revisiting this screen, soon.

parameters1

 

 

 

Step 3: Map the parameters to the File System Task using the expression editor.

This looked like the image below.

expressions

 

 

 

 

 

 

 

 

Step 4: Run the package.

And the fun began.

error1

Error 1

Error 1

The first error is actually two errors.  Because I had set the initial IsDestinationPathVariable, and, IsSourcePathVariable parameters to True, the package is actually looking for variables, not parameters.  My first attempt at fixing this was to set these values to False, however, that threw an error because it was looking for a connection, and, c:\testData\backup is not a connection.

The solution to this involved 3 things.

First, I set the IsDestinationPathVariable and the IsSourcePathVariable set to true.

Next, I create two string variables, varSource and varPath, which I configured as follows:

variables

 

Then, I configured the parameters to point to these variables, which meant that my parameters tab looked like this.

parameters2

 

 

 

Life was good, it was time to run the package, and move on.

And then this happened.

Error 2

Error 2

Error: The result of the expression “@[$Package::Operation]” on property “\Package\My Move Demo.Properties[Operation]” cannot be written to the property. The expression was evaluated, but cannot be set on the property.

This error didn’t help, much, but the word Operation got me to look in the expressions and click on the expression I was using to set the operation property.  It turns out that this property is an Int32, not a string.  That’s actually logical as Select boxes contain a selection that is presented to you and then sets a value for your selection that is almost always different than the description that you based your selection on.

So, I reset the parameter to an int32, looked at the list, saw that Move File was seventh on the list, and set it to 7.  Nope, that is “Move Directory”.  Ah, maybe it starts at 0?  So, I set it to 6.  Nope, that’s “Copy Directory”.  Finally, I just started rolling through them until I discovered that “Move File” is 1.

And happy days.

This is my working parameter tab.

parameter3

 

 

 

 

 

And the File System Task

filesystem

 

 

 

 

 

 

 

And that is a working set of parameters, tied into a task using expressions, that includes linking 2 parameters to  variables. I had planned to do more with this, and I will, but this post is plenty long. If by some crazy chance you work through these, then you should save this as a package. We’ll be visiting it in my next post.