I’m still here and some great resources

Well, after a long break from blogging I am back. We have a new addition to the family so that kept me away. Actually, that and just being lazy to blog really.
Anyway, I recently came across some useful resources on SQL 2005 and thought I would share:

JumpStartTV
Great intro videos for beginners wanting to learn SQL 2005, SSIS, SSAS, etc. They are free but you have to register.

TechNet Virtual Labs: SQL Server 2005
These labs include SSAS, Management Studio, SSIS, Data Mining, etc.

How to use rank function in SQL Server 2005
A very easy to follow query example on the ranking functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE

DTS Dynamic Properties and Global Variables

It’s been a while since I used Global Variables in SQL DTS. So here is a simple tutorial to:
Setup a Stored Procedure using Input Variables Dynamically from an INI file (or any other source available in Dynamic Properties Task)
(e.g. EXEC usp_test ?, ?)

1. Add a SQL Task and enter your SQL script e.g. EXEC usp_test ?,?
a) Click on “Parameters”
b) Click on “Create Global Variables” to create global variables depending on how many you need in your SP
c) Click OK
d) Assign each Global Variable to a Parameter.
e) Click OK

2. Assigning the Global Variables to values from an INI file:
a) Add a Dynamic Properties Task
b) Click on “Add”
c) Expand on Global Variables
d) Select a Global Variable and click on Value and then click on Set
e) On the Add/Edit Assignment window, select a value from an INI file
f) Repeat steps (b) to (e) to assign other Global Variables
f) Click OK and you’re done

Note: There are multiple ways to accomplish the tasks above. For instance, you can add new global variables when you first create a DTS package. Then add a Dynamic Properties task to assign values to the Global Variables. Finally, add a SQL task to execute the SP using input parameters. The global variables at this point will already be available.