Archive for May, 2012

What’s Inside an SSIS 2012 .ispac File?

If you’re curious, you can rename the .ispac file and give it a .zip extension, and extract. Voila, we see all the files that make up the ispac – a manifest, your DTSX files, and a .param file. Also note that the manifest does not have the usual uber-long .ssisDeploymentManifest extension

VN:F [1.9.22_1171]
Rating: 10.0/10 (10 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)

Get a Report Handle Using SSRS2008 and PowerShell V3

In order to work with report items with SQL Server Reporting Services 2008, most of the time you need to get a handle to the report items.

Here is a sample code snippet that uses the ReportingService2010 web service and PowerShell V3 CTP.

 
cls
 
#replace this with your reportserver web service URI
$ReportServerUri  = "http://localhost/ReportServer/ReportService2010.asmx";
 
$proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential ;
 
#we'll use the ListChildren method to navigate through all your SSRS items
#the $true parameter specifies recursive navigation
$catalogitemsarray = $proxy.ListChildren("/", $true);
 
$reportpath = "/Customers/Customer Contact Numbers";
$report = $null;
 
foreach ($catalogitem in $catalogitemsarray)
{
   if($catalogitem.Path -eq $reportpath )
      {
         $report = $catalogitem;
      }
}
 
#display properties
$report

Sample result is as follows:

ID                    : 15b3dd87-d0de-43a0-8692-030dcfdab945
Name                  : Customer Contact Numbers
Path                  : /Customers/Customer Contact Numbers
VirtualPath           : 
TypeName              : Report
Size                  : 23870
SizeSpecified         : True
Description           : 
Hidden                : False
HiddenSpecified       : False
CreationDate          : 5/13/2012 12:13:48 AM
CreationDateSpecified : True
ModifiedDate          : 5/13/2012 12:13:48 AM
ModifiedDateSpecified : True
CreatedBy             : KERRIGAN\Administrator
ModifiedBy            : KERRIGAN\Administrator
ItemMetadata          : {}

VN:F [1.9.22_1171]
Rating: 10.0/10 (2 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Dynamically Generate Database Snapshot Creation Script

Just a short snippet to dynamically generate a database snapshot script. Could be useful especially if your database has multiple files and/or filegroups:

-- run with "Results in Text" mode
DECLARE @dbname VARCHAR(100) = 'SampleDB'
 
-- we will timestamp both the snapshot name and the files
DECLARE @currdate VARCHAR(20)
SET @currdate = '_' + CONVERT(VARCHAR(20), GETDATE(), 120)
SET @currdate = REPLACE(@currdate, ':', '')
SET @currdate = REPLACE(@currdate, '-', '_')
SET @currdate = REPLACE(@currdate, ' ', '_')
 
DECLARE @SQL VARCHAR(MAX) 
SET @SQL = ''
SELECT
   @SQL = COALESCE(@SQL + ('(NAME=' + [name]
                           + ',FILENAME=''C:\Temp\Snapshots\' + [name]
                           + @currdate + '.ss''),'), '')
FROM
   sys.database_files
WHERE
   type_desc = 'ROWS'
ORDER BY
   [file_id] 
 
    -- need to remove last comma
    SET @sql = LEFT(@sql, LEN(@sql) - 1)
SELECT
   'CREATE DATABASE '+ @dbname+ @currdate+' ON ' + @sql + 
   ' AS SNAPSHOT OF ' + @dbname
VN:F [1.9.22_1171]
Rating: 8.9/10 (13 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)
`