Powershell – Variables

I’ve just started to play around with variables in Powershell. I used them a little bit before this, but am in the process of figuring out basic commands around variables. First thing to note is that variables are not case-sensitive.  That means that a variable called “$MyVariable” is the same as “$MYVARIABLE” or “$mYvARIABLE”.  You can declare a variable and assign a value easily using something like:

$variable = value

That pretty much covers the majority of basic assignments. You can also use the Set-Variable command to not only set a variable, but change some options such as making a variable read-only or making it into a constant. Once a constant has been declared/set, you cannot delete it. It will be cleaned up when the Powershell session ends.  The only somewhat tricky point around variables is using special characters.  All variables should be declared within {}’s if you need to use special characters.
It’s easy to list all of the variables and their values using:

dir variable:
dir variable: | Format-Table Name, Value, Description -autosize

The first command lists all variables and their values. The second lists the name, value, and a Description property as well and then auto-sizes everything accordingly. Useful if you want to set other properties of your variables besides name and value. You can also use that latter command with a “-wrap” parameter to show the definitions of the system-level variables.


If you want to use Windows Environment variables, you’ll want to reference the “env:” virtual drive within Powershell. These are the variables such as your Path or Windows folder that are used within Windows. Any changes you make to these variables are only set within the scope of your Powershell session.


By default, variables stay within the scope of the function or session, but you can override this with $private to limit a variable to a scope, $local to use default scoping and enable variables to be read by sessions the current session creates, $script to allow the variable to work anywhere in the current script, and $global to allow the variable to be used anywhere, even external functions and scripts.





Variable Types and Attributes


Working with variables is easy with the defaults because they are weakly or loosely typed. If I assign a string to a variable, that variable contains a string. If I assign a bit value, the variable contains a Byte. That means that variables can change types easily as the script runs. However, that also means that if I’m expecting a variable to contain a date value and it somehow gets a string or floating point value, I could be very surprised.  Powershell allows for strong data typing of variables as well. If you define a variable in this manner:

[datetime]$myDate = "2009-12-25"

You will explicitly declare a variable of type DateTime. If you then attempt to set the variable to a string, you’ll get an error message. The variable types are standard .NET types with a handful of specific Powershell types.


You can set other properties using the Attributes for a variable and can even clear out the strong Typing using an Attributes.Clear() command against the variable. Attributes can be used to set constraints around a variable such as whether or not the variable can store $Null values, check constraints, or even RegEx patterns for the data stored in the variable.





This is by no means a definitely explanation of everything around variables in Powershell, but it’s a good start for most people. You can always run get-help About_Variables within Powershell for more details or get-help Set-Variable to read more about setting variables. I may update or post a follow-up if something else pertinent about variables comes up. I’d love to know a way to list out variable datatypes from within Powershell, but haven’t found a way yet.

Powershell – Modules and Profiles

Probably not technically correct, but pretty close. I’ve been trying to use SQLPSX for Powershell, recently updated to v2.0. For the longest time, I’ve tried various methods to import the modules and couldn’t get them to import successfully. I knew that this was likely due to lack of knowledge so spent a little time tracking down the root cause of my problems.
After quite a bit of searching, I finally figured out why I’ve been having such a hard time loading Powershell modules. By default, the Modules reside in
  %USERPROFILE%\Documents\WindowsPowershell\Modules
I verified that this was set within Powershell with quite a bit of different code snippets.  For some reason, this folder was never created in my Documents folder.  I manually created a folder called “WindowsPowershell” and another inside that called “Modules” and then extracted each of SQLPSX’s module folders into that newly created directory and I finally could use external modules. I restarted Powershell and used

import-module SQLServer

I got a warning that Powershell could not had to set my execution policy to allow remotely signed scripts. That’s not too hard to do, but I couldn’t import new modules without doing that. I ran

Set-ExecutionPolicy remotesigned

and set the default to “Y”. (This is a local test machine so I’m not as concerned about changing this setting.) I re-ran my import-module command and the modules finally imported.

Of course, now I’d like to automatically load these commands whenever I run Powershell, as well as customize the default Powershell environment to pull in the various SQL Powershell modules used in the SQLPS environment that ships with SQL Server 2008.  I found this MSDN article that discusses profiles and how to work with them. You can create a default profile easily with:

new-item -path $profile -itemtype file –force
notepad $profile

This will create a new profile for all users and all shells on that machine. The second command will open up that new file for editing. Place whatever commands you want to run on startup in this file. For example, this article discusses how you can get all of the behavior of the default SQLPS mini-shell. While not optimal, it looks like a great way to get the full power of Powershell w/ the new SQL functionality.

Getting Started with Powershell

