In SSIS 2012 there is a great new feature called environments. They can be thought of as a collection of parameters or variables. One of the questions I get asked when teaching SSIS 2012 is ?Can I duplicate an environment??. There is a move feature already included. But this moves the environment instead of copying. Now you can write T-SQL Scripts like the one at the bottom of this blog to create an environment and create the variables in the environment, but this is time consuming. You can scri...
Read More
Recently I did a webinar on the IRS and SSRS. In this webinar I covered creating reports with images and forms. I also covered how to manipulate data and showed you how to cheat on your taxes(not really).You can download the files for this webinar here: http://www.bidn.com/Assets/Uploaded-CMS-Files/IRSandSSRS-cefd99fa-6074-4be6-84ee-db1490dd3f42.zip You can watch the webinar here: http://pragmaticworks.com/Resources/webinars/Default.aspx
I did a webinar during the 12 days of 2012 on how to use Report Builder 3.0. In this webinar I showed how to pull data from Stored Procedures and Shared Data sets. I also showed the issues that come with some of these items.You can see the webinar here: http://swrt.worktankseattle.com/series/59/seriessignup.aspx
If you want to execute a set of SSIS packages in SQL Server 2008 or 2005, you can do this using T-SQL. First you will need a table with all of your package names on it. Then a While loop to execute each package. Here is the example code: Declare @FilePath varchar(2000) Declare @cmd varchar(2000) DECLARE @package_name varchar(200) Declare @PackageCount int Declare @X int Set @X = 1 Set @PackageCount = (Select COUNT(*) from Packages) set @FilePath = 'C:\Package Path' While (@X <= @...
I did a webinar on Monitoring SQL Server with SSIS on pragmaticworks.com. Check it out on the webinars page there. Here are the code files for that webinar. The code is done in 2008 R2.
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
With some calculated members in MDX it only makes sense to see the calculation if a certain Hierarchy is used. For example: Aggregate(YTD([Date].[Calendar].CurrentMember),[Internet Sales Amount]) This calculation only works in the Date.Calendar Hierarchy. I wanted to show the end users a message informing them about this and hide the calculation at the same time. Here is how I did this: Case when ([Date].[Calendar].level is [Date].[Calendar].[Calendar Year] or [Date].[Calendar].level is [Date].[...
I just returned from Seattle, WA and my first time speaking at SQL PASS Summit. I did the SQL Smackdown T-SQL vs SSIS Loading a Data Warehouse with Adam Jorgensen. The session was a hit, it was standing room only and the room was full from beginning to end. Adam did a great job showing the T-SQL method and I showed the SSIS method. In the End The referee Devin Knight and Adam jumped me a stomped me into the stage. I survived and will live to put on another SQL Smackdown. You can download the fil...
Previously I wrote a blog on how to do a for each loop to look through each col in an SSIS data flow here . Well things have changed since I wrote that blog, in fact I believe that old code only works in SSIS 2005. So I thought I would be good to update to SSIS 2008 and show you new and better way to loop through all of the columns in a script tasks. You could do the same work with a derived column. The problem comes when you have hundreds of columns and you need to do the same work on eac...
SQL Saturday 85 in Orlando , FL on September 24, 2011 was a great event. The people putting it together did a great job of putting on the event. The logistics on these events must be a daunting tasks and they handled it perfectly. I would like to personally thank Bradley Ball ( Blog | Twitter ), Karla Landrum( Blog | Twitter ), and Shawn McGehee( Blog | Twitter ) for the work they did to put this SQL Saturday. I apologize to anyone I left out. In my first session I covered SSIS Script tasks. You...
Check out this cool Infomercial Pragmatic Works made about BI xPress. http://pragmaticworks.com/landing/bixpressinfomercial.aspx
My SSIS White Paper has been published! Download the White Paper and the Sample Files here. "SSIS is a fantastic Microsoft tool for performing Extract Transform and Loading (ETL) procedures. A challenge in SSIS is identifying a problem once you deploy your packages. In this webinar you will learn how to both identify and solve your SSIS issues." REGISTER NOW! What you'll learn: ? How to set up auditing on SSIS packages ? How to set up logging on SSIS packages ? How to use both native t...
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 ...
I?m very proud to announce that Pragmatic Works will be presenting a Business Intelligence Workshop pre-con for SQLSaturday #85 in Orlando on Friday September 23rd! In this session you?ll have not one, not two, not three but FOUR Pragmatic Works consultants walking you through how to develop a full business intelligence solution from the ground up! Your instructors for this awesome pre-con are: Jorge Segarra ( Twitter | Blog ) I?m a BI Consultant for Pragmatic Works and a SQL Server MVP. In addi...
Are you ready for a knock down drag out fight! Come See SSIS vs T-SQL at SQL Saturday 85 in Orlando on September 24, 2011. Watch Mike Davis (@MikeDavisSQL) take on Luis E Figueroa (@LuisEFigueroa) in a head to head battle. Showing the best practices of each method. Also, check out the pre con with Mike Davis and some of the other powerhouses from Pragmatic Works. http://www.sqlsaturday.com/85/eventhome.aspx
Here are three scripts that create and Date and Time Dimension and can add the fiscal columns too. First run the Dim Date script first to create the DimDate table. Make sure you change the start date and end date on the script to your preference. Then run the add Fiscal Dates scripts to add the fiscal columns. Make sure you alter the Fiscal script to set the date offset amount. The comments in the script will help you with this. This zip file contains three SQL scripts. Create Dim Date Create Di...
I am very excited to announce that the Schedule is now live for SQL Saturday 85 in Orlando, remember to save the date for Saturday September 24th! There will be a lot of information to come. There is a Full Day Pre-Con Training also. I will be seaking on SQL Server Denali and also on Scripting with .Net in SSIS. Hope to see you at my sessions. Please go to the link, http://www.sqlsaturday.com/85/schedule.aspx , to see the sessions and the tracks. Look forward to seeing you in Orlando!
PASS Summit is coming up fast, and my Session with Adam Jorgensen "SSIS vs T-SQL: Loading a Data Warehouse", is one of the comunity choices. Please take a moment and vote for my session. Be sure to vote for my Community Session for PASS Summit http://www.sqlpass.org/summit/2011/SummitContent/CommunityChoice.aspx SSIS vs T-SQL: Loading a Data Warehouse
Getting the YTD from a cube is a simple MDX formula either using the PeriodToDate function or using the YTD function. One request you may get it to see the previous year to date. So if you are looking at March 21, 2011 year to date, you want to see the year to date from March 21, 2010. This can be done by using the ParallelPeriod function. Here is the Previous YTD calculation using adventure works. (PARALLELPERIOD( [Due Date].[Date].[Calendar Year], 1, [Due Date].[Date] ), [Measures].[Sales Amou...
Previously I wrote a blog on Top N and Bottom N reporting. There were a couple of gotcha I did not cover in that blog so I thought I would cover those here to answer some of the questions I have received. The other blog can be found here . On a report you are trying to group the top N number of rows together and all others should be in a separate group. Let’s say you want the top 5 sales people based on amount. In my other blog I showed how to use the Top N and Bottom N filters on groups. The ma...
Checking a File in Use and File Compression in SSIS with Task Factory Getting compressed files from customer is a common occurrence in today’s database world. Decompressing and loading those files can be a lot of work, even in SSIS. Task Factory makes this very easy. Task Factory comes with almost 30 tasks. Two of those tasks are the File Properties Task and the Compression Task. The File Properties Task lets you check all of the properties of a file, including checking if a file is in use. Look...
Ever had to load a Data warehouse? Isn’t it so much fun creating all of those transforms to load a type 2 dimension? Of course it’s not; it is a lot of work. Well now you can load those dimensions very easily using Task factory. Task Factory is a group of almost 30 tasks that you can add on to SSIS. These tasks make your job much easier. Instead of creating dozens of task, you can use just one Task Factory task to accomplish the same work. Usually you would need a data flow like the following im...
How to Document SQL Server So you created this amazing BI solution. You have SSIS Packages loading your data warehouse, you have SSAS Cubes, and you have reports in SSRS. But how do you document this entire solution? It used to be a long tedious process. Now it’s easy! Bi Documenter is a must have tool for anyone using SQL Server. It will document SSIS, SSAS, SSRS, and databases. BI Documenter will create either HTML files, or one CHM file containing all of the information of your environment. E...
BI Xpress SSAS MDX Builder and SSIS Deployment If you build SSIS Packages, or work on SSAS Cubes, BI Xpress is a must have tool for you. It has made my life so much easier in all of my BI work. I want to share with you just a couple of the features it contains. This is by no means a complete list features. You can find a complete list at pragmaticworks.com. Deploying SSIS Packages is one of the biggest pains in SSIS development. Not anymore! I can deploy my packages with a two clicks now. You ca...
SSAS – Using the Euro or Pound currency Symbol in a measure Format String If you have a set of measures and some of them are US dollars, some are Euros, and some are UK Pounds, you want to format them correctly in SSAS. First you will need to know the ASCII command to type a pound and euro symbol. You can find this in the character map in windows. You must use the 10 key number pad to type these in while holding down ALT. Euro = Alt + 0128 € Pound = Alt + 0163 £ Next you will need to put a back ...