Aaron Akin

February 2, 2009

Sorting SSIS Packages & Folders in SSMS

Filed under: SQL,SQL 2005,SQL 2008,SSMS — Aaron Akin @ 5:24 pm

It had always bothered me when I opened a SQL 2005 Integrated Services instance to find an SSIS package, only to see that the folders and packages were not in alphabetical order.

SSMS Object Explorer

It wasn’t long before this started to get irritate me.  I did some digging and found 2 system stored procedures (sp_dts_listfolders and sp_dts_listpackages) in the msdb database that SQL Server uses to display the packages and folders in menus.



January 21, 2009

Recursive Common Table Expressions – Part 2

Filed under: CTE,SQL,SQL 2005,TSQL — Aaron Akin @ 7:48 pm

In my last post, Recursive Common Table Expressions – Part 1, I gave a brief overview of common table expressions (CTE) and gave some examples on how to use them to write recursive queries.  The first two examples showed how to find the missing numbers, or dates, in a sequence.  It was assumed that you already had a table with a list of numbers or dates in it.  The examples in this post will expand on the first two examples to show you how to build a range of numbers or dates given a starting and ending point.


January 20, 2009

Recursive Common Table Expressions – Part 1

Filed under: CTE,SQL,SQL 2005,TSQL — Aaron Akin @ 6:43 pm


I recently had a project that required me to find the number of employees who were scheduled for every 15 minute increment, for every business day for a week, and for many stores.  Yikes!  Sure, I could use tons of loops and temp tables to do this, but I’m all about set based queries whenever possible…and I knew this was possible and I love a challenge!

It finally occurred to me that I could use the self-referencing (recursive) capabilities of Common Table Expressions (introduced in SQL 2005) to the mimic loops I needed to accomplish my goal.

I took some of the recursive CTE queries that I came up with for my project and turned them into 4 examples.  It was quite a bit of info, so I’ll be spreading them across 2 posts.


January 14, 2009

Failed to lock variable for read access in SSIS

Filed under: Checkpoints,SSIS — Aaron Akin @ 5:37 pm

I was working on a new SSIS package recently, and in the process of trying to fix an issue with my package, I created an entirely new problem.  My package now starting failing with the following error:

Error: Failed to lock variable XXXX for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created."

It was so frustrating that I figured I would post it here in case anyone else comes across the same problem.

If you’ve ever seen this error before, you’ll most likely find that it is caused by a script task/component that is referencing a variable that no longer exists.  In my case, I had no scripts but I did change some of my package variables.

After a while of trying to figure out what would cause this issue, I finally realized that my package had checkpoints enabled.  When checkpoints are enable, details about the variables are written to the checkpoint file when the package is executed.

In the process of fixing the original problem with my package, I deleted some of the existing variables.  I made sure to update all of my tasks that referenced the deleted variables, but I forgot to delete the checkpoint file before restarting the package.

When the checkpoint file was read upon execution, it tried to assign a value to the variables that were deleted, but since they did not exist anymore, I got this error.

I guess the point is, if you make changes to the structure of a package when checkpoints are enabled and a checkpoint file exists, it might be worth your time to delete the file before restarting the package with the new changes in effect.


January 5, 2009

Cleaning Recent Projects List in Visual Studios

Filed under: SQL 2005,SQL 2008,Visual Studios (BIDS) — Aaron Akin @ 6:16 pm

I’m always creating a bunch of projects in Visual Studios for testing purposes, all of which clutter up my Recent Projects list on the VS Start Page.  If you’re like me and want to clean up this list, make sure Visual Studios is closed and then open up the Registry Editor (Start > Run > regedit) and find the following key:

Visual Studios 8.0 (SQL 2005):

Visual Studios 9.0 (SQL 2008):

Here you’ll find a list of keys that represent the projects/solutions that appear in your Recent Projects.  You can either delete all of these entries, or just specific ones.  Be careful if you only delete specific ones though.  If you have 4 items (File1, File2, File3, File4) and you delete File2, only File1 will appear in your Recent Projects list.  To fix this, you’ll just need to rename the rest of the keys so that there are no gaps in the sequential numbering.  In this case, you would need to rename File3 to File2 and File4 to File3.

When you re-open Visual Studios, you should find a much cleaner list.


« Previous PageNext Page »

Blog at WordPress.com.