I’m in the process of learning PowerShell. This seems to be the first scripting language that we can use at the server/desktop level that has some serious backing from Microsoft. I remember VBScript, CScript, Batch files, and similar, but it seemed that if I wanted to run a script, I had to find workarounds or plug-ins to do what I wanted inside of some other language and then mix and match those together.  Powershell seems to drive a lot of processes behind the scenes. That being said, here’s a quick summary of some important things I’m learning in the process.

1. Powershell can call executables directly, but needs an explicit path to those executables if they aren’t in the system path. This means that you may need to use .\MyProgram.exe instead of just MyProgram.exe.

2. get-help command  - I can see myself spending a lot of time with this to figure out syntax and uses for various commands. As I’ve played around with it tonight, I’m really impressed by how much information is available without needing to leave PowerShell. Reminds me a lot of the “man” pages in *IX, but with more examples and in a language I can understand.

3. get-command –verb verb – Another useful command to list all Powershell Cmdlets containing that verb.

4. Parameters can be abbreviated as long as there are enough characters to uniquely ID the parameter. An error message will be thrown if there aren’t enough. Probably better to let auto-complete handle the parameter name in that case to avoid possible ambiguity.

5. Common Parameters. There are apparently several common parameters that should be available for most Cmdlets.  I can see a lot of use for –ErrorVariable and –OutVariable. These are designed to capture error or output details, respectively.

6. Aliases. I can see these being both helpful and a little painful. Helpful in that a lot of familiar commands such as dir and ls are aliases, but painful in forgetting that I’ve set one and seeing some underlying Cmdlet change. I guess I have some qualms from my days of using a pretty customized BASH shell and forgetting a lot of the basics that were used to create that shell in the first place. Just running the get-help on Alias returned several screens of information. Example command to run to see CmdLet followed by its aliases:
   dir alias: | Group-Object definition

  And just as I figured, there’s a way to export the aliases and re-import them from a file later so you don’t need to define them each time you restart Powershell. Export-Alias and Import-Alias

7. Virtual Drives. PowerShell has a lot of different virtual drives set up to access Aliases, Registry settings, physical drives, and others. I’ll be exploring those more as I need them. I’m halfway assuming that one of these tied to a server would be related to AD structures, but that may just be a whole new set of Cmdlets.

 

I think this may be a good place to stop for the day. It seems the next section deals with functions which are reminding of DOS Batch file parameters at first glance. I hope that is not the case, but I’ll know more about that tomorrow. So far I’ve gotten a good handle on some of the basics and learned some ways to help myself during scripting. That’s a great start.

Learning to work around DB Pro

I’ve been trying to get a multi-database solution working correctly with MS Visual Studio Team System for Database Professionals (aka DBPro or DataDude). Anyone who has tried to tie multiple co-dependent databases together within DB Pro has experienced some of the pain points in working with these projects. Hopefully these notes can help you to some extent.

Pain Point #1: Circular References

This is the largest challenge that we face in our environment. DB Pro doesn’t really understand that DB A can reference DB B which can then reference DB A.  You can easily add a Database Reference to another project in your solution by right-clicking the “references” sub-folder and adding a new Database Reference.  However, once you’ve done that, you can’t add one from that project back to the original.  I understand you don’t want to get caught in an endless loop, but it would be nice to have some sort of Max Level of Recursion set that would stop the program from getting caught in that.

That being said, there is a way around this. You can generate a .DBSchema file for your existing database using the instructions from this site. Note that you may need to tweak the options a little bit. I think the “ModelType” parameter has been deprecated in GDR2.  Save that DB Schema file to some central folder and repeat for each database you want to reference.  Once done, go back into your projects and add a reference to each of those files. Because these are DBSchema files and not the actual database projects, DB Pro will recognize these and you’ll be able to add a reference as needed to each database.

Remember that these schema files will not be kept up to date automatically.  As you build your DB Projects, you’ll need to update those schema files in some way.

Pain Point #2: System Objects

This isn’t a hard problem to work around and there are a lot of hints out there about this.  The easiest way to handle references to the system objects is by adding a reference to the included DBSchema files created when you installed DB Pro.  These can generally be found in %ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer. There should be sub-folders for 2000, 2005, and 2008.  Simply include these references in your projects and you should be set.

This may not always help you if you reference another database referencing the system objects, though. I ran into this problem and had to find yet another workaround, which leads me to…

Pain Point #3: “Unresolved References”

When all else fails and you can’t actually get around the “Unresolved Reference” warnings due to the use of temp tables, referencing another database that references the system objects, or some other reason you have a final recourse. That is to tweak the file properties to ignore certain error messages or warnings that occur for that file.  It’s not a great solution because you could miss some valid warnings, but it can help you get around those warnings that keep popping up when you try to build or deploy.  To add an exception just find the file, select it, and edit its properties.  The last option is to “Suppress Warnings” and that will let you bypass the warnings that normally occur when DB Pro can’t figure out what you are trying to do.  This should really only be turned on for code that is definitely working in your environment and the code should be reviewed carefully when enabling this option.

 

