Home > Microsoft, PowerShell, Programming, SQL Server > A SQL day in the Life…

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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: