You can create a script task component within SSIS to check for a file and then sleep, however several sources said that this could spike the processor so I deceded to go a different route.  To solve the problem, I wrote a quick little powershell script to check if the file exists and then wait in a loop before starting the SSIS package.

I created a SQL agent job with 2 steps.  The job runs daily at 1:00 AM. The job has a status of Executing on Step 1 until the file exists.

Step 1: powershell script to check for the file (see below)

Step 2: Execute SSIS package task.

The file that I am looking for in this example is: C:\test\ImportFolder\fileToCheck_20130212.txt
You will notice that today’s date is also appended to the end of the file name.  I built the file name out dynamically along with the current date stamp.

$dt = Get-Date -format yyyyMMdd
$path = 'C:\test\ImporFolder\'
$theFile = $path + 'fileToCheck_' + $dt +'.txt'

#  $theFile variable will contain:  C:\test\ImportFolder\fileToCheck_20130212.txt
While (1 -eq 1) {
	IF (Test-Path $theFile) {
		#file exists. break loop
		break
	}
	#sleep for 60 seconds, then check again
	Start-Sleep -s 60
}

This post is part of the blogging phenomenon known as TSQL Tuesday. This month’s blog party is hosted by Wayne Sheffield, who is writing a series of blog posts in the month of February all about powershell..

In this tidbit, I have a multi-step SSIS package that needs to be run on a daily basis.  However, one step of the process should only be run on a specific day of the week (lets say it should only be run on Tuesday).

My screenshots are all taken in Visual Studio 2010.  The process is the same for 2008 R2.

The process:

1. Create the necessary steps.  For this simple example, I created 3 Execute SQL tasks.  They each run the query: “SELECT 1″.
   
2. Drag the green arrows to link each of the tasks in order.  By default, the links will all be success constraints.
   
3. Double click on the line between Task 1 and Task 2.  This will open the Precedence Constraint Editor.
1. Change Evaluation operation to: “Expression and Constraint”
2. In the expression block, type: “DATEPART( “dw”, getdate()) ==3″
• Using the DATEPART(“dw” ) function, Sunday=1 and Saturday=7.  Since we only want Tuesday, we choose ==3.
3. Press OK
4. You will notice that the line between Task 1 and Task 2 now has an “fx” symbol on top of it.
   
4. At this point, the package will run and Task 2 will only run on the specified day.  However there is an issue with the current setup: Task 3 will begin executing as soon as Task 1 completes.  It will not wait  until Task 2 completes on Tuesdays.  We need to modify that link as well to create a fork in the path.
5. Double click on the link between Task 1 and Task 3.
1. Change Evaluation operation to: “Expression and Constraint”
2. In the expression block, type: “DATEPART( “dw”, getdate()) !=3″
3. Under multiple constraints, change to the option: “Logical OR. One constraint must evaluate to True”
• Changing to Logical OR is required.  Since we created the fork at Task 1, only one of the two lines going into Task 3 will evaluate as successful completion.
4. Press OK
5. You will notice that the line between Task 1 and Task 3 turns into a dashed line and it also has an “fx” symbol on top of it.
   
6. You can now run your package to test that the steps work properly.  To test the design, I changed my formula’s ==3 and !=3 to a different day of the week to make sure that Task 2 got bypassed correctly on its off day.

The big news is that Pittsburgh’s SQL Satuday is this Saturday October 6 at La Roche in the north hills of Pittsburgh.  I have been organizing the schedule for the event, which is now posted on the SQL Saturday site. Check it out, and make sure you get registered to attend.  There’s a great mix of local and out of town speakers, and some great topics that will be presented.

Let me know if you have any questions, and hope to see you on Saturday!

PS C:\> Import-Module activedirectory

After importing the module, you can learn more about the Get-ADUser cmdlet by using some of these commands

Get-Help Get-ADUser
Get-Help Get-ADUser -examples
Get-Help Get-ADUser -detailed

Examples are great, but I learn better by seeing real results, so lets run a quick query to see what information we get.

