Just a quick note, folks: I've released the MidnightDBA Blogcast 006 (direct link to Blogcast 006 MP3) - I read a short blog from Sean titled "Flatline", prefaced by a couple of notes on the January NTSSUG meeting, and SQL Saturday Dallas.

Also, I haven't published the link on the site yet, but Sean's Ground Zero SSIS class video is now available online! This is the talk from last Thursday's North Texas SQL Server User Group meeting.

Edit: Two more updates. First, the North Texas SQL Server User Group has a new Resources page, with group discounts, training resources, and reference materials!  AND, Sean has made some new renovations to MidnightDBA.com, including a smaller moon (for those of us with normal sized monitors) and a Twitter feed.  Awesomesauce!

Happy days,

Jen McCown

http://www.midnightdba.com

MVP and author Brian Knight - according to his Pragmatic Works bio - is the co-founder of SQLServerCentral.com, JumpstartTV.com, and is on the Principal Board of Directors of PASS. But who is the man behind the cape?  We sit down and discuss the 2012 end of the word with Brian Knight, and how we can profit.  Other highlights:

• He comes from the Dot Com....
• On his home: "We grow rednecks."
• Me: "What we need is a worldwide panic!"
• On Coke: "I dig it."
• Brian started Pragmatic Works essentially because he was bored.
• Hah, at 6:50 in the video a phone starts buzzing, and I thought it was mine.
• "Fun and controversial? That's us!"
• Two words: "Custom pharmecuticals."  Nono, it's a sample database, people....
• We push our video interview with Itzik. We agree he's not a dumb guy, and have an awkward moment of silence.
• We have very very fond words for Brian's latest book, Microsoft SQL Server 2008 Integration Services Problem-Design-Solution
• "I talked to Brian Knight, and he said you suck."

And that's just part 1 of 2.  Go watch it, you know you want to.

-Jen McCown, http://www.MidnightDBA.com

For the record, Sean's wearing his "My database isn't small..." shirt, and I'm sporting the "My database can beat up your database" tee. 

To quote Dave Barry, I swear I'm not making this up.  We just got a lovely email from - dare I say it? A fan!

Just a note to say "thank you" for creating your midnightdba website, chock full of awesome SSIS videos! 

I work heavily with T-SQL programming and SSRS 2005, but haven't touched SSIS. In fact, any time I tried to dive into SSIS training my motivation lagged significantly -- I just couldn't envision the possibilities of SSIS even though I know full well that it's an ETL tool. Then, a couple of weeks ago I came across "midnightdba" -- it all made sense to me. Your videos provided the clarity and purpose I've been needing to get learning/working with SSIS.

Peter here then goes on to make a video request, which is quite a rarity. We love video requests (though sometimes, as in the case of the lady who requested Silverlight tutorials, they can be difficult for us to accomodate).

BIG thanks to Peter for the feedback and request!

-Jennifer McCown, http://www.MidnightDBA.com

P.S. Sean's had some trouble with the new site code this week, but (being the soopergenius he is) he has it all straightened out. Keep an eye out this weekend for new vids, including at least one new DBAs @ Midnight.

So, yeah. I got a couple things going on.  *Deep breath* site upgrade 3 projects at the job blogging and articles for sqlserverpedia planning sql saturday for january three major trips in the next six months. Plus I think I'd like to learn to play guitar.

We're watching the pilot of Star Trek Next Generation, by the way, and the just did the saucer separation for the first time, with that insanely triumphant music.  you know?  It's still kinda cool.  And the engine section still looks beheaded to me.

Okay, two new videos from Sean at MidnightDBA:

• Add User to Windows Group with Powershell (13 min) - I love this video because it's so unusual to find something like it in the DBA community.  We're quite often called on to do things outside of our direct job descriptions and this is something I have to do a lot.  So I'm showing you how to add a user to a Windows group so you don't have to TS into a lot of boxes to add someone.
• Fix SSIS Pkg Import Errors (5 min) - This is a live troubleshooting video.  I had an error importing an SSIS pkg so I turned on the camera and worked the issue live for you.  A couple of the symptoms are not being able to copy tasks inside Visual Studio and getting an error about not being able to serialize the runtime object.  This is one of those cases where at least being exposed to the types of things that can go wrong will help you come up with a solution even when you've never seen a specific error.

Some new articles from me:

• Generate Insert Statement for Table - Here's a simple enough procedure to generate an insert statement for a given table. I know you can do this through SSMS, but I dislike having to navigate through the tree to get to the table I want.
• Functions - @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT - When you insert a row into a table with an identity column, SQL generates the identity value and inserts it behind the scenes. There are several ways to retrieve that value...
• Identities Overview - I contributed the IDENTITY INSERT section.  Go-go identity insert!

 And happy days for all.

-Jen McCown, http://www.MidnightDBA.com

This is the companion text to the video SSIS: Deploy Packages.  This will be a two- or three-part series, I think.

There are two ways to deploy SIS packages, and the difference becomes important when you have a lot of packages. The first way is just to individually deploy a package directly to SQL (or the file system). The second way is to create a manifest, which allows you to deploy all the packages at once, instead of having to do one at a time. Today we’re talking about the manifest.

A quick side note: When you create a new Integration Services (SSIS) package in Visual Studio, it's named Package1.dtsx by default.  Always rename your package to something meaningful, and select Yes in answer to "Do you want to rename the package object as well?" See the blog titled SSIS Packages: Rename the Package Object as Well? for more information.

Deploy with Create Deployment Utility

When you have a package ready in Visual Studio,

