Archive for March 10th, 2012

SQLSaturday #114 in Vancouver – PowerShell and ETL with SSIS

SQLSaturday #114 in Vancouver is coming up. It will be held at the BCIT Burnaby Campus – SW buildings. Have you registered yet?

If you haven’t, you can still register! I love SQLSaturdays. I personally enjoy attending them a lot, and wished there were more SQLSaturdays in the Vancouver vicinity so I can attend more. Although if I had the time and money, I’d fly to other SQLSaturdays too (did you know there’s a SQLSaturday in Honolulu, Dublin and Portugal?).

If you’re in the database/SQL Server field, you should have plenty of reasons to attend. There’s lots of awesome stuff to learn, especially since SQL Server 2012 has just launched last March 7, 2012! Plus, this is a free training event. Yup, you read it right, it’s a free awesome event. One day of SQL Server training can cost you a few hundred dollars, but SQLSaturdays are packed with different sessions, and you don’t even have to worry about the dollar cost.

Here are the event details as posted at the SQLSaturday site:

Who:
YOU! All you have to do to get involved is register online to reserve your spot. Show up and attend the sessions that you choose.
What:
This is a FREE one day training event for SQL Server developers, administrators and other data professionals.

When:
Saturday, March 17, 2012. Register online now to reserve your spot. Attendee check-in will begin at 7:30 am until 9:00 am. The first sessions of our four tracks begin at 9:30 am.

Where:
BCIT South-West Buildings
3700 Willingdon Avenue
Vancouver, BC V5G 3H2
Canada

I have a couple presentations. As usual, I am both pretty excited and nervous.

ETL with SSIS for Beginners

You’ve heard of the term “ETL” before, but you’re not quite sure what it really is and how to do it. In this session we’ll demystify this so-called ETL. Don’t worry, no previous experience required. We’ll work our way from getting your data from your sources, to cleaning and reformatting them, and loading them to your data warehouse. We will also look at some standards and conventions you should consider when doing ETL to simplify your process, and save you some headaches in the future. Of course, our go-to ETL tool is SSIS.

You did what with PowerShell?!

PowerShell is cool. And powerful. Don’t believe me? See it for yourself. Come to this session, and I will show you cool things we can do with PowerShell – ranging from backing up your servers, working with BLOBs and XML, to automating SSRS report deployments and downloads, and even integrating with SSIS. We’ll work with different facets of SQL Server from a PowerShell perspective.

See you there!

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

Insert XML files to SQL Server using PowerShell

One way to bulk upload your XML files into SQL Server is by using PowerShell. In the script below I am using SQL Server 2012 and PowerShell V3 (CTP).

Note that you can use the script in SQL Server 2005/2008/R2 and PowerShell V2, with some slight changes in syntax.

I use Invoke-Sqlcmd to get this done.
First up, I have a prep T-SQL script file that I use to create my tables:

$instanceName = "KERRIGANSQL01"
$databaseName = "SQLSaturday114"
$prepfile = "C:presentationsSQLSaturday114PowerShellStuff.Table.sql"
Invoke-Sqlcmd -ServerInstance $instanceName `
-Database $databaseName -InputFile $prepfile

Nothing fancy about this table. Just a basic table with an XML field:

IF OBJECT_ID('PowerShellStuff') IS NOT NULL
DROP TABLE PowerShellStuff
GO
 
CREATE TABLE PowerShellStuff
(ID INT IDENTITY(1,1) NOT NULL,
 FileName VARCHAR(200),
 InsertedDate DATETIME DEFAULT GETDATE(),
 InsertedBy   VARCHAR(100) DEFAULT SUSER_SNAME(),
 XMLStuff XML,
 BLOBStuff VARBINARY(MAX)
)

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.5/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)
`