Get-ADUser -filter * -ResultSetSize 1
#Note, I included “-ResultSetSize 1” so that I was not overwhelming the domain controllers while testing.

Awesome, I can now see user accounts from Active Directory! The output that I got showed me some of the information that I needed, but I am still missing some pieces (primarily email address and phone number). The “-Properties” option will let you pick additional fields to include in the output. I got a little stuck here briefly, because the Get-ADUser cmdlet names for the properties do not all match the Active Directory field names. To figure out what the appropriate field names were, I ran this:

Get-ADUser -filter * -ResultSetSize 1 -Properties *

Cool, now I can put the fields together to get a shortened list of only what I am looking for:

Get-ADUser -filter * -ResultSetSize 1 -Properties EmailAddress,OfficePhone
# Note this will return additional fields (DistinguishedName,Enabled,ObjectClass, SID,…)

I got a little bit stuck here too, because I was getting too much information. When I got to the point of exporting this data to a CSV file and importing it into SQL Server (coming later), I got hung up because some of the fields did not always have information for my organization. The solution came by using a pipe (SHIFT + \ key) and the Select-Object cmdlet. This let me filter for only the specific columns that I wanted out of Active Directory.

Get-ADUser -filter * -ResultSetSize 1 -Properties EmailAddress,OfficePhone | Select-Object EmailAddress,OfficePhone,DisplayName,SamAccountName

I now see only the 4 columns that I care about. On a larger scale test, I realized that I was returning accounts that I did not want to see (like disabled accounts, Administrative accounts, etc.) I used the –Filter option to include some search criteria here.
Filtering in powershell is a little different than what I am used to. For example, “=” is “-eq” in powershell and “not equal to” or “<>” is “-notlike” in powershell. You can also combine multiple filters by including the entire set in curly brackets { }, individual parameters in parenthesis (), and using the “-and” operator. The Asterisk is the wildcard variable.
For example:

-Filter {(Name -notlike "*(Administrator)") -and (Name -notlike "Matt*") -and (Enabled -eq "True") }
# I also threw in there where Name is not like Matt*

Now that I have only the fields that I want, and I filtered out the users that I don’t want to see, I can start working on importing it into SQL Server. I could have used powershell to insert the records directly into SQL, but I was concerned about latency issues and spamming the domain controllers into a denial-of-service attack. I was working with more than 50,000 Active Directory accounts. I definitely did not want to hold up the domain controllers if there was an issue with the SQL server during the process. Because of this, I decided to export the data as a CSV comma delimited file and then use SSIS to import the data.
Exporting the data to a csv file uses another pipe (SHIFT + \ key) and the export-csv cmdlet. Make sure to put in your appropriate file path to export to

#Make sure you put your file path between the < >
 | export-csv -path \\\\ADUsersExported.csv -NoTypeInformation -Encoding "UTF8"

Putting everything together.

Make sure to put in your appropriate file path to export to.
I also took out the “-ResultSetSize” option so that all records were returned.

#Make sure you put your file path between the < >
Get-ADUser -Filter {(Name -notlike "*(Administrator)")  -and (Enabled -eq "True") }  -Properties SamAccountName,DisplayName,EmailAddress,OfficePhone | Select-Object EmailAddress,OfficePhone,DisplayName,SamAccountName | export-csv -path \\\\ADUsersExported.csv -NoTypeInformation -Encoding "UTF8"

Once the data was exported to a CSV comma delimited file, I am using SSIS to import it into SQL server. The powershell script and SSIS package are both scheduled to run daily overnight when things should be slower on the servers.

=================================================

My first job out of college was a Network Administrator for a small non-profit organization.  As the only IT guy in the organization, I was responsible for a lot…managing servers, planning upgrades, scheduling downtime, email setup/support, network switches, cabling, wireless network access/security, managing data, backing up data, help desk support, desktop pc support, web site design, managing the phone system, building overhead paging, video surveillance system, report design and generation, unjamming printers and copiers, evaluating new applications, and developing custom applications.  The organization has classrooms spread out across the entire county.  Each classroom had various technologies from a PC, to phones, answering machines, and speaker systems.

I definitely could not specialize in any particular area in this position.  On any given day I could have needed to travel 60 miles round trip to fix a problem in one of the outlying classrooms and return to my office prepare data to submit for a federally mandated report.  When people asked me what I did, I would tell them my job was to fix anything that plugged into a wall.  With a non-existent budget, I had to get creative to make things work in the organization.

Through a series of life choices, I moved on to a new company in a new position that focused more on systems and database administration.  This position is definitely a lot more specialized than where I started.  There are now other support groups that I can refer people to for issues with PC’s, Exchange, networking, phones, etc.

I still think that it is important to keep up on a lot of the basics, especially with SQL server.  People like to blame the database as the problem when often times the solution is not nearly that simple.  Many problems that I encounter on a day-to-day basis are rooted in specialties not directly related to SQL server.  I may not be a specialist in networking, pc repair, or Active Directory administration, but it has been very beneficial to me to have a good working knowledge of these concepts.  There are other people responsible for fixing these things at my current company.  I can usually figure out what the problems is  and direct to the correct support groups fairly quickly with my generalist background.

Carl Demelo shared an awesome way to move your template directory to a different directory on SQL Server Central.  This solution will only work on Windows 7 using the new shell command mklink.

I tried it out to move my template directory to a network drive that gets backed up on a regular basis.  It worked perfect, thanks Carl!

Now for my trick, I have a view that was created to help write more dynamic rolling sql queries and reports in my organization.  I’m not sure who the original author of the script is, as it has been passed around and modified several times.  I thought I would share it here in the hopes that it helps someone else someday.  If you’re the original author or know who is, please let me know so I can give you due credit.

This view defines a bunch of different date parameters compared to the current date, including:

• TODAY_BEGIN
• TODAY_END
• YESTERDAY_BEGIN
• YESTERDAY_END
• DAY_BEFORE_YESTERDAY_BEGIN
• DAY_BEFORE_YESTERDAY_END
• SUNDAY_WEEK_BEGIN
• SUNDAY_WEEK_END
• MONDAY_WEEK_BEGIN
• MONDAY_WEEK_END
• PREVIOUS_SUNDAY_WEEK_BEGIN
• PREVIOUS_SUNDAY_WEEK_END
• PREVIOUS_MONDAY_WEEK_BEGIN
• PREVIOUS_MONDAY_WEEK_END
• MONTH_BEGIN
• MONTH_END
• YESTERDAYS_MONTH_BEGIN
• YESTERDAYS_MONTH_END
• PREVIOUS_MONTH_BEGIN
• PREVIOUS_MONTH_END
• SECOND_PREVIOUS_MONTH_BEGIN
• SECOND_PREVIOUS_MONTH_END
• THIRD_PREVIOUS_MONTH_BEGIN
• THIRD_PREVIOUS_MONTH_END
• FOURTH_PREVIOUS_MONTH_BEGIN
• FOURTH_PREVIOUS_MONTH_END
• TWELTH_PREVIOUS_MONTH_BEGIN
• TWELTH_PREVIOUS_MONTH_END
• PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN
• PREVIOUS_SIXTH_MONDAY_WEEK_END
• PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN
• PREVIOUS_SIXTH_SUNDAY_WEEK_END
• NEXT_MONTH_BEGIN
• NEXT_MONTH_END

You can take a quick look at the result returned from this view.  After creating the view (script included below), run:

SELECT  *
FROM    vw_date_ranges

With these columns defined, you can easily query a database table looking for rows based off of a date by cross joining this view and the adding the date columns to the where clause.  For example, if you want to see all orders for “This Week”, you can run a query similar to:

SELECT SalesOrderID,
       OrderDate,
       SalesOrderNumber,
       PurchaseOrderNumber,
       CustomerID,
       TotalDue
FROM   Sales.SalesOrderHeader
       CROSS JOIN vw_date_ranges
