Friday, August 31, 2012

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.

Wednesday, August 29, 2012

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.

Friday, August 24, 2012

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.

Tuesday, August 21, 2012

SSAS 2012 Gotcha: Impersonation Info in Data Sources

I don't know if SQL Server 2012 got pickier about this or what. When we did our upgrade to SQL 2012 we actually replaced our Development and Quality Assurance (QA) servers. As a result, there weren't any databases on there to work with. In order to allow developers to deploy to these servers we created blank AS databases and added a role in the new DB that would allow them to deploy. Unfortunately, this didn't work like it used to (or at least how I thought it used to). Instead, the developers kept getting failures when they tried to deploy along with the following error message:
The ImpersonationInfo for datasource 'ASDBName' contains an ImpersonationMode that can only be used by a server administrator

Friday, August 17, 2012

Weekly TidBIts (Aug 17 2012)

I was locked in a dark room last week working on a project. Well, I guess "locked" is a bit strong and it wasn't exactly dark but I was working on a project. Things didn't go well and I didn't keep track of where I got my information from so I had to start over again. Maybe I will get it right going forward.

Wednesday, August 15, 2012

Ask the BI Admin: How do I get meaningful error information from a Script Task in SSIS?

Most of us have seen the dreaded SSIS Script Task error and struggled to understand what it is trying to tell us. If you haven't seen it before, take a look at this:
Error: 0x6 at Really Bad Script Task: The script returned a failure result.
Mind-numbingly useless. There is a way, however, to get what you need if you put a little forethought into building your Script Tasks.

Monday, August 6, 2012

Folder Security in SSIS 2012

Deploying my first SSIS 2012 Project Deployment Mode project to the SSIS Catalog was easy. Mainly because I was a sysadmin on the server. When I tried to tighten down the security a little bit things got a little confusing. One of our developers were itching to deploy a new project to our development server so I went ahead and added him to the ssis_admins role in the catalog database but this isn't how I wanted things to be moving forward.

Friday, August 3, 2012

Weekly TidBIts (Aug 3 2012)

Here is a collection of BI-related information that I have found in the past week. I can't think of a single week where I didn't learn something new from someone else so I will try to make this a regular occurrence. (Vacations and holidays notwithstanding :)

SQL Coding Standards: Your Argument is Invalid

It may just be me, but it seems like the Coding Standards debate will never end. A Google search for "sql server coding standards" comes up with "About 738,000" results. A search for "coding standards" at SQL Server Central nets 85,666 items (articles, forum posts, and maybe some other things). Everything has ideas on what they think the standards should be. Many of these notions are carried down from years or even decades of experience. I argue that all your experience may just be a crutch holding up the habits you have developed over the years and your argument is invalid.