Weekly TidBIts (September 28, 2012)

I don't have a lot of experience doing anything very advanced in Excel so I ended up researching some stuff related to Turtle Charts this week. As a result, two Excel articles kick off the Weekly TidBIts today. In fact, despite a busy week, I don't have a lot of SQL Server or MSBI stuff for you until the very last item. It's all good stuff though so check it out if you have time.

Weekly TidBIts (September 21, 2012)

Whoa! It's been a while but I have some neat stuff to share today. SSIS and SSAS/MDX are in the mix along with a special treat if you're "of age."

SQL Server Upgrade Gotcha: Read-only Databases

Let me start off by saying that this is not Microsoft's fault. You really can't expect any different behavior in this scenario. The story goes a little something like this:

We had a couple datamarts that held historical information that could not be recovered from the source so we had them marked "read-only" to prevent them from getting mucked up. These datamarts were copies of another datamart that we do update on a regular basis so they have really long names to explain why they're hanging around in a read-only state.

Well, after the SQL Server 2012 upgrade I didn't notice that the databases got stuck in the "recovery" state. The indicator that SQL Server Management Studio puts at the end of the database name was hidden because the database name was so long. Since no one actually uses these datamarts, no one noticed for a long time. That is, until I needed the disk space for something else. You can't detach a database in a recovery state you know.

BI Admin Scripts: Run DTExec with PowerShell

There are a few different ways to run SSIS packages. You can use the DTExec utility. You can create a script that runs it the package via the object model. I think there are some others that I can't remember at the moment too. My scenario required a script that could run a package whether it was built in SSIS 2008 R2 or 2012. Additionally, the 2012 packages could be part of a Package Deployment Model project or a Project Deployment Model project. Read on for a script that covers everything I could think of (so far).

Weekly TidBIts (August 31, 2012)

If you're reading this within a day or two of it being published then I am probably riding roller coasters at Universal Studios Orlando. Try not to feel bad for me.

SSIS 2012 Gotcha: DTExec.exe and the Environment Reference ID

Microsoft dropped the ball on this one. The DTExec.exe utility (MSDN docs) takes a parameter called EnvReference or Env for short. You would think that you could pass the name of your SSIS environment or maybe even the path to the environment, especially since the only way to find the ID is to query the catalog database. You would think.

Read along for a PowerShell function that will get the Environment Reference ID for you. In a future post I will put this together with another chunk of PowerShell that you can use to run packages from the standard command prompt or a PowerShell command prompt.

Weekly TidBIts (August 24, 2012)

Another installment of the eagerly anticipated Weekly TidBIts. This week starts off with some more SSIS information but also includes some PowerShell helpers.