WHERE  Sales.SalesOrderHeader.OrderDate > vw_date_ranges.SUNDAY_WEEK_BEGIN

Or if you want to see all orders for “Last Month”, you can run a query similar to:

SELECT SalesOrderID,
       OrderDate,
       SalesOrderNumber,
       PurchaseOrderNumber,
       CustomerID,
       TotalDue
FROM   Sales.SalesOrderHeader
       CROSS JOIN vw_date_ranges
WHERE  Sales.SalesOrderHeader.OrderDate >= vw_date_ranges.PREVIOUS_MONTH_BEGIN
       AND Sales.SalesOrderHeader.OrderDate < vw_date_ranges.MONTH_BEGIN

Using this method helps me keep my sql query clean, and provides a rolling date range on queries and reports.

Download the script here

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_date_ranges]
(TODAY_BEGIN, TODAY_END, YESTERDAY_BEGIN, YESTERDAY_END, DAY_BEFORE_YESTERDAY_BEGIN,
DAY_BEFORE_YESTERDAY_END, SUNDAY_WEEK_BEGIN, SUNDAY_WEEK_END, MONDAY_WEEK_BEGIN, MONDAY_WEEK_END,
PREVIOUS_SUNDAY_WEEK_BEGIN, PREVIOUS_SUNDAY_WEEK_END, PREVIOUS_MONDAY_WEEK_BEGIN, PREVIOUS_MONDAY_WEEK_END, MONTH_BEGIN,
MONTH_END, YESTERDAYS_MONTH_BEGIN, YESTERDAYS_MONTH_END, PREVIOUS_MONTH_BEGIN, PREVIOUS_MONTH_END,
SECOND_PREVIOUS_MONTH_BEGIN, SECOND_PREVIOUS_MONTH_END, THIRD_PREVIOUS_MONTH_BEGIN, THIRD_PREVIOUS_MONTH_END, FOURTH_PREVIOUS_MONTH_BEGIN,
FOURTH_PREVIOUS_MONTH_END, TWELTH_PREVIOUS_MONTH_BEGIN, TWELTH_PREVIOUS_MONTH_END, PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN, PREVIOUS_SIXTH_MONDAY_WEEK_END,
PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN, PREVIOUS_SIXTH_SUNDAY_WEEK_END,NEXT_MONTH_BEGIN,NEXT_MONTH_END)
AS
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)                                TODAY_BEGIN,
dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()+ 1 ), 0)) - .000011574        TODAY_END,
DATEADD(dd, DATEDIFF(dd,0,getdate()), -1)                                       YESTERDAY_BEGIN,
dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()  ), 0)) - .000011574          YESTERDAY_END,
DATEADD(dd, DATEDIFF(dd,0,getdate()), -2)                                       DAY_BEFORE_YESTERDAY_BEGIN,
dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()  ), -1)) - .000011574         DAY_BEFORE_YESTERDAY_END,
-- Relative Dates - Weeks
DATEADD(wk, DATEDIFF(wk,0,getdate()), -1)                        SUNDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -1) + 6.999988426          SUNDAY_WEEK_END,
DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)                         MONDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)  + 7.999988426          MONDAY_WEEK_END,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -8)                        PREVIOUS_SUNDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -8) + 6.999988426          PREVIOUS_SUNDAY_WEEK_END,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -7)                        PREVIOUS_MONDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -7) + 7.999988426          PREVIOUS_MONDAY_WEEK_END,
-- Relative Dates - Months
dateadd(mm,datediff(mm,0,getdate()),0)                                                    MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0)) - .000011574                   MONTH_END,
dateadd(mm,datediff(mm,0,getdate() - 1),0)                                                YESTERDAYS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() - 1 )+1, 0)) - .000011574                YESTERDAYS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-1, 0)                                              PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0)) - .000011574                    PREVIOUS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-2, 0)                                              SECOND_PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )-1, 0)) - .000011574                  SECOND_PREVIOUS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-3, 0)                                              THIRD_PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )-2, 0)) - .000011574                  THIRD_PREVIOUS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-4, 0)                                              FOURTH_PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )-3, 0)) - .000011574                  FOURTH_PREVIOUS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-12, 0)                                              TWELTH_PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )-11, 0)) - .000011574                  TWELTH_PREVIOUS_MONTH_END,
-- Added 2/23/09 These will be used in a claim audit report that looks at data from 6 weeks prior.
DATEADD(wk, DATEDIFF(wk,0,getdate()  )-6, 0)  as   PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()  )-6, 0) + 7.999988426  as PREVIOUS_SIXTH_MONDAY_WEEK_END,
DATEADD(wk, DATEDIFF(wk,0,getdate()  )-6, -1) as   PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()  )-6, -1) + 6.999988426  as PREVIOUS_SIXTH_SUNDAY_WEEK_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )+1, 0)                                              NEXT_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )+2, 0)) + .000011574                    NEXT_MONTH_END

