If you only get a chance to read one book this year, it should be Drive , The Surprising Truth about what Motivates Us. This book has made me rethink how I am motivated and how I can motivate others. It is all based on scientific studies and business studies. Some of the most successful companies use these methods and that is a big part of why they are successful. If you don't think you have time to read it, get the audio book from your library and listen to it in the car on the way to a from wo...
Read More
My new book on Business Intelligence is available on amazon. You can pre order now and save. http://www.amazon.com/Knights-Microsoft-Business-Intelligence-24-Hour/dp/0470889632
There is no native Transform built in to SSIS to get the previous row in the data flow. But with a little work you can do this and make it perform much better than a SQL cursor and you don’t have to use the dreadfully slow OLEDB Command transform. In this example I have some data that shows the day a patient was admitted to the hospital and you want to figure how many days it has been since the person was in the hospital last. So you want to calculate the days since the last admit date. Here is ...
With the new Map control in SQL 2008 R2 there are three different map types, Road, Aerial, and Hybrid. If you want to allow your users to change the map type you would think you could map a parameter to the property on the tile layer with these three types. If you map a parameter to the map type and try to change the type the report stays on the original map setting. But there is a trick to get this to work. First create a parameter with the three map types in the available values. Then create t...
The scope statement in MDX is great for applying calculations to a certain area of a cube, also called a subcube. Dustin Ryan wrote a great article on the scope statement here . The issue I am going to cover will be using the scope statement when you only want to cover all but one member of a dimension. The normal scope statement would be: Scope([Referral Type].[Referral Type Desc].members,[Measures].[ER to IP Admits]); This = ([Measures].[ER to IP Admits] * 0); End Scope And this works great an...
If you are using the templates for MDX calculations built into SSAS 2008 you might run into some strange issues. In the image below you can see the calculation is working for all years except for 2008. My first thought was data issue. After querying the table and finding no issues I returned to the MDX calculations in the cube and started troubleshooting. Here is what I found. The format string is set to standard when you drag over the Period to Date template. But the word standard does not have...
SSIS Execute SQL Task Error : No disconnected record set is available for the specified SQL statement. If you get the error in SSIS that says: [Execute SQL Task] Error: Executing the query "INSERT INTO [table].[dbo].[tablename] ([N..." failed with the following error: "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established corr...
I just took the Microsoft Business Intelligence Certification Test 70-452. This is the pro test and the second one after the MCTS 70-448. I have a lot of experinece with the BI world so the test was not that hard. I did use the transcender guide to help prepare and it was a big help. You need to know the administrative side for this test. It had a lot of high level questions about setting up servers and deployment. Good luck.
I will be at SQL Saturday in South Florida on July 31st, 2010 Hope to see you there. http://www.sqlsaturday.com/eventhome.aspx?eventid=47 The South Florida SQL Saturday will be help on July 31st, 2010 at Devry University - South Florida, 2300 SW 145 Ave, Miramar, FL 33027. Event checking will be at 7:30 with the Keynote beginning at 8:00 and sessions beginning at 8:30. Sessions will wrap up between 5:00 and 5:30.
In a recent SSIS class one my students ask me, if you are reporting off a table and using it in a matrix, does the matrix show null rows. The answer is yes. Here is an example table and the resulting report. This is the table used. All rows were selected Here is the resulting matrix. The nulls are not hidden. To hide them the query would have to eliminate them.
Multiple value parameters are a common tools used in SSRS. You can use this tool to select which columns show on a report. You will need to create a multiple value parameter and place an expression on each column on the report. More specifically the expression needs to be on the column visibility property of the columns. Here is an example report with five columns. Here are the parameter available values with the column choices. I hard coded these choices into the available value fields; you cou...
If you have two or more records on the same row, and need to write each record on its own row in a destination, you have two choices. You can do this in series or parallel in a single data flow in SSIS. Here is the input table I am using for my example. Notice I have three names on one row. I need these to be inserted into a table with a first name and last name column only. So all three first name fields need to be mapped to the only first name columns on the destination and the same is true fo...
In an SSAS cube you sometimes need to connect a dimension at a different granularity than other dimensions. For example, if you have budget measures and actual numbers. If you are trying to budget your expenses you budget at a higher level than the actual amounts. You budget money for groceries, not for Eggs, Milk, and bread. But your actual amounts will be down to the individual items. In the cube browser image below you can see this in use. Notice the budget numbers only show on the category l...
If you need to call an AS 400 program from an SSIS package this can easily be accomplished with an Execute SQL Task. This can be made even easier with two variables and an expression too. The AS400 program QCMDEXC is usually called using the CALL QSYS.QCMDEXC command. This is then followed by a command like CLRPFM FILE(MDAVIS/APPLSQL). Then this is followed by a ten digit string of numbers that contain the number of characters in the command. Then a period followed by five more zeros. So the com...
If you want to use the new mapping feature in SQL server R2. You may want to get the Spatial data for the address data you have. There are several websites that can do this for you. Here are a few: http://batchgeo.com/ http://www.findlatitudeandlongitude.com/batch-geocode/ http://www.ffiec.gov/Geocode/default.aspx http://geocoder.us/ https://webgis.usc.edu/Services/Geocode/Default.aspx
You can loop through excel files using SSIS. This will use the For Each Loop container and a Data Flow task. First create a variable named strExcelfile as a string variable; you can leave the value blank. Next, drag in a For Each Loop. Set the enumerator to For Each File, and point it to the folder where the excel files exist and type .xls or .xlsx for the file type. In this example the excel files are in c:\test\excelfiles\. Then click on the Variables Mappings node and add the strExcelFile var...
I will be speaking at SQL Satruday in Jacksonville on May 8 2010. Hope to see you there. http://sqlsaturday.com/38/eventhome.aspx
When using the Bulk insert task in SSIS, you might encounter an error stating that The File Does Not Exist or The Path Specified Cannot be Found. This is usually due to the fact that the file must be on the same server as the database. In other words, it you are connecting to a remote machine the flat file must be on the remote machine. The connection to this file in the SSIS package must use a UNC path and not a mapped drive. If you are using the local instance of SQL server then the file conne...
The Web Service task in SSIS can be used to call a web service command to perform a needed operation in your package. The results of most web services will be in XML Format. You can save these results into a variable or into a file. Either way, you can then parse the XML to find the node values you need. This can be done with the XML task in SSIS. Click here to see my blog on the XML task . The Web Service I am using is a free demo service. It allows you to enter a zip code and returns the city ...
The XML Task in SSIS allows you to parse through an XML file and read the nodes in the XML. In a previous blog I showed how to use a web service task to get the city and state when entering a zip code. You can view that blog here . The results we got back from the web service were in XML format and we saved them into a variable named Results. This image shows the value in the variable. We need to get the city and the state out of this XML and save them each into a variable. You will need to crea...
When running a For Each Loop through a set of files, sometimes you will have specific files that you do not want to load. For example, I have a set of files named: Abc.txt Mno.txt Rts.txt Wln.txt Xyz.txt If I want to skip the file that starts with “W” then I will need an expression in my For Each Loop to detect this file. Inside the For Each loop I am going to place a sequence container. This will give me a place to anchor my expression which I will place on the precedence constraint coming from...
If you are like me you click the mouse a lot, and fast. I drive my mouse to the breaking point clicking around so fast on items, especially in SSIS, which is a graphical interface. But when clicking on the tasks in SSIS I sometimes end up in the rename function instead of opening the edit screen. There is an easy way to avoid this. Look at the image below: Double clicking in the red section will open the editor. Double clicking slowly in the blue section might start the rename function. If you d...
When running a package with an execute SQL Task it is easy to get syntax errors. If the tasks turns red, but still accomplished its work, like an insert statement and the error you recieve is: Executing the Query " " Failed ... This is saying the query was blank. If you have a GO statement at the bottom of your SQL task an there is a space after it, that is the cause of your issue. Remove the space, or the entire Go statement.
When creating report models you might run into bad database designs where the tables have no foreign keys and/or no primary keys. This can be resolved in the report model DSV. I have created two tables, one with first names, the second with last names. Each has an ID column which is the row that matches the row on the opposite table. There are no primary keys and no foreign keys on either table. In visual studio I created a report model project and selected the database with these two tables. Th...
In my last SSRS 2008 class I was showing the students the chart tools and used color as a series on a chart. It was the product color column from the AdventureWorks 2008 Production.Product table. The series automatically chooses colors based on the color pallete of the chart. This made the report confusing because the colors did not match the product colors. Right click on the Chart and select Series Properties. Select the Fill option and click the expression button next to color. Then set the v...
It's fast, easy and free! Submit articles, get your own blog, ask questions & give answers in the forums, and become a better developer, faster.
enter your email address: