Category: SQL Server

Execute SSIS package as a Service Account

At my current client, I have a very specific set of parameters to work within regarding security. Chiefly, I need read permissions on the underlying database for a Microsoft Dynamics AX 2012 RS installation. I have a series of SSIS packages that extract data from AX on a daily basis, executed from SQL Server Agent. The AX database, however, is hosted on a remote server and neither myself or the client team have permissions directly on this box. Our only option for connecting to the SQL Server is via a service account which has db_datareader permissions on the AX database. So how to execute the Agent jobs running as the service account? By using a Credential and a SQL Agent Proxy.

Credentials
The first step is to set up a security Credential on the SQL Server that executes the jobs. These can be found under Security>Credentials in SSMS (see Figure 1). The Credential identity should be set to be the name (including the domain)of the Service Account, and must include the password. To create the Credential in T-SQL:

CREATE CREDENTIAL < CredentialName >
	WITH IDENTITY = '<domain>\<service account>'
		,SECRET = '<password>';
GO
Credentials
Figure 1

Proxies
Next, a Proxy for SSIS must be created in the SQL Agent. These are found in SQL Server Agent>Proxies>SSIS Package Execution (Figure 2). To create a proxy in T-SQL (note that subsystem_id 11 specifies that it is for SSIS Package Execution):

USE msdb;
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name = N'<ProxyName>'
	,@credential_name = N'<CredentialName>'
	,@enabled = 1;
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'<ProxyName>'
	,@subsystem_id = 11;
GO

 

Proxy
Figure 2

Agent Job
Finally, the Agent job needs to be set up to run as the Proxy. In the Run As drop-down in the relevant Job Step, select the newly created Proxy (see Figure 3).

Agent
Figure 3

References

Credentials
Create a SQL Agent Proxy
sp_add_proxy
sp_grant_proxy_to_subsystem

Try R, a free course

I’ve been branching out a bit in my spare time with regards to other tools and languages for manipulating and analysing data. The R language has interested me for a while, and I came across this free tutorial which serves as a pretty good introduction to its basic usage and syntax. It does so in a rather nice interactive web interface. Highly recommended if you have a spare hour or two to go through the course. As a plus, there is a link to a discount on the O’Reilly book Data Mashups In R once the course has been completed. R itself can be downloaded from here. Get stuck in fellow data geeks!Rlogo

Table variables, identity columns and reseeding

Ever tried to reseed an identity column in a table variable? No? Let’s give it a go…

Let’s declare a simple table variable, @country, and insert a few rows.

DECLARE @country TABLE
(
 CountryId INT IDENTITY(1,1),
 CountryName VARCHAR(50) NOT null
);

INSERT INTO @country
 ( CountryName )
VALUES
 ( 'Scotland' ),
 ( 'England' ),
 ( 'Wales' ),
 ( 'Northern Ireland' ) ;

SELECT
 CountryId ,
 CountryName
FROM @country ;

Truncating a normal table will remove all the rows and reseed the identity value. The following syntax is not valid for a table variable however:

TRUNCATE TABLE @country;

The following error would be returned: Incorrect syntax near ‘@country’.

We can remove all the rows from the table by using DELETE rather than truncate, but this does not reset the identity value. Can we use DBCC CHECKIDENT, like we would on a physical table? No…

DBCC CHECKIDENT (@country, RESEED, 1);

will return Must declare the scalar variable “@country”., and

DBCC CHECKIDENT ([@country], RESEED, 1);

will return Cannot find a table or object with the name “@country”. Check the system catalog.

Table variables cannot be dropped, nor can they be re-declared. In fact, trying to do the latter returns this error: “The variable name ‘@country’ has already been declared. Variable names must be unique within a query batch or stored procedure. The bold part of this message is important to note. Bear this in mind while we try to declare the table variable in it’s own scope:


DECLARE @i INT = 1,
 @count INT = 5;

WHILE @i <= @count
BEGIN

-- declare table variable in scope of the while loop
 DECLARE @country TABLE
 (
 CountryId INT IDENTITY(1,1),
 CountryName VARCHAR(50) NOT null
 );

