Remember when you used to have to write your times tables out? You had to because there was no such thing as a pocket calculator. It is sad that I remember that. You would get a piece of lined paper and write 1 2 3 4 5 6 7 8 9 10 horizontally and then vertically and start filling in the products in a grid. Yes that was also before you had to learn the 11 and 12 times table. Well here is an easier way using a recursive CTE. First, I was looking for a way to populate a table with numbers from 1 to...
Read More
I have a table with a column named ErrorCode. When I tried to use it in a data flow in SSIS I got this error: [SSIS.Pipeline] Error: The package contains two objects with the duplicate name of "output column "ErrorCode" (2048)" and "output column "ErrorCode" (2285)". The reason for this is because SSIS automatically creates a column named ErrorCode which is used for internal error handling. By right clicking the datasource and selecting Show Advanced Editor you can rename the SSIS generated Erro...
If you’ve done it then you probably know that when you create a Variable in SSIS the default Namespace for it is User as you see below. You can change the Namespace by clicking the Choose Variable Columns icon and checking the Namespace box. This is easier that using the properties windows especially if you are going to rename the Namespace for several variables. Isn’t that nice to know?
I learned today that you must refresh the Calculations Tab if you plan to drag and drop items from the Metadata tab to the Expression or else you may drag a member that may have changed or even not exist. Do this by clicking the Reconnect Icon just like you would in the Browser Tab when you get the warning "The cube has been reprocessed on the server. To prevent possible browsing errors, click Reconnect. To hide this message, Click here." The Calculation and Cube may process without error but yo...
If you want to convert the time part of a DateTime data type into common non-military time display of hour and minute with AM or PM with no leading zeros, you can use this expression in a Derived Column. It uses the DATEPART Function to get the appropriate parts for the time and determine if it is AM or PM. For example: 2011-08-07 21:56:41.813 would become 9:56 PM. Copy and Paste the Code and try it. ((DT_I4)DATEPART("Hh",«DateTimeColumn») == 0 ? "12" : ((DT_I4)DATEPART("Hh",«D...
It is possible that your cube might seem to behave properly until you add a date filter while browsing in Excel. If so you need to check the ValueColumn property for the Key attribute for your date dimension. It is found at the bottom of the properties window for the Dimension Attribute. The Value column is what will be used to evaluate expressions like filter in the underlying MDX behind Excel. Click on the Ellipsis button and select the appropriate value. In this case the value should be &ldqu...
I started the journey for this blog because I wanted to make sure I understood all the differences between TRUNCATE and DELETE. What I discovered is that the main difference is that TRUNCATE is DDL and DELETE is DML. As if there aren’t enough acronyms to remember, here you go. DDL – Data Definition Language (used to define data structures) Examples: ALTER, CREATE, DROP, TRUNCATE TABLE DML – Data Manipulation Language (used to retrieve, store or modify data) Examples: SELECT, INSERT, UPDATE, DELE...
Here is an interesting and simple way of generating a random value for each row in a SELECT. I needed to do this for a project where I needed to assign a random integer from 1 to 5. It works by casting the NEWID as varbinary and using it as a seed for the RAND function. Without the seed the RAND function would generate the same random number for each row. You can play with it here using the AdventureWorks database. USE AdventureWorks SELECT CAST ( RAND ( CAST ( NEWID () AS VARBINARY )) * 5 + 1 A...
My Favorite BI Tool Gets Better I recently had to make a significant number of changes to some connection managers and was glad to be able to use the new Package Configuration Wizard tool in BIxPress from Pragmatic Works . If you have worked with configurations for any time you know that it can be a little cumbersome to make sure you get everything right. And then there are the inevitable changes that have to be made that always seem to break your configurations. The new Package Configuration Wi...
In SSIS you might have occasion like I did to do a Lookup that needs to match data of different types. Here is the error you may get when you try to map the lookup column. There are a couple of ways you can fix this. One possibility is to convert the data by using the Data Conversion Transformation. The other way is to use CAST in your SQL query for the Lookup (assuming you are using the Best Practice of a SQL query).
There are a few tasks that I find myself using all the time when I’m on a project using MS Visual SourceSafe. I discovered that you can add buttons to your BIDS environment to suit your needs. Simply right click in a blank area on the menu and select Source Control. This will add all the buttons for Source Control. Then you can click on the drop down button and remove items you don’t want cluttering up your environment. The four I use all the time are Check In, Undo Check Out, Refresh Status, an...
In SSIS it is a best practice to name your Connection Manager the same as your Configuration File. You can save yourself some time by using cut and paste from the following screens taking advantage of Windows slow-double-clicking . Assuming you know how to create Configuration Files, use the Browse button to locate your Configuration File. After browsing to its location, click once to select the Configuration File Click a second time but not too fast to select only the Name without the extension...
My career in Business Intelligence started with the Pragmatic Works Foundation ( Brian Knight ’s gift to the IT community). The five day comprehensive class included training in SQL Server Management Studio and Business Intelligence Development Studio and helped me develop the skills needed to become a professional BI Consultant in one of the fastest growing sectors of IT. The experience for me was truly life changing! When I started my first day at Pragmatic Works I immediately knew this was a ...
You may want to use SQL Server Agent to have a Cube processed as part of a scheduled job. This is how you create the step to process the Cube. After connecting to the Analysis Server, right click on the Cube and select Process. When the process window opens, click the dropdown by Script and select Script Action to Clipboard . Then click Cancel so you will not start processing the Cube immediately. Now open a job or create a new Job in SQL Server agent. In the Job window, click on Steps and New t...
There are times during development when you need to send a package to someone to help troubleshoot a problem or validate some results, etc. Recently, a client got this message when trying to load a package that I had emailed to them. We had been sharing packages back and forth with no problem until this one occurred. As you can see the “Additional Information” doesn’t really give any additional information at all. Apparently this is a very generic message that will occur under a variety of situa...
I was looking for a way to combine text from multiple rows into a single text column. There are several ways I found to do this but here is a way using FOR XML PATH. First the reason: My source data comes from a transactional DB that users enter text descriptions in multiple transactions. Here is mockup: Text TextFamily TextGroup TextSequence This is a sample text line. C 1 1 This is another line which A 1 1 is followed by the next line that A 1 2 may roll over to the next, but A 1 3 should be r...
While working on creating some dimension and fact tables I needed to make a change to my date dimension. I went to truncate the table so I could get a fresh reload and received this error message from Management Studio: Cannot truncate table 'DimDate' because it is being referenced by a FOREIGN KEY constraint. The Foreign Key was on my FactPurchase table. This made sense so I removed the constraint and then truncated the table. When I tried to add the constraint back I got this error: Unable to ...
My wife came up with a good explanation to tell people what I do as a BI Consultant. Her explanation was basically that I help companies get their data in a format that helps them make good decisions. It started me to thinking that sometimes we forget what we actually are supposed to be doing when we are working. It has been said that “Discipline is remembering what you are doing.” So I thought I would try to remember and in the remembering maybe help someone else will remember, too. First, what...
Answering this question the wrong way can have at least one undesirable effect on your package that I know about. I was following someone else’s work and adding logging to a package and was using a system variable to append the package name to a file name with a timestamp. Easy enough, right? (Check out http://bidn.com/blogs/timmurphy/ssas/1246/append-timestamp-to-log-file-name ). The first problem was following after someone that didn't use best practices, but that's another story. It seems tha...
I was using an Oracle SQL statement in an Oracle Connection to do some ETL in SSIS and Visual Studio began locking up and restarting every time I clicked on the Columns tab inside the OLE DB Source Editor. Thinking there may have been a problem with the SQL, I first executed it in SQL Tools and had no problem running it there. I checked with a coworker who knows more Oracle syntax than I and he decided to remove the double pipes, || and use the CONCAT() function instead. It was a shot in the dar...
It is handy to be able to look at a long list of Package Log Files and find the one you are looking for by a time stamp. Here’s how to append a Timestamp to a log file using package name. In this case I have already created a txt log file connection named DimTransDesc which writes to c:\SSISLogs. My business requirements are very specific for the format I must use. In the Connection Manager, click on the log file connection and press F4 to bring up its properties. Click the + symbol and then the...
I recently had an opportunity to use the ROW_NUMBER () ranking function along with the OVER PARTITION BY clause. Here’s a simplified example of how I used it. In this example the desired result is to get the first value in ColumnB for each group of Values in ColumnA. ExampleTable: ColumnA ColumnB 1 a 1 b 1 c 2 d 2 e 2 f 2a g 2a h 2a i 3 j 3 k 3 l 3.5 m 3.5 n Here’s the query: SELECT GroupNumber , RankLetter FROM ( SELECT ColumnA AS GroupNumber , ColumnB AS RankLetter , ROW_NUMBER () OVER ( PARTI...
If you need to use an ADO.NET source you will discover that the Data access mode only gives you a choice of Table or view or a SQL command but no choice of SQL command from variable like there is in an OLEDB connection. There is a way to make the SQL command dynamic. In the Control Flow Tab, select the Data Flow Task that contains the ADO.NET Source and press F4. This will bring up the properties window for the DFT. Click the expression button, opening the Property Expressions Editor, and select...
Here is a way to display the Top or Bottom 10 records of an Interactive Sort. The Interactive Sorting is the easy part. First, in the Text Box Properties, Enable interactive sorting and choose what you want to sort by. Then to only show the Top or Bottom 10 records right click on the Row Tab to the left of the Table and select Row Visibilty… Click the Expression button and enter the following RowNumber expression: The rows greater than 10 will be hidden in this case. When you preview and click t...
Sometimes you may need to Work Offline in SSIS. The option is found on the SSIS menu once you open a Solution. This can be handy if you are working with sources which are temporarily unavailable or have changing credentials. I had a situation (a mixture of development and production environment) where my username got locked out of an AS400 connection because the validation process in the SSIS package was hitting the server over and over and eventually triggered a lockout. Working Offline is a gr...