An account becomes orphaned when there is a user account with security rights in the database that is not linked to a user account that can log in to the server.  This happens because SQL logins with the same username have different SID’s on different servers.  It is not a problem for Active Directory logins because the SID is stored in Active Directory.

Once an account becomes orphaned, it is a pretty easy fix:

USE <database name>
ALTER USER <username> WITH LOGIN = <username>

But it’s still a pain to remember to run this code to reset the logins.  I usually forget, and the scratch my head for a minute to figure out why the application won’t connect to the development database anymore.

Fix it once and for all

Microsoft has a handy script to fix orphaned users (link).  Download and run the script on your production server to create two stored procedures: sp_hexadecimal and sp_help_revlogin.

Then run:

EXEC sp_help_revlogin

Run the output from this procedure against your development server to create the appropriate logins with the correct username/password and SID.

Ever since I ran this, I have not had any more orphaned users when restoring databases back to dev.

One of the main vendor-built applications that I support has views built on top of tables.  Unfortunately that’s not the end of the story.  These views are built on top of views, which join together other views, which link back to the database tables.  In all the training materials provided by the vendor, they say to *always* use the views when writing a report on the data and never directly query the table.

Using the vendor provided views generally works out OK and performs reasonably well.  One particular report I wrote kept bugging me due to how long it took to run (several minutes each time). compared to how much data was actually returned.  So I started looking at the execution times and the query plans.  Note, I took the screenshots below using the fantastic free tool from SQL Sentry, SQL Sentry Plan Explorer.

This report needed to join 5 tables to get the data that I needed.  Using the vendor provided views, here is the join diagram I started out with:

You can see that the vendor views are joining together a lot more hidden tables (table & field names blurred to protect the innocent) than the 5 I actually need.  The nested views are even hitting the same tables more than once.

Here is the original query plan:

You can’t see it in the screen shot, but one of the thick lines in the middle is representing 23 million rows!

OK, time to pull out the detective hat.  I decided to rewrite the query using only the base tables.  I had to do a bit of extra work with things like UTC datetime vs. local time which the views converted.  After the rewrite, the join diagram looked like this:

Here is the final query plan:

The highest number of rows coming through is about 11,000.  A far cry from the 23 million rows in the original query! The execution plan also looks a lot leaner than what I started with as well.

The original query was running with an average CPU time = 26062 ms, and an average elapsed time = 26424 ms.
My rewritten query is now running with an average CPU time = 0 ms, and an average elapsed time = 266 ms.

Looking at the actual execution plan, SSMS is prompting me that there is a missing index for my new query, and there is still a Clustered Index Scan that accounts for 87% of the query.   I may look into that more at a later date, but for now I am very happy running a query in a couple of seconds that used to take several minutes. I don’t think it’s really worth the extra effort to try to shave another 100 ms off of a query that completes in under 300 ms.

The vendor supplied nested views were each joining together multiple views and tables, which was causing a lot of extra and unnecessary bloat in my query.  An hour of work and cutting out all the bloat made a huge difference in this particular report’s run time.

And the users rejoice at how fast their report now runs!