INSERT INTO @country
 ( CountryName )
 VALUES
 ( 'Scotland' );

SELECT
 CountryId ,
 CountryName
 FROM @country ;

DELETE FROM @country ;

 SET @i = @i + 1 ;
END

The output is as follows:

Note that even though we declared the table every time the loop was executed, no error was returned (even though it was being declared in the same batch) and the identity column continued to increment.

So really, this is just an extremely long-winded way of saying…if you need to reseed an identity column and you are using a table variable, consider using a temporary table or a physical table instead!


					
					
			

T-SQL Tuesday: A Day In The Life

This month’s T-SQL Tuesday is hosted by Erin Stellato, and the theme is A Day In The Life. The premise is to track what you do over the course of a day as a SQL professional. My days can differ greatly, depending on the role I’m currently working in, and for what client. My current role is mainly database development and data migration work for an upgrade to an existing application. Last Wednesday went roughly something like this…

tsqltuesday

0900 – Getting set up for the day by preparing my to-do list. Then I caught up with work-related emails, sent a few replies back before switching Outlook off for a couple of hours – it’s a distraction. I then did the usual morning routine, checking out the latest code from Subversion and updating the database documentation. I also spent a couple of minutes on the SQL Server Central Question Of The Day – I find it leads me into work quite nicely, and I often learn something new.

0920 – Had a discussion with the lead .Net developer regarding some requested data model changes.

1000 – Started working on an SSIS package to migrate data from a legacy system into the main application database.

1130 – Updated system testing and user testing environments with new copies of static data.

1200 – The client Subversion repository had gone through some changes to separate development streams, so I moved all the database scripts to the relevant places. Red Gate Source Control…I can’t live without you!

1230 – Daily conference call to discuss progress and issues with the data migration.

1300 – Lunch (cottage pie – nice!), caught up on the days news and blogs.

1330 – Evaluating and designing the changes required for a new piece of functionality; then updating the relevant stored procedures before testing and documenting.

1600 – Made some logic changes to an existing stored procedure.

1700 – Started modelling some structural changes to a part of the database that was causing the developers some issues.

1715 – Load and performance testing conference call.

1745 – Home time!

I’m not sure that’s the most typical day for me – I’d say the work is more fragmented than normal. But it pretty much answers the question “What did you do all day?”.

Thanks to Erin for hosting T-SQL Tuesday this month!

Diving into T-SQL Grouping Sets

The Grouping Sets feature was added to the T-SQL GROUP BY clause with the release of SQL Server 2008. Despite its usefulness, I’ve not come across many (or any) of them in the wild at any of the clients I’ve worked at in the past few years. Here is a primer on how to use them, using the AdventureWorks OLTP database.

SQL Server 2005 Style

In SQL Server 2005, we could use the WITH ROLLUP syntax to add an aggregated total row:

SELECT
    st.Name ,
    SUM(soh.TotalDue) AS TotalDue
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom')
GROUP BY
	st.Name WITH ROLLUP;

WITH ROLLUP

The WITH ROLLUP and WITH CUBE features will be removed in a future version of SQL Server.

Basic Grouping Sets

With SQL Server 2008 onwards, we can use the GROUPING SETS syntax to achieve the same results. The grouping set is a defined grouping of data in a query. Each grouping set is defined by placing the columns in parentheses. An empty group, (), will show an aggregation across all sets. Here we define a grouping set for territory name, and an overall aggregation:

SELECT
    st.Name ,
    SUM(soh.TotalDue) AS TotalDue
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom')
GROUP BY
	GROUPING SETS ((st.Name), ());

Basic Grouping Set

Another way of expressing this is to use GROUP BY ROLLUP():

SELECT
    st.Name ,
    SUM(soh.TotalDue) AS TotalDue
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom')
GROUP BY
	ROLLUP (st.Name);

GROUP BY ROLLUP

Adding another column into the Grouping Set

Grouping Sets obviously become more useful when dealing with multiple columns for grouping. In this next query, we’ll add in a column for the order year, and define two grouping sets; one for the territory name and one for the year.

SELECT
    st.Name ,
    YEAR(soh.OrderDate) AS Yr ,
    SUM(soh.TotalDue) AS TotalDue
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom') AND soh.OrderDate >= '20070101'
GROUP BY
	GROUPING SETS ((st.Name), (YEAR(soh.OrderDate))) ;

Grouping sets with two columns

Let’s expand this out and amend the second set to include the territory name. This will return aggregates rows for both the name and year and the name alone.

SELECT
    st.Name ,
    YEAR(soh.OrderDate) AS Yr ,
    SUM(soh.TotalDue) AS TotalDue
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom') AND soh.OrderDate >= '20070101'
GROUP BY
	GROUPING SETS ((st.Name), (st.Name, YEAR(soh.OrderDate))) ;

Grouping Sets with two columns in one set

The overall aggregate can be returned by using the empty grouping set.

SELECT
    st.Name ,
    YEAR(soh.OrderDate) AS Yr ,
    SUM(soh.TotalDue) AS TotalDue
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom') AND soh.OrderDate >= '20070101'
GROUP BY
	GROUPING SETS ((st.Name), (st.Name, YEAR(soh.OrderDate)), ()) ;

Grouping sets with overall total

To group on everything, all columns and combinations can be defines in their own grouping sets:

SELECT
    st.Name ,
    YEAR(soh.OrderDate) AS Yr ,
    SUM(soh.TotalDue) AS TotalDue
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom') AND soh.OrderDate >= '20070101'
GROUP BY
	GROUPING SETS ((st.Name), (YEAR(soh.OrderDate)), (st.Name, YEAR(soh.OrderDate)), ()) ;

Using grouping sets to group on everything

Using the GROUPING function

We can identify whether a column has been aggregated by the use of grouping sets (or ROLLUP and CUBE) by using the GROUPING function. It will return 1 for aggregated or 0 for not aggregated. The following query uses this to identify if the territory name column has been aggregated:

SELECT
    st.Name ,
    YEAR(soh.OrderDate) AS Yr ,
    SUM(soh.TotalDue) AS TotalDue,
    GROUPING(st.Name) AS TerritoryGrouped
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom') AND soh.OrderDate >= '20070101'
GROUP BY
	GROUPING SETS ((st.Name), (YEAR(soh.OrderDate)), (st.Name, YEAR(soh.OrderDate)), ()) ;

Using the GROUPING function

Using the GROUPING_ID function

The GROUPING_ID function in T-SQL computes the level of grouping. The grouping columns can be passed in to return an INT which will show the level. For example:

SELECT
    st.Name ,
    YEAR(soh.OrderDate) AS Yr ,
    SUM(soh.TotalDue) AS TotalDue,
    GROUPING(st.Name) AS TerritoryGrouped,
    GROUPING_ID(st.Name, YEAR(soh.OrderDate)) AS GrpLevel
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom') AND soh.OrderDate >= '20070101'
GROUP BY
	GROUPING SETS ((st.Name), (YEAR(soh.OrderDate)), (st.Name, YEAR(soh.OrderDate)), ()) ;

Using the GROUPING_ID function

This can be used to filter rows on certain levels by using the HAVING clause:

SELECT
    st.Name ,
    YEAR(soh.OrderDate) AS Yr ,
    SUM(soh.TotalDue) AS TotalDue,
    GROUPING(st.Name) AS TerritoryGrouped,
    GROUPING_ID(st.Name, YEAR(soh.OrderDate)) AS GrpLevel
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom') AND soh.OrderDate >= '20070101'
GROUP BY
	GROUPING SETS ((st.Name), (YEAR(soh.OrderDate)), (st.Name, YEAR(soh.OrderDate)), ()) 
HAVING
	GROUPING_ID(st.Name, YEAR(soh.OrderDate)) = 3 ;

Using GROUPING_ID to filter rows

Another use of this is for labelling the grouping rows:

SELECT
    st.Name ,
    YEAR(soh.OrderDate) AS Yr ,
    SUM(soh.TotalDue) AS TotalDue,
    GROUPING(st.Name) AS TerritoryGrouped,
    CASE GROUPING_ID(st.Name, YEAR(soh.OrderDate)) 
		WHEN 0 THEN 'Territory & Year Total'
		WHEN 1 THEN 'Territory Total'
		WHEN 2 THEN 'Year Total'
		WHEN 3 THEN 'Overall Total'
    END	AS GrpLevel
FROM
    Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
WHERE st.Name IN ('Germany', 'United Kingdom') AND soh.OrderDate >= '20070101'
GROUP BY
	GROUPING SETS ((st.NAME), (YEAR(soh.OrderDate)), (st.Name, YEAR(soh.OrderDate)), ()) ;

Using GROUPING_ID to label rows

Grouping sets and grouping functions add some powerful functionality to the standard GROUP BY clause. Let’s hope I see them being used more in the future. :)

References and further reading:
SQL Server Books Online: GROUP BY
SQL Server Books Online: GROUPING
SQL Server Books Online: GROUPING_ID

Case-sensitivity in a T-SQL WHERE clause

I was asked today if it was possible to use a case-sensitive LIKE statement in a WHERE clause. The default SQL Server collation is case-insensitive, but the collation used can be specified in a query. To return the collation of the current database:

SELECT DATABASEPROPERTYEX(DB_NAME(),'Collation');

In my case, this returned Latin1_General_CI_AS. The case-sensitive version of this is Latin1_General_CS_AS. To use this collation for a column, specify it after the column using the COLLATE statement. For example, I have a table called Groups, with 2 rows. The first row has a GroupName of “Admin”, and the value in the second row is “Standard”. The following query returns both rows:

SELECT GroupID ,
    GroupName
FROM dbo.Groups
WHERE GroupName LIKE '%a%'

Results:

GroupID	GroupName
1	Admin
2	Standard

However, when we use the COLLATE statement to use the case-sensitive collation, only the second row is returned:

SELECT GroupID ,
    GroupName
FROM dbo.Groups
WHERE GroupName COLLATE Latin1_General_CS_AS LIKE '%a%'

Results:

GroupID	GroupName
2	Standard

Practical uses for this could include exact string searches and password matching.

T-SQL to drop all Foreign Keys that reference a table

In a recent data migration project, I had a task to re-number some primary key columns for certain tables, so that there was no key overlap when a number of systems were combined. Part of this process involved identifying and dropping all the foreign keys that referenced the primary key on the tables that were being changed, then adding them again after the updates were completed. To save a bit of time, I knocked up this little script to generate the DROP and ALTER statements for the referencing foreign keys for a given table.

SELECT
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
		+ ' DROP CONSTRAINT ' + fk.NAME + ' ;' AS DropStatement,
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
	+ ' ADD CONSTRAINT ' + fk.NAME + ' FOREIGN KEY (' + COL_NAME(fk.parent_object_id, fkc.parent_column_id) 
		+ ') REFERENCES ' + ss.name + '.' + OBJECT_NAME(fk.referenced_object_id) 
		+ '(' + COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) + ');' AS CreateStatement
FROM
	sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.schemas s ON fk.schema_id = s.schema_id
INNER JOIN sys.tables t ON fkc.referenced_object_id = t.object_id
INNER JOIN sys.schemas ss ON t.schema_id = ss.schema_id
WHERE
	OBJECT_NAME(fk.referenced_object_id) = 'account'
	AND ss.name = 'dbo';

A Couple Of T-SQL Quickies

It’s been a while since I’ve posted any SQL Server content, so in a bid to get back into the swing of things, here are a couple of very simple, but useful, T-SQL commands. I’ve used these recently in a large project I’m working on.

Identify all tables with no Foreign Key constraints:

select distinct
t.name
from sys.tables t
left outer join sys.foreign_keys fk on t.object_id = fk.parent_object_id
where fk.object_id is null
order by t.name;

List all tables in a database, with row counts

select t.name ,
ps.row_count
from sys.dm_db_partition_stats ps
inner join sys.tables t on ps.object_id = t.object_id
where ps.index_id < 2
order by t.name;

Powershell Deployment Script Update

Since last weeks post on automated DB and ETL deployment builds with Powershell, I’ve made a couple of small but useful changes to the Powershell script.

The distinct parts of the script are now encapsulated in parameterised functions. The end goal of this is to be able to turn the main script into a Powershell Module that could be installed and used when required. So now there are three functions, GetHeadFromSVN, SyncDB and DeployPackages.

DeployPackages has been changed to make it quicker and more streamlined. It will now only deploy or re-deploy packages that have been changed in the last hour, rather than every package in the repository.

The functions are as follows:

# Get latest DB scripts and SSIS packages from SVN
function GetHeadFromSVN
{
    param($svnpath, $svnurl, $destinationfolder)

    Set-Location $svnpath
    svn checkout $svnurl $destinationfolder
}

# Use Red Gate SQL Compare to deploy schema changes to each DB
function SyncDB
{
    param($sqlcomparelocation, $argfile)

    Set-Location $sqlcomparelocation

    Invoke-Expression ".sqlcompare /ArgFile:$argfile"
}

# Deploy SSIS packages
function DeployPackages
{
    param($filepath, $ssisfolder, $server, $dtutilpath)

        $dtutil = $dtutilpath
        Set-Location $filepath

       # Only deploy packages that have been changed in the last hour
        $files=get-childitem *.dtsx | where {$_.LastWriteTime -gt (Get-Date).AddHours(-1)}

        if ($files -ne $null) {
        foreach ($file in $files) {
            $basename=$file.BaseName
            $deletearguments = " /delete /SQL $ssisfolder/$basename /SourceServer $server"
            $deployarguments = " /file $file /copy SQL``;$ssisfolder/$basename /destserver $server"

            Set-Location $dtutil

            Invoke-Expression "dtutil $deletearguments"
            Invoke-Expression "dtutil $deployarguments"
         }
    }
}

Here are some examples of how they would be called:

GetHeadFromSVN "C:Program FilesSlikSvnbin" http://<svnserver>/repos/bi/Database "C:NightlyBuildsBISVNDatabase"
GetHeadFromSVN "C:Program FilesSlikSvnbin" http://<svnserver>/repos/bi/ETL/branches/Phase2 "C:NightlyBuildsBISVNETL"

SyncDB "C:Program FilesRed GateSQL Compare 9" "C:NightlyBuildsBIConfigurationStagingCompare.xml"

DeployPackages "C:NightlyBuildsBISVNETLSourceToStage" "SourceToStage" "<target server>" "C:Program FilesMicrosoft SQL Server100DTSBinn"
DeployPackages "C:NightlyBuildsBISVNETLStageToWarehouse" "StageToWarehouse" "<target server>" "C:Program FilesMicrosoft SQL Server100DTSBinn"

The Codeplex project page has been updated with the latest code.

Automating DB and SSIS builds from source control

This week I’ve been working on a new branch of a data warehouse project. To make deployment to development environments easier I decided I’d attempt to get some sort of continuous integration/automated builds to our dev server up and running. Doing this sort of thing has never been particularly straightforward for database projects, but with the fantastic SQL Compare command line tools and SQL Source Control from Red Gate, this task is made so much easier.

I wanted something relatively simple (as always), something that meant not having to install a framework. I did look at TeamCity, NAnt and MSBuild, but decided against using any of these, at least in the short term. My reasoning being that something light and fairly portable with as few dependencies as possible would be more useful at this stage.

The intention was to set up a nightly build job that would:

  1. Get the latest code from the development branch from SVN
  2. Deploy any schema changes to the development databases
  3. Delete and re-deploy the SSIS packages for the ETL

I used the excellent Continuous Integration article from Red Gate to get me going. I had started to implement this using a batch file (yuk!), but quickly moved onto using PowerShell, my new favourite toy on Windows! The examples in each section will show the PowerShell commands used in the script.

Setup and Configuration

Working locally initially, I created the following folder structure on the C drive:

  • NightlyBuilds
  • — BI (the project name)
  • —— Configuration (to hold any configuration files)
  • —— SVN (for the SVN checkout)
  • —— Output (for any output or report files)

