Steven Wright Blog

As mentioned in the previous post: Automating building of SSIS projects , I am working on integrating our SSIS projects into our continuous build/deployment process. In this post, I will cover my implementation of automating the deployment of SSIS projects. Our continuous delivery pipeline uses the Octopus Deploy platform which is a release management; automated deploy and operation runbook platform that consists of a central server and deployment agents referred to as tentacles. The tentacles receive the deployment packages, which contains the code being deployed, and runs the deployment scripts. The server is where all the deployment packages are stored; deployment projects are maintained and the management of the following: infrastructure, users and server. Octopus deployment projects are defined as steps from one of the following templates types: Built-in, custom or Community ...

As mentioned in a previous post I've recently been working with a SQL Server unit test framework: tsqlt. I would like to share how I managed to integrate the framework and unit test into my project life-cycle. Unit tests should be treated the same as application code, meaning they should be contained within a project under source control. Being under source control will allow the tests to be maintained and changes tracked when multiple people are helping to write tests.  Also having the unit test in a project means there is a deployment mechanism out-of-the-box. The tsqlt framework has a few requirement which are as follows CLR is enabled on the SQL Server The framework and the unit test are contained within the same database of the objects which are being tested The database instance is trustworthy As unit test are required to be contained within the same database as the objects being tested, also due to the way SQL Server Data Tools (SSDT) project validation works, th...

I have been working with the new SQL server data tools database project. This is going to be the successor to database professional tool, also known as data dude. There have been several differences to how this product works. One of them is the new tool project folder structure. There is now no folder structure within the project which means any file is automatically created into the root folder of the project. This is unlike database professionals, which had 2 comprehensive folder structures, the options were: by object type folder tree or schema related folder tree. My preference was always the object type folder structure, were script files which contain similar objects were all stored in the same folder e.g.: tables under a table folder, primary keys under the keys folder, views under a view folder etc. If you missed the object tree folder structure, like I have, there is a method to create this structure very quickly within your new data base project by using the following steps...

I was asked to help create a maintenance plan for a SQL server . I chose to implement the plan using PowerShell for the following reasons:  PowerShell is a procedural scripting language and the maintenance plan would have a few procedural steps and iterations through objects. PowerShell was also built on top of the .Net framework. This means I could make use of the SQL Shared Management Object (SMO) library within my script. SQL Server also has a SQL PowerShell provider , which is pre-loaded when you run SQLPS. Support also has been extended to SQL Server Agent as there is a native PowerShell, which uses SQLPS, job subsystem. I have been using PowerShell for a while and found it really useful; it allows you to solve problems, much more easily, than using the standard SQL tools. A copy of my script, and related scripts, are available from my SkyDrive using the following link: OneDrive The maintenance script, SQLServerMaintanceTask.PS1 , does the following, depending o...

Table of Contents 1   Introduction. 2   Automation process components   2.1 The Build.   2.2 The Deployment script.   2.3 Automated deployment and execution. 3   Visual studio tips.   3.1 Reducing unresolve reference errors.   3.2 Composite Projects. 4   Resources. 1 Introduction As I have mentioned in previous posts I have been working on a data warehouse project. One of my main roles in this project was to work on the build, deployment and development processes. Within this post, I would like to show how I, with help from my colleagues, was able to automate a build, deployment and execute the data warehouse code. The reason for developing this automation was to try and find problems before we released to user acceptance testing (UAT). Once this automation started running it did capture errors. One error in particular, which would only appeared in release, was missing permissions. As the developers a...

Introduction I have, as mentioned in a previous blog , been working on a data warehouse project using CDC for extracting the source system data. I would like to share some of the experiences and evaluation criteria used for selecting a CDC tool for our project. The reason for using a specialist tool was as follows: Couldn't move all the source databases to SQL Server 2008 Needed the capture change data to be sent to another server instance A consistent management experience for CDC between all our source systems. The two database vendors the tool needed to support was SQL Server and IBM AS400 Timescales First we underestimated how long it would take to select a CDC tool. We plan for 1 month, for both evaluation criteria and running the tests, and it took us about 2/3 months. The time was taken in evaluating the tools against the criteria and gaining access to database systems from other projects. However our development of the ETL solution was able to continue, while t...