In my previous post we walked thorough the process of creating SSIS documentation using BI Documenter . In this post we will reap the rewards of our work on from the last post and review the documentation(not much of a reward, I know, but its better than a sharp stick in the eye). When we last saw our documentation we had created an .CHM file as our document type. Remember that BI Documenter can create two types of documents, .CHM or an HTML document. Inside the .CHM file you c...
Read More
Hello BIDN, The recording from my webinar today has been uploaded and is now ready to be viewed on demand. I would like to thank everyone who took time out of there busy schedules to listen to me talk about SSRS expressions. As promised during my webinar you can see all of the examples used today below along with a few other links that I have found useful. Report Execution with concatenation ="Report Generated on: " & Globals!ExecutionTime Report Execution on separate lines ="Report Generate...
These days, everything seems to be 64 bit. As technology advances and costs start to come down, businesses are easily moving their IT systems to 64 bit hardware. There is one little issue with that. In SQL Server land, there are some things that still need to be done in 32 bit. In SSIS, there are a few tasks that cannot run in 64 bit. The four most common are the Active X script task, the Execute DTS 2000 package task, the Excel Source, and Excel Destination. The first two, you shouldn?t ...
Microsoft SQL Server 2012 Release Candidate 0 (RC 0) has a confirmed issue with Master Data Services configuration. The following error is raised by the Master Data Services Configuration Manager when launched: Before creating an MDS web application, you must correct the following errors: Internet Information Services (IIS) is not configured on this server. The required .svc handler mappings are not installed in IIS. For more information, see http://go.microsoft.com/fwlink/?LinkId=226284 . For w...
Let’s learn a little T-SQL. Yes, I am doing a series on T-SQL for the absolute beginner who is interested in understanding how to get specific pieces of information for their company’s data. Do you know what are the top selling products or who are the best sales people or maybe even who the best customers are? We are going to start from the ground up learning the environment of Sql Server Management Studio: , Connecting to a Database Engine , Querying your Database , Tables , etc. To...
When archiving files, it is common to encounter the need to zip multiple files into one archive. This can be easily done with the help of Task Factory. For this example, I will use a file system task, the Task Factory Compression Task, and a ForEach Loop container. First, start with the file system task. You want to move all the files you are interested in to one folder. You only want to point it to one file for now. The rest will be handled by the loop. There are three settings I highlig...
Have you ever inherited an ETL environment and tied to make heads or tails of what was going on? The previous developer didn?t leave any documentation. You have deadlines to meet and you need to know which packages do what. Does this sound familiar? The worst is when you do this to yourself, a few years ago you created a package at someone?s request and now they?ve come back to you asking about that package and you can?t remember anything about it. Proper technical documentatio...
Named sets are simply MDX expressions defined with an alias that return a set of members. If you find yourself writing complex MDX expressions to return a set of members often or if you have a commonly used expression, consider creating a named set. This will prevent you from having to duplicate your work. To create a named set, open your SSAS project and head to the Calculations tabs. Find the icon with the curly brackets and click it. Give your named set a name. For this example, I'm using the...
If you understand how to use a SELECT statement you have the ability to create a subquery. When you use a SELECT statement within another TSQL statement you are using a subquery. One example is within a WHERE statement. The simplest form of a WHERE statement is WHERE a specific column equals a specific value. I will use the AdventureWorks database in an illustration. Such as in this example that would only return rows where the ProductID was equal to 709: In the case where you only want rows whe...
You’ll often be tempted to build a CASE WHEN Level calc to get an average monthly calc, but there’s an better way. You'll notice the error when using CURRENTMember and in the client tool doing a multi-select, it doesn't work because it doesn't have a single member to work against - this solution fixes this See below for an example against Adventure Works: with member Measures.MonthlySales as --CASE --WHEN --(EXISTING [Date].[Calendar].Members).Item(0).Level IS [Date].[Calendar].[Mont...
Just a quick note that SQL 2012 - Release Candidate 0 is up on Microsoft's site now. http://www.microsoft.com/sqlserver/en/us/future-editions.aspx From what I see so far - it's a free ( with registration ) download and installs / runs as a 180-day evaluation. There are 32 and 64 bit versions available separately ( ISOs ) or you can get a DVD ISO image with both versions.
Hello BIDN, I just wanted to send out a list minute reminder to everybody before they go into their annual turkey comma that I will be speaking next week. I have the privilege of doing a free online webinar for Pragmatic Works this Tuesday 11/29/11. During this 45 – 60 minute presentation I will be covering all the basics when it comes to the SSRS expression language. So if you’re new to SSRS or still don’t have enough confidence when it comes to working with the expressions yo...
One thing I get a lot of questions about in SSIS is ?What is the difference between the For Loop and the ForEach Loop?. They are both used for two different things. The For Loop is used for looping through a number of tasks for a set number of times. It?s not really used too frequently. Let?s look at the configuration. In the For Loop container, there isn?t a lot to configure. First off, you need a variable of the integer data type. For the initial expression, this is the starting value o...
Every Tuesday and Thursday, Pragmatic Works has free webinars on a wide range of SQL related topics. I have done of few of these in the past. The issue I am running into is fresh content. What I need are suggestions. What kinds of topics would you like to see? Give me your input. Anything related to SSIS and SSRS. Check out our webinars page. You can access it here . Look at the past and upcoming webinars. They are all about an hour long and full of amazing info.
1. Start the host system 2. Copy the .vdh and a copy (on HOST pc) C:\vhd (create the directory if not there) (The folder will have the .vhd and the copy for emergency procedures listed below. 3. Rename the VM for current box 4. Open cmd 5. Type cd c:\ hit enter 6. Type bcdedit /copy {current} /d "Student***" Hit enter. 7. There is a guid that is produced. Right click and select Mark. Highlight the brackets and the guid and then hit enter. 8. bcdedit /set { paste guid here } devi...
Create VHD 1. Create a VHD in Virtual PC (32bit only) or Oracle Box (64 or 32 bit, must be VHD). 2. Once created load Windows 7 3. Open sysprep (windows/system32/sysprep) 4. Run the .exe 5. Choose OOBE and select normalize ? choose shut down ? click ok 6. Copy the .vhd to a drive
If you ever want to open up a can of worms ? use a title like that! This reminds me of some of my college courses. Almost every professor believes that their subject is the most important subject. You may be taking a course that is not in your major, but it is still the most important subject. I think the same goes for Business Intelligence. The BI team is technical in nature and might lend itself to creating the path that the BI project should go. BI team has a high level knowledge...
Our Product BI xPress won two awards from SQL Server Magazine. Check it out! http://www.sqlmag.com/content1/topic/2011-sql-server-magazine-editors-community-choice-awards-140830/catpath/awards/showprivate/1/page/3
Last week it was announced that Pragmatic Works' tool, BI xPress , won the SQL Server Magazine Gold Editors Choice Award for Best Business Intelligence and Reporting Tool and the Silver Community Choice Award. BI xPress allows for real time monitoring of all your SSIS packages executing on your servers. The way that BI xPress can allow you to monitor your SSIS package in real time is by applying a standardize auditing framework across all of your SSIS package using the BI xPress Auditing Framewo...
The other day I was trying to create a data driven subscription for a specific report and I couldn't. For some reason after I applied my stored credentials to the data source in Report Manager, I could only create a New Subscription, but not a Data Driven Subscription. I honestly was baffled as to why I was getting this below error. I took all the necessary steps. I created the stored credentials in SSMS. Selected the database I wanted the access to and then applied the credentials on the data s...
If you have ever developed SSIS packages that would eventually be deployed to a production environment then you've run into this issue. I'm talking about Package Protection Levels. What tends to happen is users set up their packages how they want them, they can execute the packages fun in debug mode inside of BIDs/VS, but the moment they deploy their package it starts wigging out. These error messages can range from "Package Validation Errors" to "Invalid Username or Password" errors. The underl...
I came to foundation as a Business and Information Technology consultant with a small list of clients. Professionally I have been successful with a general knowledge of many things, but not had the opportunity to excel at one thing. Being mediocre at many skills is not appealing to me because I want to be excellent at everything I do. So to accommodate my objective I searched for a full-time job that I could focus on one set of IT skills. My search lasted five years until I participated i...
When using BI Documenter, some users may encounter the following error. Could not allocate space for object <Object Name>. <Object Name> in database <Database Name> because the 'PRIMARY' filegroup is full. Normally, this is caused by a size limit your DBA placed on the growth of the database. If you are using BI Documenter, you want to make sure that there is no limit to the growth, or at least make sure it?s set to a reasonable limit. You can check this setting and chan...
This week we’re going to talk about a topic that has been gaining steam in the last few years and as it has it has started impacting database administrator’s worlds more and more: virtualization. Why do I make this statement? Well since the economy currently sucks, shops are finding ways to consolidate and make their dollars stretch a little further. Back in the day when you had a new application you pretty much went out and bought yourself some new servers and went on your merry way...
Expressions, what are they and why are they used? In Business Intelligences there are different types of expressions. The language for expressions in IS and RS are different. In this blog, expressions will be discussed as a concept more than technical information on expression syntax. Expressions via the SSIS expression language make your packages dynamic. You can setup expressions on most objects so that if action A occurs the result will be result A or if it fails then result B. This me...