Search results matching tags 'SQL Server' and 'etl'

SSIS Design Patterns, the Book

andyleonard — Mon, 06 Aug 2012 16:37:43 GMT

For the past two years, I have had the honor and privilege or authoring SSIS Design Patterns alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current publication date is 27 Aug 2012 and I have high confidence in this date.

I take responsibility for publication delays and apologize to those who pre-ordered the book. The reasons for the delays are not important. I have built a career as a software developer and architect based on the following maxim:

Deliver quality late, no one remembers.
Deliver junk on time, no one forgets.

The shared goal of everyone working on this project has been to deliver quality. Proofing the manuscripts, I believe we have achieved that goal.

:{>

I Am Here To Help™

andyleonard — Fri, 19 Aug 2011 13:30:00 GMT

Introduction

This isn’t a trick. It’s not a gimmick. It’s not bait-and-switch. It is selfish; please allow me to explain:

I Am Here To Help™

This has been my motto for the past few years. How can I serve you today? Are you thinking about starting a User Group and don’t know where to begin? I can help. Are you planning an event and encountering obstacles? I can help. Having trouble getting an SSIS package to do what you want? I can help. Trying to troubleshoot a SQL Server issue? I can help. Have a question about raising chickens or guinea fowl? I can help.

For things with which I cannot personally help you, I know someone who can either answer your question, put you on the correct and best path for an answer, or who knows someone who can help.

“How Do I Contact You?”

There’s an Email link in the upper right corner of this blog. Or you can email me at andy.leonard@gmail.com. Or contact me on Twitter.

“Why is this Selfish?”

I’m glad you asked! Because I enjoy helping you. I believe everyone is put into life for a purpose, and serving you is my purpose. I am thrilled each time I get to help someone.

Helping others makes you feel awesome. People can give you money and things, but no one can give you the satisfaction that comes from contributing.

Helping others is contagious. I’ll talk about this more in a future installment of my series on Managing Teams.

You can get in on this too. How? Pull over next time you see someone stopped on the side of the road with the hood of their car up or standing next to a flat tire. Help someone struggling to load their groceries from the shopping cart into their car. Call someone and tell them you’re thinking about them. Add an encouraging comment to a blog post or send a friend or co-worker or enemy or competitor an email just to say “Hi” and catch up. If you have nothing left due to the economy – financial, emotional, intellectual, social, or any economy – offer the next person you see a smile. Smiles are free in every currency. Be nice.

Conclusion

Try it. And holler if you need a hand. I’m here for you.

:{>

Presenting at Hampton Roads SQL Server and SharePoint Users Group Tomorrow!

andyleonard — Tue, 19 Jul 2011 16:58:14 GMT

I am honored to present Designing an SSIS Framework to Hampton Roads SQL Server and SharePoint Users Group tomorrow, 20 Jul 2011! You can register here.

Details:

Infotec
100 Constitution Dr
Virginia Beach, VA 23462

Wednesday, July 20, 2011 at 6:30 PM (ET)

If you read this blog and will be there, introduce yourself! I’m the fat guy with a fu.

:{>

Try-N-Save SSIS Packages

Mike C — Mon, 29 Nov 2010 05:17:00 GMT

The Try-N-Save SSIS Packages from my SSIS Dimensional Data Optimization presentation are available at http://cid-8f7e6c950afc6b3e.office.live.com/self.aspx/.Public/Presentation/TryNSave.zip.

I'm still working on getting the sample database uploaded - even compressed a backup is larger than SkyDrive's upload filesize limit. I'll script it out when I have time (in addition to DDL, there are some tables that need to be prepopulated).

Presenting Part 4 of Building a Scalable, Enterprise-Class Data Warehouse for SQL Server 20 Oct 2010

andyleonard — Fri, 15 Oct 2010 11:00:00 GMT

I am honored to present Part 4 of 4 of Building a Scalable, Enterprise-Class Data Warehouse for SQL Server Wednesday, 20 Oct 2010!

This presentation features a demo of expressor 3.0 studio by Steve Frechette, expressor's Vice President of Engineering. It's going to be fun and I hope to see you there!

:{>

SQL Saturday #48 Recap

andyleonard — Mon, 04 Oct 2010 11:00:00 GMT

Introduction

I got to attend SQL Saturday #48 2 Oct 2010 and was honored to present.

"I'm Andy, and I'm a session-hopper." There. I said it. When I'm not presenting, I often wander between sessions. I want to learn a little something from everyone. Here are some sessions I wandered through and some notes from them:

Alejandro Mesa - Parameter Sniffing

I started in Alejandro Mesa's session on Parameter Sniffing. I actually got to speak with Alejandro before his session started. We spoke about family and the importance of feedback. Alejandro is cool. He's friendly and cares about delivering quality presentations. His passion for SQL Server and the community is evident - even if you only spend a few minutes with him.

I listened to the beginning of his session. In the portion I heard, he shared a good definition of parameter sniffing. He summarized the benefits and performance hit of parameter sniffing, and stated he would show how to detect and tune for various sniffing scenarios. I wanted to hear the remainder, but Andy Warren was presenting at the same time...

Andy Warren - DBA 101: The Basics

I skipped over to Andy Warren's (Blog | @sqlAndy) talk on DBA 101: The Basics. Andy is a great presenter who's passion for education and SQL Server are obvious to anyone with a pulse.

From what I heard Steve Jones (Blog | @way0utwest) wrote this presentation. There were 25 people in the room with 27 seats. The number of people in the room are a testament to the popularity of introductory material. It's a cool presentation to introduce people to SQL Server. Andy (and Steve) did a good job covering the objects of SQL Server: tables, views, stored procedures, and triggers. There were good questions from various attendees - evidence they were following and thinking about what they learned.

Introducing any topic in SQL Server in 60 minutes is tough. If you know someone brand new to SQL Server and databases in general, this is a good talk for them.

Julie Smith - Cool Tricks to Pull from your SSIS Hat

Meeting Julie Smith (Blog | @Datachix1) was a cool surprise for me. Julie is a great presenter with years of Business Inteliigence experience. Her presentation covered SSIS Expression Language and Variables. She did a good job with the "gotchas" of Variable scoping - accidentally creating a variable at a lesser scope than intended is a big-ee. She demonstrated the constraint options of the precedence constraint - which is a powerful and under-represented topic in many SSIS training sessions. I learned to use the Preview Pane in Windows Explorer!

I liked her presentation style and the material. Plus she provided some feedback on my SSIS Design Patterns talk that I will incorporate in future 60-minute versions of this talk.

Unfortunately, I needed to leave for the long trek back to Farmville before her session completed. I look forward to learning more cool SSIS tricks from Julie in the future!

Conclusion

SQL Saturday #48 was a huge success. The Columbia community responded to this awesome event by turning out in droves. I found the attendees of my sessions engaged - even right after lunch - and I received awesome questions throughout.

The organizers of the event are to be commended. They organizd an awesome event - from the Speaker Dinner through the After-Event party. About 90% of those in my sessions were attending their first SQL Saturday event.

:{> Andy

SSIS: Mo' Secure Configurations

Mike C — Mon, 18 Jan 2010 22:19:00 GMT

I ran into an issue the other day and I needed a solution for automically configuring my SSIS packages from securely stored DBMS connection strings. Problem is that most DBMSs don’t support Integrated Authentication—they require a username and password. Storing the username/password combo in the connection string in plain text is a security risk, so the question becomes an issue of storing this information securely.

There are several ways to store configuration data for SSIS packages. Probably one of the easiest and most popular methods is to use .dtsConfig files stored in the file system. This method presents some challenges when you want to secure usernames and passwords in the files. I won’t go into the details, since this post isn’t about .dtsConfig files, but Jamie Thompson does a great job of running down the .dtsConfig file options over at his old blog: http://consultingblogs.emc.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx.

After abandoning .dtsConfig files the idea of combining SSIS SQL Server configurations and cell-level encryption popped into my head. I created a quick proof of concept and tested it. Then SSIS guru Jamie Thompson pointed me over to a blog post from Larry Charlton (from way back in 2007!) at http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html that is very similar to what I came up with. Kudos to Larry for a nice piece of code. My concept is very similar to Larry’s, but with one major difference in implementation that I’ll cover below.

So the idea is that SSIS, by default, looks for a table named [dbo].[SSIS Configurations] to get its configuration data. This script builds the table:

CREATE TABLE [dbo].[SSIS Configurations]

(

ConfigurationFilter nvarchar(255) NOT NULL,

ConfiguredValue nvarchar(255) NULL,

PackagePath nvarchar(255) NOT NULL,

ConfiguredValueType nvarchar(20) NOT NULL

);

Here’s a breakdown of the columns that SSIS is looking for:

ConfigurationFilter holds a name you assign to the configured value. In my sample package I named my entry “Password Connection String”.
ConfiguredValue is the actual value. In the sample package this is the actual connection string with username and password.
PackagePath is the SSIS package property path. In the example the connection string value is used to configure the connection string of an OLEDB Connection named “Password Login Connection”. The path is “\Package.Connections[Password Login Connection].Properties[ConnectionString]”.
ConfiguredValueType tells SSIS the data type of your ConfiguredValue. In this case the data type is “String”.

Once you create the [dbo].[SSIS Configurations] table you can populate it with a connection string either through the SSIS designer or using a SQL INSERT statement like the following:

INSERT INTO [dbo].[SSIS Configurations]

(

ConfigurationFilter,

ConfiguredValue,

PackagePath,

ConfiguredValueType

)

VALUES

(

'Password Connection String',

'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

'\Package.Connections[Password Login Connection].Properties[ConnectionString]',

'String'

);

Note: if you use the SSIS designer in BIDS to populate the table with connection strings, it will strip out sensitive information like passwords.

You can use SQL Server security to restrict access to this table, but for my purposes that wasn’t good enough. Due to IT policies I specifically had to obscure the plain text of the connection string since it contained a password. For that I decided to turn to SQL Server cell-level encryption. The trick is that SSIS needs to be able to read the data directly from the table as plain text, but it shouldn’t be stored as plain text.

Larry’s implementation uses the T-SQL EncryptByPassPhrase and DecryptByPassPhrase functions, both of which require you to store a plain text passphrase somewhere to encrypt and decrypt your data and limit you to Triple-DES encryption. But the main problem I have with this method is that storing a plain text passphrase somewhere doesn’t solve the security issue—it just passes the buck. The same problem is evident when you try to encrypt SSIS packages by password.

Make no mistake, encryption key management is the hardest part of encryption—it’s a simple case of Turtles All The Way Down. Personally I don’t want to take on the responsibility for managing plain text passwords and passphrases—I’d rather let the server do that for me. So my solution needs to eliminate the need to store and pass plaintext passwords and passphrases to encrypt and decrypt the secure data.

To secure data using cell-level encryption first we need to do a little setup. The first step is to create a database master key (DMK), as shown below.

Note: always backup new encryption keys and certificates as soon as you create them!

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Ma5t3rK3yP@55';

Then we need to create a certificate that is secured by the database master key (DMK). For simplicity I named the certificate ConfigCert.

CREATE CERTIFICATE ConfigCert

AUTHORIZATION TestUser

ENCRYPTION BY PASSWORD = 'myT3stP@$$'

WITH SUBJECT = 'Configuration Encryption Certificate',

EXPIRY_DATE = '20201231';

ALTER CERTIFICATE ConfigCert

WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'myT3stP@$$');

Next we create a symmetric encryption key that’s protected by the certificate. I called the symmetric key ConfigSymKey.

CREATE SYMMETRIC KEY ConfigSymKey

AUTHORIZATION TestUser

WITH ALGORITHM = AES_256,

KEY_SOURCE = 'I am the very model of the modern major general',

IDENTITY_VALUE = 'Stella! Stella!'

ENCRYPTION BY CERTIFICATE ConfigCert;

Note: it’s a best practice to always specify a KEY_SOURCE and IDENTITY_VALUE in the CREATE SYMMETRIC KEY statement so you can recreate the same symmetric key when necessary. There is no BACKUP SYMMETRIC KEY statement.

Then we need to create a slight variant of the [dbo].[SSIS Configurations] table designed to store the encrypted configuration values, as shown below.

CREATE TABLE [dbo].[SSIS_Configurations_Encrypted]

(

ConfigurationFilter nvarchar(255) NOT NULL,

ConfiguredValue varbinary(512) NULL, -- This column changed

PackagePath nvarchar(255) NOT NULL,

ConfiguredValueType nvarchar(20) NOT NULL

);

The only difference between this table and the [dbo].[SSIS Configurations] table is the ConfiguredValue column. In the encrypted version of the table this column is varbinary instead of varchar, and it’s bigger than it’s unencrypted version. The reasons are that (1) all SQL Server encryption functions return varbinary data, and (2) the encrypted data will always be longer than the plain text.

The next step is to drop the [dbo].[SSIS Configurations] table and create a view with the same name.

DROP TABLE [dbo].[SSIS Configurations];

CREATE VIEW [dbo].[SSIS Configurations]

SELECT ConfigurationFilter,

CAST

(

DecryptByKeyAutoCert

(

Cert_ID(N'ConfigCert'), NULL, ConfiguredValue

) AS nvarchar(255)

) AS ConfiguredValue,

PackagePath,

ConfiguredValueType

FROM [dbo].[SSIS_Configurations_Encrypted];

The view uses the DecryptByKeyAutoCert function to automatically decrypt the ConfiguredValue column. This works because SSIS does not differentiate between the view and the table of the same name. This is a good thing, because it simplifies our process quite a bit. Our next step is to make sure that inserts and updates to the view properly update the underlying table with encrypted data. For this we create an INSTEAD OF INSERT, UPDATE trigger.

CREATE TRIGGER SSIS_Configurations_Trigger

ON [dbo].[SSIS Configurations]

INSTEAD OF INSERT, UPDATE

BEGIN

SET NOCOUNT ON;

-- SQL treats updates as a logical delete followed by an insert

-- So look at the deleted virtual table to see if anything was deleted

DECLARE @IsUpdate char(1) = CASE WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'N'

ELSE 'Y'

END;

-- Open the symmetric key

OPEN SYMMETRIC KEY ConfigSymKey

DECRYPTION BY CERTIFICATE ConfigCert;

-- Do a merge

MERGE [dbo].[SSIS_Configurations_Encrypted] AS target

USING inserted AS source

(

target.ConfigurationFilter = source.ConfigurationFilter

AND target.PackagePath = source.PackagePath

AND target.ConfiguredValueType = source.ConfiguredValueType

)

WHEN MATCHED AND @IsUpdate = 'Y' THEN

UPDATE SET target.ConfiguredValue = EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue)

WHEN NOT MATCHED AND @IsUpdate = 'N' THEN

INSERT

(

ConfigurationFilter,

ConfiguredValue,

PackagePath,

ConfiguredValueType

)

VALUES

(

source.ConfigurationFilter,

EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue),

source.PackagePath,

source.ConfiguredValueType

);

-- Close the symmetric key

CLOSE SYMMETRIC KEY ConfigSymKey;

END;

This trigger intercepts INSERT and UPDATE requests to the [dbo].[SSIS Configurations] view to automatically encrypt the ConfiguredValue column, which is then used to update the encrypted data in the underlying [dbo].[SSIS_Configurations_Encrypted] table. We can re-run the previous INSERT statement from the beginning of the article to test it.

INSERT INTO [dbo].[SSIS Configurations]

(

ConfigurationFilter,

ConfiguredValue,

PackagePath,

ConfiguredValueType

)

VALUES

(

'Password Connection String',

'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

'\Package.Connections[Password Login Connection].Properties[ConnectionString]',

'String'

);

A couple of quick SELECT queries reveal the results:

SELECT *

FROM dbo.[SSIS_Configurations_Encrypted];

SELECT *

FROM dbo.[SSIS Configurations];

The results from the first query show the ConfiguredValue is stored unreadable/encrypted. Querying the view returns the unencrypted plain text.

Making a clear fashion statement with the classic belt and suspenders, you can deny permissions to the underlying table and the view to those that don’t need to see it, and you can deny access to the symmetric key and certificate to those same folks. If someone gains access to the view or the table, they still need access to the encryption key and certificate to decrypt the contents. Without this access, anyone querying the view will see NULL in the ConfiguredValue column.

Sample scripts and SSIS package are attached to this entry.

UPDATE: Andy L. pointed out that [dbo].[SSIS Configurations] is just the default name SSIS uses for your configuration entries table. You can actually change it in the designer at design time, or in SQL Server at build time. The designer lets you choose which table to point at. The important thing is that the columns need to have the correct names.

A Day of SSIS Training 15 Oct 2009 at SQL Saturday #21 Orlando

andyleonard — Tue, 25 Aug 2009 11:00:00 GMT

I'm honored to be presenting a day of SSIS training entitled From Zero To SSIS on 15 Oct 2009 - the Thursday before SQLSaturday #21 (17 Oct 2009) in Orlando. I'm so excited about the opportunity! I've personally taught hundreds of developers to use SSIS and wrote courseware used to train even more. In this course, I use real-world data and cover SSIS development from a practical perspective.

I teach you how - and why - I develop SSIS the way I do.

I've also submitted a session on Database Development for the event proper. Here's hoping I get to meet a bunch of new Florida friends and catch up with some old Jacksonville friends!

:{> Andy

New SSIS 101 Article

andyleonard — Thu, 21 Aug 2008 14:20:00 GMT

My latest SSIS 101 article - SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers - is live at SQL Server Central today!

Enjoy!

:{> Andy