SSIS – Change the Protection Level for all Packages in a Project

Here’s a quick one I found this morning while fixing an execution issue with one of my SSIS packages. I needed to change the Protection Level of all the packages in a project from Encrypt With User Key to Don’t Save Sensitive. The project contains over 30 packages, and I didn’t really fancy opening each one, navigating to the properties and amending the Protection Level for each one – that’s too time-consuming and frankly, boring. I initially figured I’d write a PowerShell or Python script to amend the relevant XML in each of the .dtsx files. But a quick Google came up with this MS Technet page, which details how to make the change via the command line, for both individual packages and for all packages within a project.

A quick amendment to the command to use the value for DontSaveSensitive (0) and to remove the password parameter was all that was required, so running this…

for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;0

…and pressing Y to confirm each change as prompted, resulted in the change being made to all packages. Result! Well almost. After going back to Visual Studio and changing the Protection Level of the Project (it must match that of the Packages), I tried to build the solution. It returned an error stating that for all the Packages, the Protection Level was incorrect and had to match the Project. But I’d just changed all of that! Turns out I had to open all the packages in Visual Studio (for some reason), and then it would build without making any further changes. Odd.

While this is useful to know for future reference, I’m much more likely to be developing SSIS projects using BIML, which would make changes like this far easier, as it would be a simple change in the BIML template, re-generate the packages and job done. It’s the future…

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

Highlights of 2012

I don’t consider 2012 to have been a great year personally, I’m quite glad to see the back of it. But I do have to join in with almost everyone else in the blogging world and post my highlights of the year. First up of course, is albums…

Records of the year

  1. Mid Air – Paul Buchanan (Mid Air on Youtube)
  2. The Devil’s Debt – Love & Money (The Devil’s Debt on Youtube)
  3. Now for Plan A – The Tragically Hip (Goodnight Attawapsikat on Youtube)

Books of the year (not necessarily published this year, but read by me in 2012)

  1. No Country for Old Men – Cormac McCarthy
  2. A is for Angelica – Iain Broome
  3. The Deep Dark Sleep – Craig Russell

Tech books of the year

  1. DBA Survivor – Thomas LaRock
  2. How to Become an Exceptional DBA – Brad McGehee
  3. Pro SQL Server 2012 Practices – a ton of great SQL folks (still reading this but it’s the business)

Beers of the year

  1. Hit The Lip – Cromarty Brewing Co
  2. Magic 8 Ball – Magic Rock Brewing
  3. A Face With No Name – Tempest Brewing Co

Sporting moment

The 49ers beating the Saints in the playoffs courtesy of this last-gasp Alex Smith touchdown pass to Vernon Davis.

Personal highlights and goals

  1. Finally getting my degree through the Open University
  2. Running 5k for the first time ever (using Couch To 5k)
  3. Reading 50 books in the year – thanks to my Kindle!
  4. Meeting one of my music heroes, Nile Rodgers, at the Edinburgh Book Festival2012 08 22 11 55 29

NFL Predictions

It’s September, it’s football time! The best time of year, it’s a long time since February. Although the season kicked off on Wednesday night with the Cowboys beating the Superbowl champion Giants, I decided I’d predict every regular season game and see who I think will be the eventual winners. The standings came out as follows:

AFC East

  1. *NE – 12 and 4
  2. *BUF – 9 and 7
  3. NYJ – 7 and 9
  4. MIA – 5 and 11

No surprise that I think the Patriots will win this division handily. I fancy Buffalo to have a good season with some nice additions on defense, enough to see them sneak into the playoffs.

AFC North

  1. *BAL – 11 and 5
  2. PIT – 9 and 7
  3. CIN – 6 and 10
  4. CLE – 3 and 13

Baltimore are the class of this division still. The Steelers and Bengals to take a slight backwards step. It’s the Bengals way!

AFC South

  1. *HOU – 10 and 6
  2. TEN – 8 and 8
  3. JAX – 4 and 12
  4. IND – 4 and 12

I think the Texans will make the playoffs for the second time, they look easily the best team here. Andrew Luck will have a decent, but ultimately frustrating, and losing, season with the Colts.

AFC West

  1. *KC – 11 and 5
  2. *SD – 10 and 6
  3. OAK – 7 and 9
  4. DEN – 7 and 9

Peyton Manning and the Broncos are making the headlines in the pre-season, but my bet is for the Chiefs to win this competitive division. The Chargers will make the playoffs via the wild card, leaving the Raiders and Broncos disappointed.

NFC East

  1. *PHI – 12 and 4
  2. *NYG – 10 and 6
  3. DAL – 9 and 7
  4. WAS – 4 and 12

I’m picking the Eagles to finally live up to their potential in 2012. The Giants will sneak in again, while Tony Romo and RGIII stay at home in January.

NFC North

  1. *GB – 13 and 3
  2. *CHI – 12 and 4
  3. DET – 8 and 8
  4. MIN – 4 and 12

Yet again the Packers are the class of the NFC, although the Bears will run them close. The Lions will struggle to keep up and miss out on another playoff trip.

NFC South

  1. *ATL – 13 and 3
  2. CAR – 10 and 6
  3. TB – 7 and 9
  4. NO – 6 and 10

This is my big shock division. I think the Falcons will take off this season, and the Panthers will make a run at the playoffs, but miss out to the Giants on the tie-breakers. The Bucs will improve, but I think the Saints will struggle in the big games without Sean Payton and end up with a losing record.

NFC West

  1. *SF – 9 and 7
  2. SEA – 8 and 8
  3. ARI – 5 and 11
  4. STL – 3 and 13

A horrendous schedule will see my Niners win four less games than last season, but still win a poor division. Seattle will be competitive but the Cards and Rams will want to forget this one, the latter being rewarded with the first pick in the draft.

Playoff Predictions

Wildcard Round

NYG at PHI – PHI win
CHI at SF – CHI win
BUF at KC – KC win
SD at HOU – HOU win

Divisional Round

PHI at ATL – PHI win
CHI at GB – GB win
KC at BAL – BAL win
HOU at NE – NE win

Championship Round

PHI at GB – GB win
BAL at NE – BAL win

Superbowl

GB vs BAL – GB win

So, I have the Packers to win it all. Really can’t see past them this season. Let’s see how much I get wrong!

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';