Thursday, March 25, 2010

PowerShell 2.0 - Capture file names

Powershell 2.0 is a very powerful tool  and certainly can be used for sql server database tasks. One of the rquirement i had was to get the list of filesnames and the lastmodified date in a particular drive/folder, use the most recent file as a excel file source to one of my SSIS packages. One of the approaches as mentioned here was to create a table which will contain the filename and the last modified date. In this case the all the files from different days have the same schema/layout. One of the first thins i did was use a powershell script to look for the file with the given extension and store each of the file and date into the sql server table. I would like to thank Sean McCown for showing how to use the dir command in powershell to find files, described in his blog(http://midnightdba.itbookworm.com/). Here is the script which finds the files, then I loop through the collection and insert records into the sql server table. I developed this in Windows Power ISE editor version 2.0, In this script i look for excel spreadsheets.

add-pssnapin SqlServerCmdletSnapin100

set-location "c:\ssis\"

$query=“INSERT INTO Table_1 SELECT "

$files=dir -recurse -include *.xls

foreach ($file in $files)

{

     $file.name,$file.LastWriteTime

     $fquery = $query + "'"+$file.name+"'"+", "+"'"+$file.LastWriteTime+"'"

     invoke-sqlcmd -query $fquery -database MyDb -serverinstance MyServer
}
 
The above script inserts records into the Table_1 table in the database MyDb. Once this is done in the job step, in the next step I run a query to find the most recent file and use that as the file as an excel source in the SSIS package.

Monday, March 15, 2010

PowerShell 2.0

PowerShell is slowly but steadily gathering steam if one were to gauge by the number of powershell projects in codeplex. PowerShell 2.0 is now available for download from the micorosft site: http://support.microsoft.com/kb/968929. The powershell version 2.0 includes a script editor which is called Powershell ISE, the editor makes it a lot easier to write powershell scripts.  Here is a snapshot of how the Windows ISE Editor looks...

The script editor has the ability to open multiple script files by using the tab interface. The key powershell cmdlets are highlighted in color which provides more readability. The script editor also has a help section which provides pretty good examples in powershell commands. With the advent of the editor writing  Powershell scripts for sqlserver has become much more easier in terms of coding and debugging the scripts. There are different ways to get into the sql server powershell mode, one is using the PSSnapin command:
Add-PSSnapin SqlServerCmdletSnapin100,
Add-PSSnapin SqlServerProviderSnapin100
The other way is to make SMO calls and instantiate SMO objects pertaining to SQL Server. There is a popular SQL Server PowerShell Library availalbe in Codeplex called SQLPSX, the latest version is 2.1,here is the link:
http://www.codeplex.com/SQLPSX

Monday, March 8, 2010

SQL Saturday Warpup

The sql saturday seesion which was organised in Charlotte was a super successful event. The event had a lot of sessions by around 14 MVP's with topics ranging from SQL Server Internals to Data Mining. These events are now going to be owned by PASS. The topics which i attended included Integrating Reporting Services With Sharepoint, Adding Bells and Whistles to Reporting services these were done by MVP Jessica Moss, PowerShell for Data Professionals by MVP Aaron Nelson (http://www.sqlvariant.com/) and Harnessing Power Pivot By Rushabh Mehta(Managing Director, Solid Quality India Private Limied), Intorduction to SSIS packages by MVP Andy Leonard(http://sqlblog.com/blogs/andy_leonard/default.aspx) . All the sessions were very information and interesting with a lot  of tips thrown in by professionals. It was definitely an eye opener for me with respect to PowerShell and PowerPivot with range and capabilties of these tools. Apparently there are more than 240 projects in codeplex for PowerShell, it was amazing to see the power of the powershell scripts. It also was evident from some of the presentations that Micorsoft is pushing sharepoint as a mjor presentation platform for BI. All the sessions I attended were packed to capacity with folks eager to know what is coming up next, of course the best thing was the whole event was free. This was my first sql saturday camp experience hoping to attend more in the future.

Friday, March 5, 2010

Team Foundation Power Tools

I use Visual Studio 2008 with Team foundation Server for all our projects. This includes .NET and BI based projects. Team Foundation Server is a very nice tool which allows for very good source control of the code base. One of my collegaues gave me an insight into the Team Foundation Power tools. This is really a cool tool and helps out immensely when you are working as part of a development team. Prior to installing this tool, the Visual Studio 2008 has to be complety closed down. Once the Power Tool is installed, open up Visual Studio and launch The team explorer. Once the team explorer comes up, it will list the team projects, within the team projects folder, there is a folder called team members. Once you expand the folders it will list the team members who are online/offline. One can righ click on the team meber, you will get the following options like: Show Check in history, Show Pending Changes and Show Shelvesets. The neat thing about this feature one can resolve any conflicts in Check in/out, get information of the pending changes which are going to be rolled into the codebase. There is an another feature called alerts which has different calssifications, where by one can get alerts. Here is a a list of events for which alerts can be setup. In a nutshell the Team Foundation Power Tools provided features to mange projects and builds effectively.