1. Go to the Project menu and select Properties.
2. Under “Deployment Utility”, Set "Create Deployment Utility" to True, which creates a manifest file when you build the project.  (By the way, does anyone know how to set this by default?)
3. “OK” out of the Property dialog.
4. Save your project, and Build. 
5. Go look in your project folder. In our example, the project folder was in My Documents\Visual Studio 2008\Projects\Integration Services Project2\Integration Services Project2\bin\Deployment\.

In there you have a your package files named packageName.dtsx file, and one deployment manifest - an XML file named projectName.SSISDeploymentManifest.  You can open the manifest in Internet Explorer to take a look.  It tells you what it's generated by, who did it, and a list of packages. You could create this by hand easy.  As a matter of fact, Sean wrote a .Net desktop app to create a manifest file from a list of packages. “From the developers I was getting nested folders with individual files; I had it make a master manifest file.”  Sweet, sweet automation...

Now, we could go to SIS and import the .dtsx file just fine, but we'd have to do that individually for each package. When we double-click the manifest, it brings up the package installation wizard which covers all the packages.

You can deploy packages to SQL or let them sit on the file system. That's something new with SIS: In DTS your packages were in SQL; in SIS you can run them t from the file system.

Package Location

So SQL Server deployment deploys the package and stores it in MSDB.  I could instead point it to a file system; it would still import the metadata into MSDB but the package itself would live on the file system.  The advantage to this is that you have a single package. It's easier to have one package & edit it as needs be, and several different marts can call it from the same place on disk.

Back in DTS you could go into Enterprise Manager and double click on a package and edit.  However, after a package is deployed from SIS, it's compiled code.  Consider these to be like any C# application: they're exe files.  So you have two choices for editing SIS packages:

1. go into SSIS manager, right click and export package to file system, and then edit in VS and redeploy.
2. OR you can store the package in VSS or TFS server, some code vault, and edit/deploy that way.

-Jen McCown, http://www.MidnightDBA.com

We were busy little bees over the Labor Day holiday (or, for our European readers, "houliday").  All of these are great, of course, but I'm going to steer you DIRECTLY to Sean's new video on Recovering SQL Passwords...do not pass anywhere or collect anything, just watch it!

• Recover SQL Passwords - Did you know that SQL Server stores username/password combos in cleartext in memory?  And did you know that you can also use a debugger to sniff the memory to retrieve those passwords?  Here I show you how to attach to an instance of SQL Server with a debugger and get a password you need to retrieve.  Thanks again to Sentrigo for bringing this to all of our attention.  12 minutes
  
• T-SQL: CASE Statement -  Here’s an intro to case statements, in the form of a conversation with Sean.  I cover simple case and searched case, and the special use of null.  BTW, I really like this video, and I don’t say that very often. 12 minutes.
  
• Grouping Basics: Level Zero - Here’s a ground level intro to grouping, with a lovely 80s arcade theme.  See the next blog post for the written companion to this video. 10 minutes.
  
• Dirty Tricks:  List from CMD - Here’s one of my dirty tricks: getting a list of folders or files from a command prompt, and formatting it for use in code using  Word. This is the extended remix version of a Midnight Snack. 7 minutes.
  
• Troubleshooting Simple Flatfile Load Errors - In this video I put together a small SSIS flatfile-to-SQL package, and troubleshoot a couple of errors.  11 minutes.
  
• Who Should Manage Backups - SORRY GUYS!!! This was about a 45min video and a glitch with the computer lost the first part of it so you only get the last 19mins.  We thought about recording it again, but we honestly don't remember what we said so I made the command decision to just post it as-is. 19 min.

 -Jen McCown, http://www.MidnightDBA.com

When you create a new Integration Services (SSIS) package in Visual Studio, it's named Package1.dtsx by default.  When you rename the package - and you should always rename the package to something meaningful - you're asked "Do you want to rename the package object as well?"  You'll want to answer YES every time. Donald Farmer clarified this in the MSDN forums:  

In Visual Studio you rename a file - the *.dtsx file. However, the SSIS package is an object in the file. So if you don't rename the object you could have a package file called MySuperPackage.dtsx containing a package object called MyOldPackage. Try saying no to the dialog once and check the name property of the package in the properties window - select the package by clicking in the background of the object explorer.

I tried an experiment: I created a new package and renamed the file "Package1.dtsx" to "Meaningful.dtsx". When the rename package object dialog appeared, I clicked No.  Let's take a look at the code...down near the bottom you'll see this:

The file is named Meaningful.dtsx (not shown), but the DTS:Name="ObjectName" is still Package1.  When I deployed this, the package name in SQL Server was Package1; it ignored the file rename.  This will cause problems if you do this with different packages; each has a different filename, but each is deployed as Package1, so it'll get overwritten. 

So that's a long way of saying: Rename the file, click Yes to rename the object.

-Jen McCown, http://www.MidnightDBA.com

Okay, we've posted NINE new videos in the last two weeks:

• Index Scripting Problems 
• Find DB Files with Powershell
• T-SQL: Over() and Partition By
• Backup File Extensions
• Pass Params to Scripts (Powershell)
• Deploy Packages
• Troubleshoot Missing DB File
• Troubleshoot SSIS: “Script component cannot build user component”
• T-SQL:  Find Last Day of Month
  

We also got our Midnight Snacks (our 1 minute tips and scenarios videos) up and going. We've decided to publish them to a YouTube channel: http://www.YouTube.com/MidnightDBA

Sean had a simply lovely O'Reilly PowerShell webcast last week. I've been told the video should be up tomorrow. I'll link to it from here and elsewhere when it's available.

And as always, you can keep up with much of our goings-on through the MidnightDBA Twitter feed: http://www.Twitter.com/MidnightDBA

`Night!