Below are the basic steps for adding custom components to SSIS; however, for more in-depth information I highly recommend reading Matthew Roche’s post about deploying and testing custom SSIS components.
1. Register the custom assembly in the Global Assembly Cache (GAC)
Copy the DLL to the c:\windows\assembly\ directory. The easiest way is to drag and drop the DLL using Windows Explorer (right clicking on the DLL and doing a copy/paste will not work). Alternatively you can use the command line utility called GACUTIL.EXE (for more info about GACUTIL see the link above to Matthew’s Roche’s post). Either way you will need Admin rights.
2. Copy the custom assembly to Visual Studio’s “special folder”
Copy the DLL to the appropriate subfolder in the C:\Program Files\Microsoft SQL Server\90\DTS\ directory. For Control Flow Tasks put the custom assembly in the Tasks folder; for Data Flow Tasks use the PipelineComponents folder. Remember that Visual Studio is a 32 bit application. If you’re running on a 64 bit machine, make sure to use the Program Files (x86) path, otherwise you won’t see your component listed in the Choose Toolbox Items window of Step 3.
3. Add the component to the SSIS toolbox
Open an Integration Services project in Visual Studio, right click anywhere in the Toolbox, and select Choose Items. Go to the appropriate tab in the Choose Toolbox Items window and select the appropriate components.
This morning I needed to check the properties for one of our SharePoint application pools so I logged onto the server and opened Internet Information Services (IIS) Manager. To my surprize the root was empty; it should have automatically connected to the localhost. To my even greater surprize, all attempts to manually get IIS to connect to the server yeilded the message “The path specified cannot be used at this time”.
After a quick search I found a post by Old Nick stating that this problem is easily resolved by restarting the Windows SharePoint Services Timer service. That’s pretty obvious, right? Sure enough, restarting that service solved the problem. The next time I opened IIS it connected to the localhost.
The other day I thought it would be fun to arbitrarily change the keys for all the members in one of my dimensions. Actually, there was a legitimate reason but after the fact I realized there was a much better way to solve my problem that didn’t involve changing the keys.
Nevertheless, the point is this: If you have a PerformancePoint (PPS) filter linked to an attribute in a dimension, and the keys change, your filter will break.
Lets say you have a PPS dashboard containing a filter and a scorecard. The filter is linked to the Category attribute in the Product dimension. If the keys in the Product dimension change from Bikes having a key of 3 and Accessories having a key of 4 to Bikes having a key of 4 and Accessories having a key of 5, then whenever you select Bikes in the filter the scorecard will display the results for Accessories. If these were the only two members in your dimension I’m not sure what would happen if you selected Accessories but I assume the scorecard would just show empty cells. Or spontaneously combust.
To fix this all you have to do is edit the filter by clicking on Member Selection, going through the wizard, and publish/deploy your dashboard.
I found this and thought I’d pass it on.
When implementing a PPS dashboard, the browser of choice should be Internet Explorer 8.
Whereas IE7 only supports up to 2 concurrent connections to the server, IE8 supports up to 6 concurrent connections to the server. In cases where there are a lot of objects on the dashboard you should see a significant increase in performance. This is because each object on the dashboard (each filter, each scorecard, each chart) requires a connection to the server. Instead of rendering only 1 or 2 objects at a time, IE8 can render up to 6 at a time.
I’m a big fan of SQL Server Management Studio; it’s a really great tool with a tremendous amount of functionality. Lately I’ve been working in the new 2008 version of SSMS and a few days ago I noticed some peculiar behavior: when you double click on text that is surrounded by brackets in an MDX query editor, only the text is selected, not the brackets; however, when you double click on text that is surrounded by brackets in a regular SQL query editor, the text and the brackets are selected - even if the text within the brackets contains spaces. How interesting is that?! Below are a couple screenshots to illustrate what I’m talking about:
Selected text in an MDX editor:
Here you can see that I double clicked on the word “Adventure” and only the word “Adventure” is selected.
Selected text in a regular SQL editor:
Here you can see that I double clicked on the word “Test” (you’ll just have to trust me on this!) and the entire text within the brackets, including the brackets, is selected.