How to REALLY Add or Delete a Task or a Container in a Control Flow in Visual Studio 2013

Ridiculous.

I was staring at Visual Studio 2013 in disbelief, looking at the empty Toolbox thinking I missed an installation step, even though all the other functionality of SQL Server Integration Services (SSIS) was working just fine.

After doing a little digging (this time Stack Overflow did not help), it turns out that SSIS has its own dedicated Toolbox, and it’s basically hidden.

Thanks to an article by Robert Bigec, the answer is pretty simple, but not obvious.

The Empty Toolbox – SSIS 2012 | Robert’s Spout.ToString()

I stared at this toolbox in disbelief. White and spacious with a confirming message that gave me no comfort: “There are no usable controls in here … “. What happened to the Source Assistant? Where in the world is the Destination Assistant? Merge: gone! Data Conversion: gone! Forget about adding an Execute SQL Task, this is not happening! I guess, I’m not making any more Integration Services packages, unless I work on another computer, or go back to 2008. Maybe this is a bug. It could be, because it is a new product, right? Maybe I should file a bug report. No, it can’t be a bug, someone would have reported it by now. I must have done something to mess it up. I vaguely remember opening up another project to look at it and closing the toolbox because it was in the way, but it should come back shouldn’t it? Yeah, it should come back, but it didn’t. Desperately searching for some way to get the items back, I trolled the menus, scoured the icons and noticed this. Click! It’s back. Yay! Hoorah! Happy days are back again!

Thanks to Robert for the answer!

 

Using Temporary Tables in SQL Statements

Found this to be a really helpful tip from Using Temporary Tables in SQL Statements. I wanted to be able to quickly construct a temporary table within a stored procedure and this was just a super-simple set of examples to get that going.

Hope others find it useful as well!

Using Temporary Tables in SQL Statements

Temporary tables can be used in the SQL statement wherever the regular tables can be used. To identify a table being a temporary table in the SQL statement, prefix the table name with the ‘#’ character.

Examples:

// Create a temporary table named Temp1 with two columns
CREATE TABLE #Temp1 ( Name Char( 30 ), seqid integer );

// This example creates two temporary tables for intermediate results
// Step 1. Create a temporary table named DeptCount and at the same time
// populate it with summary data from an existing table in the
// database

SELECT deptnum, count(*) as NumEmployees
INTO #DeptCount
FROM employees
GROUP BY deptnum

// Step 2. Create another temporary table named LocCount which list the
// number of employees in each location for each department.

SELECT deptnum, location, count(*) as cnt
INTO #LocCount
FROM employees
GROUP BY deptnum, location

// Finally using the 2 temporary tables to list the percent of employee
// on each location for each department

SELECT a.deptnum, a.location, ( a.cnt * 100 ) / b.NumEmployees As PercentAtLocation
FROM #LocCount a, #DeptCount b
WHERE a.deptnum = b.deptnum

Choosing Bitness Inside Business Intelligence Development Studio

Visual Studio came to a screeching halt when attempting to run an SSIS package the other day, and it turns out that it doesn’t like running in 64-bit, at least when we were using this under Visual Studio 2008.

Therefore, here is some help for those frustrated by this (which, incidentally, feels like something the IDE should know about and just switch on its own)…

Choosing Bitness Inside Business Intelligence Development Studio (BIDS)

If you’re running your package inside BIDS, the setup is simple unless you’re using the Execute Package Task or Execute Process Task to run child packages. The package you currently have open will (by default) run in 64 bit mode.  The setting that controls this is a property on the project called Run64BitRuntime.  To access this property, right-click on the Integration Services project in your solution explorer and select Properties.  Then select the Debugging node in the editor.  The default here is “true”, which means all the packages in this project will run in 64-bit mode.  If you change this to “false”, all the packages will be run in 32-bit mode.

Hope this helps!

How to Format the Labels in a Pie Chart in SSRS 2008 R2

OK this REALLY made me do a facepalm.

I was wresting again with SSRS (Microsoft’s SQL Server Reporting Services), and was just trying to format the numbers in a pie chart. It seemed clear enough: right click on the little numbers in the pie chart and edit the number format. It even displays all the numbers in all the pie slices with the new format.

Easy, right?

No.

That change only affects the first series, with no obvious way of selecting the other series values to format them… since they look like they’re already formatted!

[image here…]

Turns out you have to move the next series to the top position in the “Chart Data” floating tool window, and then you can modify the values. I found the answer here on Stack Overflow:

reporting services – How to Format the Labels in a Pie Chart in SSRS 2008 R2 – Stack Overflow

i had this same issue, if you just use the arrows (little blue arrows at the top of the chart data box) to move the value to the top of the list you can edit the label details/code/expression etc for that series. then just repeat this for all the values you’ve added. i’m sure there’s a better way but this worked so that’s what i did. It seems that when you are editing a label it is only ever editing the item/value at the top of the list, after you’ve done your labels then just shuffle them back into the order you wanted them listed

Once you do this, you see new values, unformatted (or, more accurately, formatted with the default number format).

[image here.]

You can then go through the same process as before and change the number format for those data points in the series.

What a pain.