Finally, I don’t necessarily claim that any of these are best practices. We’re learning here and trying to figure out the best way to work around the limitations and quirks in DB Pro to get it working in our environment. I’m more than happy to learn new and better ways to do things so feel free to share your tips or to correct me where I’m wrong.

SSIS and XML Output

I was pretty surprised by this and am in pretty good company.  SSIS 2005 and 2008 do not have an XML Destination adapter. We have another "opportunity" from Microsoft to work out a way to export XML data.  I'll disagree that this is a good idea, despite those saying that it's easy to work around in a script component.  I'm not a great SSIS Script writer.

My problem - I want to take a simple SQL query that uses FOR XML AUTO to generate XML and put that into a file.  If you set this as the only part of your source query, you end up with an output of DT_Image.  Converting that to text results in a long string of numbers. While I was amused at the result, it didn't help me generate the necessary XML for our partners.

I came up with a relatively simple workaround that works well for me because I'm only dealing with one XML Data Set at a time.  I put something like the following in my OLEDB Source:

DECLARE @XMLOutput XML
SET @XMLOutput = (SELECT * FROM MyTable FOR XML AUTO)

SELECT CAST(@XMLOutput as VARCHAR(MAX)) as XMLResults

I was then able to pipe that into a Delimited Text File destination with a CRLF Delimiter and no column headers. That generated results I could pass on to our partners. While it's not the most elegant solution, it was much easier to me than trying to write and maintain a script component just to handle XML.

Data Loads

One of the things I really like about SQL Server Integration Services is that it can load large data sets quickly, especially if you need to look up values along the way.  However, there are times that things behave in strange manners that seem hard to explain.  We were recently trying to load about 64 million rows into a warehouse-type database and kept hitting a bottleneck once we got past around 30-35 million rows.  No matter what I tried, the load would run at about 2 million rows per minute, gradually slowing down until it reached the 10,000 rows per second load speed.  I found several helpful posts, including some pointers to check the MaxRowBufferSize and similar settings.  Tweaking these helped someone who was on a memory-bound machine with a very similar problem.  I tried that and saw my initial load times improve, but still slowed down to a crawl at around the same point.

I learned more about looping through (aka "shredding") a recordset of CustomerID values to try to go through that set.  I set up a FOR Loop to loop through 5 million records at a time, I checked for processes running against the table. Nothing seemed to help.  I had posted information on Twitter, had a co-worker looking at the package with me, and even got some replies from Paul Randal.  (Thank you for the assist, Paul - sorry I wasn't checking my tweets more regularly that time.)

In the process above, I was challenged trying to set the CustomerID in some dynamic fashion on my source query and eventually resorted to treating the SQL Command as an expression and hacking in my variables into the WHERE clause.  It worked, but definitely felt awkward. I am more than open to some suggestions about how to use SSIS variables inside of a FOR loop as part of the OLEDB Source Command.  I also learned a little more about watching variable values - like needing to have a breakpoint set before they'll be available.  And my co-worker pointed me to a wonderful discussion showing that DtExecUI runs only in 32-bit mode. I'd been using that as a quick/easy way to get my parameters plugged in and to keep an eye on the general progress of the load.

About the same time that Paul suggested checking my index fragmentation levels, I remember seeing a very similar behavior pattern several years ago when a co-worker was trying to update a column in the middle of a somewhat wide clustered index on a multi-million row table. The server was thrashing all over the place, trying to re-order millions of rows as this command took place.  I checked the table and sure enough, there was a wide clustered index on the table that would cause exactly that behavior.  I don't know why I didn't check it before other than thinking that I'd just created a test table and no indexes on it.  In retrospect, that was poor planning on my part.  For the future, I'll remember to check clustered indexes before I try to load millions of (unsorted) rows into a new table.

It was a somewhat humbling experience, mostly because one of the basic things I know about loading lots of data was also something that I just completely ignored when doing all of my troubleshooting. Still, a little humility is a good thing and I've now re-learned several programming techniques in the process. The best part about this was seeing a 6 hour load process reduced down to 30 minutes, including dropping and re-creating the clustered index. That's going to make a noticeable difference in our nightly processes.

Getting Started

Well, I was inspired by Steve Jones (of SQL Server Central fame) to start a professional blog, if only to record some of my thoughts and experiences. I'll be writing semi-regularly on SQL Server T-SQL, SSIS, Reports, workarounds, interesting news bits, and things of interest to those who dabble in the world of SQL Server. A lot of it will probably be familiar to those who work with SQL Server quite a bit, but I'm hoping someone will find this helpful.

Thanks for stopping by and feel free to let me know how I'm doing.

-Peter Schott