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.

29 Mar

SAS: Variables And Expressions

This will be really basic, because I’m still at a basic level in the process but I need to hit variables and expressions.  First, a data source:

data mydata;
	infile '....';
	input visitingTeam $ 1-20 concessionSales 21-24 bleacherSales 25-28
	ourHits 29-31 theirHits 32-34 ourRuns 35-37 theirRuns 38-40;
run;
ods listing;

proc print data=mydata;
run;
                      concession bleacher  our    their  our   their
Obs       visitingTeam   Sales   Sales    Hits    Hits   Runs   Runs
1     Columbia Peaches     35      67       1      10     2      1
2     Plains Peanuts      210       .       2       5     0      2 
3     Gilroy Garlics       15    1035      12      11     7      6  
4     Sacramento Tomatoes 124      85      15       4     9      1

Now, lets add two variables, totalHits and totalRuns. You add them by modifying the data statement as follows:

data mydata;
	infile '....';
	input visitingTeam $ 1-20 concessionSales 21-24 bleacherSales 25-28
	ourHits 29-31 theirHits 32-34 ourRuns 35-37 theirRuns 38-40;
	totalSales = concessionSales + bleacherSales;
	totalHits = ourHits + theirHits;
	totalRuns = ourRuns + theirRuns;
run;
                      concession bleacher  our    their  our   their     total     total    total
Obs       visitingTeam   Sales   Sales    Hits    Hits   Runs   Runs     Sales      Hits    Runs
1     Columbia Peaches     35      67       1      10     2      1        102        11      3 
2     Plains Peanuts      210       .       2       5     0      2          .         7      2 
3     Gilroy Garlics       15    1035      12      11     7      6       1050        23     13 
4     Sacramento Tomatoes 124      85      15       4     9      1        209        19     10

The variables that we created have been added to the report. They should all make sense except for the totalSales value where it is a period. The reason for that is that in the raw data there is a . which is the equivalent of null in the SAS world and since you can’t add a null to anything else and produce anything but a null, well, if you add a period to anytihng else you still wind up with a period.

You can also assign a variable as a constant. For instance,

myVar = 'Something went thataway';

You can also perform calculations using variables. For instance,

myVar = 60;
hits60 = ourHits * myVar;

Expressions, absent parenthesis, are processed in the following order:

negative prefix and exponentiation
multiplication and division
addition and subtraction

This is the kind of thing that is always asked on tests.  You’ll need to know it.

Finally, date constants. This is another thing that I think SAS pulled out of their behinds, but, if you want to assign a date constant you do it as follows:

  • “ddmmmyy”d
  • ‘ddmmmyyyy’d

Single or double quotes are fine.

dd = 1 or 2 digit day number.
mmm = 3 character month.  Such as Jun, Jul, Aug, etc.
yy or yyyy = 2 or 4 digit year.

For example:

myVar = "14Jul1982"d

This is really basic. Keep that in mind if you are thinking of putting the match to the flamethrower if you are so inclined.