Thursday, March 14, 2013

Export SSIS from MSDB when all else fails

So I saw an interesting problem.

DTUTIL, and the interface to SQL Server management studio are broken on a production server, but no one is certain what package is currently running in production, and aren't sure how far back to look into source control to find the package with the right version number. 

Since the package is unable to be exported in a simple manner, IE using the command line or SQL server management studio, there has to be another way to get the data off of the server. 

What I did was take a reference example I have available of creating files from BLOB data and generated an SSIS package that can be pointed at a servers MSDB and extract all of the SSIS packages, with only a couple small changes for anyone's specific situation.

The basic build of the package is this:

1.  Create a connection to MSDB
2.  Create a variable to hold the file path
3.  Create a dataflow
4.  Create an Ole DB Source to the MSDB connection
5.  Use a select statement (such as the following)

name + cast(verbuild as varchar(255)) + '.dtsx' as filename
,packagedata -- quick row number script

from sysssispackages packages
join dbo.sysssispackagefolders folders
on packages.folderid = folders.folderid
where ownersid <> '0x01'

6.  Use a derived task to create a fully fledged filename
7.  Create a transform script task to generate folders in the export folder if they don't already exist
8.  Use an export column transform to do the actual blob export.

Below is the example I made for this.

Tuesday, March 12, 2013

Finding the visible in the invisible

I don't really intend this to be a general blog, but here is something I found very interesting and it leaped right out at me as an opportunity for programming.

So many things that are done are based on subtle facial twitches, heart rates, with this stuff you could codify it visually with lag time (IE the movement stuff is a post processing thing) but lets say they could do this in ALMOST real time, and you had the visual part in google glass. So lets say a 1-2 second lag time. You could SEE someone getting heated when you ask them a question, even if they are not "saying" they are angry or mad. Optimistically you could have a hippy answer and say "this will let us be more socially adept" but really people would just probably use it to see if their answers or responses are positive when trying to pick up the ladies  

Officers could probably tell IMMEDIATELY if you were drunk, and if they used this tech to do that (IE no outward signs of drunkeness but had flush skin, breathing wrong or something) would that be enough to allow them to search you (4th amendment? ) would these be allowed by Jury members in a court situation? Could software be done to look at subtle eye ticks and be calibrated to tell if someone is lying in real time? How about the ability to test for attraction (IE when a person glances at you, does their heart rate increase, do their eyes widen) etc... What a cheat sheet, Hell, if I was going to make a social interaction program with it, I would actually just call the program Cheat Sheet.