Source Control

Our development team use dedicated local databases and push changes to the centralised Subversion (SVN) repository using Red Gate SQL Source Control. The first step in the build was to check out the latest SQL schema scripts and SSIS packages. After installing the SlikSVN client, I could use the following Powershell commands to perform the check out:

Set-Location "C:Program FilesSlikSVNbin"
svn checkout http:///repos/BI/Database "c:NightlyBuildsBISVNDatabase"
svn checkout http:///repos/BI/ETL/branches/Phase2 "c:NightlyBuildsBISVNETL"

Deploy Schema Changes

Next up was to use the SQL Compare command line tools to compare each of the databases on the development environment with the schema scripts.

Set-Location "c:Program FilesRed GateSQL Compare 9"
Invoke-Expression ".sqlcompare /ArgFile:C:NightlyBuildsBIConfigurationEDWCompare.xml"
Invoke-Expression ".sqlcompare /ArgFile:C:NightlyBuildsBIConfigurationStagingCompare.xml"

At the moment, I’m using XML argument files for each comparison (this may well change as this progresses), these have a format like:

C:NightlyBuildsBISVNDatabaseEDWbranchesPhase2
server name
    EDW
default
C:NightlyBuildsBIOutputEDWSchemaDiffReport.html
Interactive
C:NightlyBuildsBIOutputEDWSchemaSyncScript.sql
user
role

Deploy SSIS Packages

With the databases being up to date, the next task was to deploy the SSIS packages. Having experienced a couple of issues in the past with packages not being overwritten correctly, I decided that, to be on the safe side, I’d delete an existing package before re-deploying it. The command line tool dtutil.exe lets us perform these tasks and more, but it can be incredibly frustrating to use at times, with it’s inconsistent syntax and problems with differences in SQL Server versions and 32 and 64 bit versions. Using the following function, the path to the SSIS packages is specified and each one is deleted on development, then redeployed.

function DeployPackages 
{
    param($filepath, $ssisfolder, $server)

    Set-Location $filepath

    $files=get-childitem *.dtsx

    foreach ($file in $files) {
        $basename=$file.BaseName
        $deletearguments = " /delete /SQL $ssisfolder/$basename /SourceServer $server"
        $deployarguments = " /file $file /copy SQL``;$ssisfolder/$basename /destserver $server"

        Set-Location $dtutil

        Invoke-Expression "dtutil $deletearguments"
        Invoke-Expression "dtutil $deployarguments"
    } 
}

DeployPackages "C:NightlyBuildsBISVNETLSourceToStage" "EDW/SourceToStage" ""
DeployPackages "C:NightlyBuildsBISVNETLStageToWarehouse" "EDW/StageToWarehouse" ""

Improvements

So that, in a nutshell, is the process I’m currently using. It works, which is nice, but I have quite a few improvements in mind:

  • Firstly, it seems to be very slow when running dtutil. I’m not sure if this is because I’m currently doing everything over a VPN, but I’ll be looking to improve the general performance. To be honest, it’s not a major issue, as it’s mainly designed for builds that happen overnight, but it would be nice if I could streamline it. I did try to use the /fdelete switch on dtutil to remove an entire folder, but that demands that the folder is empty.
  • Add more parameterisation so that it could be used for different projects on different environments
  • Use the SQL Data Compare command line tools to ensure any static/lookup data is up to date
  • Add an option to drop (if required) & create the databases and SSIS folders. This would allow the script to deploy to “virgin” machines and test the entire build.
  • Add job creation scripts
  • Build in SSRS report deployment. The project I’m working on does not use a great deal of SSRS reports (OBIEE is the end user tool), but given that report deployment can be a pain, it would be nice to include this too.
  • Add some reporting as to the success or failure of any component of the script.

Update: Looks like the VPN was indeed causing a slowdown. Running it connected locally to the network, 60+ SSIS packages were deployed in <3 minutes.

Code

I’ve started a project on Codeplex for this, really just as a place to keep the code. Any suggestions or comments are, as always, more than welcome.