A SQL day in the Life…


Needed to take a short break from ‘Creating the Lab’ <<said with a big announcer voice with full on reverb>> and take a detour (detox?) down SSIS-ville for a second.  When you get to wear many hats, the hits keep coming.  Wee-haaaaaaa! (expletive deleted…).  First this little segue started out with a simple enough request: We need to add this ‘other’ data to the data warehouse.  Easy right?

Maybe a picture would help?

image

The data warehouse and app lives in the company space and the ‘other’ data lives on a different network in web-ville.

Do to security stuff, this problem was resolved with a third server… something like this…

image

But it’s not as clean as that…  Webbie writes a file that the intermediate server picks up from the web and deposits it to a file share where the receiver checks the directory for the file and imports it when it’s there.  Well, I didn’t want to follow that same path and my goal was to simplify it using a single script and a point to point VPN.  Just too many moving parts and many possible points of failure.

So my mental picture looks like: 

image

I think this looks simpler.  Now, to get the script to do what I need it to do…

First, says I, I’ll put this all in SSIS, Schedule it as a SQL Job and voilà, I get something like…

image

I’m going to leave this diagram simple because this no workee… no matter what variation I tried.

For example: Connect to Web uses a superset of credentials that have all the right permissions.  The connection is visible to all yet the subsequent SSIS package never see’s the connection.  Now to be honest, I actually did not try the final configuration that is shown above and that “may” have worked.  But in the process of trouble shooting, I discovered that PowerShell could fire off the connection, then the SSIS package without a problem.

So, Pretty Pictures asside, this is what I ended up with:

#instantiate the VPN connection using RASDIAL
rasdial <Connection Name> <connection credentials> /domain<web domain>

#execute the SSIS package using dtexec
dtexec /f "C:\scripts\<package file name>.dtsx" | Out-File $File –append

#close VPN connection
rasdial <connection name> /disconnect

This doesn’t seem to log much but the pipe directive sends all the output to the file giving you a very rich report:

Time Started: 02/27/2012 05:33:57
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 5:34:06 AM
Progress: 2012-02-27 05:34:17.30
Source: Post to Final Tbls
Validating: 0% complete
End Progress
Progress: 2012-02-27 05:34:17.92
Source: Post to Final Tbls
Validating: 25% complete
End Progress
Progress: 2012-02-27 05:34:18.12
Source: Post to Final Tbls
Validating: 50% complete
End Progress
Progress: 2012-02-27 05:34:18.17
Source: Post to Final Tbls
Validating: 75% complete
End Progress
Progress: 2012-02-27 05:34:18.37
Source: Post to Final Tbls
Validating: 100% complete
End Progress
Progress: 2012-02-27 05:34:18.37
Source: Prep
Validating: 0% complete
End Progress
Progress: 2012-02-27 05:34:18.37
Source: Prep
Validating: 25% complete
End Progress
Progress: 2012-02-27 05:34:18.47
Source: Prep
Validating: 50% complete
End Progress
Progress: 2012-02-27 05:34:18.47
Source: Prep
Validating: 75% complete
End Progress
Progress: 2012-02-27 05:34:18.55…

Hmmm…  maybe a little more info in the log would be helpful

#instantiate vars and file name
$dt = get-date
$d = $dt.dayofweek
$File ="C:\scripts\ServiceLog_" + $d + ".txt"
#start log
"—————— Started:" + $dt + "  ——————" | Out-File $File <<add –append if overwrite not desired>>

So far so good.

# begin Processing
# open VPN
"—————— Connection opened: " + (get-date).toshorttimestring() + " ————" | Out-File $File –Append

rasdial <connectionName> <credentials> /domain:<domain>

"—————— SSIS Started: "  + (get-date).toshorttimestring() + "  ————————–" | Out-File $File -Append
dtexec /f "C:\scripts\PullDataFromRWeb.dtsx" | Out-File $File -append
"—————— SSIS Completed: " + (get-date).toshorttimestring() + " —————-" | Out-File $File -append

Finally
# close vpn
rasdial <connectionName> /disconnect
"—————— Connection Terminated: " + (get-date).toshorttimestring() + "———" | Out-File $File -Append

Ah, much better.

Output?

image

Left off the SSIS package.  I’ll update this later once the job runs again.

Ok, now I set up the package to use a domain account and set up the schedule and got…

Error: 2012-02-28 10:42:04.10
   Code: 0xC0016016
   Source: 
   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
Error: 2012-0

Oh ya, file system SSIS and security… encryption is an issue.

Easy to fix…

image

Set a password…

image

Now, a little bit of error trapping and the final script looks something like:

#instantiate vars and file name

$dt = get-date
$d = $dt.dayofweek
$File ="C:\scripts\ServiceLog_" + $d + ".txt"
$state = ""
#start log

# begin create error handling and function defs

# open VPN Function

Function connectVPN
{

"—————— Started:" + $dt + " ——————" | Out-File $File
"—————— Connection opened: " + (get-date).toshorttimestring() + " ————" | Out-File $File -Append

rasdial <connection name> <credentials> /domain:RMLSWeb | Out-File $File -Append

}

#execut the SSIS package
Function executeSSIS
{
"—————— SSIS Started: " + (get-date).toshorttimestring() + " ————————–" | Out-File $File -Append

dtexec /De <password for SSIS> /f "C:\scripts\<packageName>.dtsx" | Out-File $File -append

"—————— SSIS Completed: " + (get-date).toshorttimestring() + " —————-" | Out-File $File -append
}

# close vpn

Function disconnectVPN
{
rasdial "<connection>" /disconnect
"—————— Connection Terminated: " + (get-date).toshorttimestring() + "———" | Out-File $File -Append
}
#cls
connectVPN
$state = test-connection "<server name – FQDN>"
#write-host $state.length

if ($state.length -gt 0)
{
executeSSIS
disconnectVPN
}
else
{
"—————— Connection Failed: " + (get-date).toshorttimestring() + "———" | Out-File $File -Append
rasdial "<connection>" /disconnect
"—————— Connection Terminated: " + (get-date).toshorttimestring() + "———" | Out-File $File -Append
}

A little note about the above script.   It creates 7 log files Monday – Sunday.  In the script I’m running, I’m checking on this information daily so adding addition elements like sending notifications and such isn’t needed.  If it was in a big server farm, I’d add a few more bells and whistles to provide constructive notice.  Since a ‘Short history’ is sufficient, I changed the log action in the first function from:

"—————— Connection opened: " + (get-date).toshorttimestring() + " ————" | Out-File $File –Append

to

"—————— Connection opened: " + (get-date).toshorttimestring() + " ————" | Out-File $File

(Dropped the append)

This way it’s a new file that remains small and I will always have 7 days of history to go back to.

Thus my output directory looks like:

image

Published by

Andre

Originally, this was a pretty darn boring post. Kinda like... Well I won't go there. Perhaps its still on the robotic side but... I could say I like music. Safe, generic and non-comittal. Or, I could say that I've been having a blast tuning up my old guitars, getting blisters on my fingers and turning the amp up past 2. Amazaing what a little overdrive and a half pressed wahwah pedal can do for a sound. Get that cool "Money for Nothin" vibe happening. I get a real kick out of reading old Sci Fi. Reading Asimov's vision about the future is really entertaining now. When he wrote much of the material, the items that were futuristic were day to day tools I used in the early part of my career. Microfiche and the like. I also remember that upstart Microsoft and MS DOS...and can you say Lotus 123? So maybe this is a little better than "I like to read and play music". My career is focused on Team Leadership and Technology. Both share the attributes of continual growth and education. Currently, I manage a team of 4 programmers as direct reports. I've been in this role since 08/2007. Prior to that, I was the team lead (on site) for integration with the customer. Customers ranged from local government to manufacturing and medical. Teams ranged in size from one to six additional team members. On the other side is technology. I've been keeping current with .net technologies focusing on C# and Sharepoint (2007/2010). Specialties Team Building and Management Technical Staff Recruiting Microsoft Visual Studio 7 through 2010 (VB.NET and C#.NET) Microsoft SQL Server 6.5-2008R2 (DTS, TSQL, SSIS, SSAS, and SSRS ) SharePoint 2010 (Office Integration, InfoPath, Site Management and planning) Windows Server 2008 R2 AD DS PowerShell Techology analysis Puchasng and working with vendors Microsoft licensing management and compliance Business Systems Analysis Traning plans Mentoring Training coordination.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s