In today's blog, I wanted to share information on the Lookup Task. It is a great task because it will take your data Join it together, similar to what a Join does in TSQL. When you pull out the task in the Data Flow, you will need to connect it to an existing task. In the example below, I have directed data to be sent from the condition split to the Lookup. Once inside the Lookup Task, you will need to set a couple of the General settings. Here you will need to decide if you want to select Full,...
Read More
This blog series will cover SSIS from the group up; beginning with the BIDS environment and going all the way through package deployment. Please feel free to message me or email me if there is a specific topic you would like addressed. Tune in each week to see the next step in the SSIS journey. The BIDS (Business Intelligence Development Studio) environment is a bit daunting the first time you look at it. Whether this is the first time seeing any type of integration services environment or movin...
I have been exploring the various tasks available in SSIS and thought I would put together a tutorial on the Web Service Task. Using this task is pretty straightforward, but some of the settings are not too clear. Here is a step-by-step guide. 1. Create a package-level variable named StockSymbol with a data type of String and a value of MSFT. 2. Add a new connection manager by right-clicking the Connection Managers window and selecting New Connection. 3. When prompted for the type, select HTTP a...
SQL Saturday in Jacksonville is coming in just about a week and a half. If you haven't already signed up to come go to www.sqlsaturday.com to register. You can also find the list of sessions for Jacksonville here at http://www.sqlsaturday.com/38/schedule.aspx . As an added bonus for everyone coming, I will be doning a session! I know if you're not already signed up, you have stopped reading this blog post and gone to register just to see my Introduction to SSIS session. We ran out of space due t...
I recently was doing some development on my local machine and needed to pull data from a linked server. I set up the linked server and everything was working great. Executing queries to pull the data to my machine was fine, but a problem arose when I needed to execute a stored procedure from the other server. First of all, to call a stored procedure from a linked server you need to use a four part qualifier. EXEC ServerName.DatabaseName.Owner.StoredProcedure PARAMETERS Using AdventureWorks2008 o...
There are many occasions where you will want to update a table in your data warehouse by adding new records and updating existing records. Now, there are a number of ways that this can be done, and these methods all have their pros and cons. The option I'm going to cover is using a merge statement within an Execute SQL Task (or a merge within a stored procedure that is executed by an Execute SQL Task). You will need to create a stage table, preferably in a stage database if you have one created....
We had a really interesting session with Patrick of Pragmatic Works yesterday on the topic "Introduction to SSIS". He has covered the following basic aspects of SSIS Opening BIDS environment with a -NOSPLASH Creating a small package with some package requirements -- Control flow, Data flow, Event Handler, package explorer Deplying the created package Scheduling a package Here are my takeways from this session Tip 1 You can use -NOSPLASH in the shortcut of the Visual studio icon to avoid the init...
Why Query When You Can LINQ INTRODUCTION TO LINQ LINQ is a abbreviation for Language Integrated Query, Which is just a fancy way of saying in that while in .net code you can write out your queries as if it were as simple as any other statement in you native programming language. For instance back it that distant past of 2006 a c# programmer would create a data table receiving object or some other in memory container. Then if up to standards create a data read and write object for the particular ...
Being a long-time SQL Server user, I have written a lot of stored procedures and made full use of input and output parameters as well as return values. When I started learning and working with SSIS, one of the first things I wanted to know was how to call and get results from stored procedures. Mike Davis has a blog posting on calling stored procedures and passing input parameters so I thought I would address return values and output parameters. Here’s a simple stored procedure that has one inpu...
I will be speaking at SQL Satruday in Jacksonville on May 8 2010. Hope to see you there. http://sqlsaturday.com/38/eventhome.aspx
For those who attended the webinar last week, I mentioned some code on how to do a parallel load in SSIS. I started to write this in a blog post but it was so long, that I had to make an article out of it. You'll find the full post here: http://bidn.com/articles/integration-services/119/parallel-loads-in-ssis .
Last week I had the opportunity to host three "What's New in R2" roadshows with Microsoft. Adam Jorgensen was the featured speaker for each. He highlighted many of the new features in SQL Server 2008 R2 and specifically showcased the new features for Business Intellligence. We began Wednesday in Waltham, MA, traveled to Farmington, CT on Thursday, and New York City of Friday. We had the chance to meet a lot of existing customers for the first time as well as meet a lot of new people. The events,...
In a few short weeks, another great event is coming to Jacksonville Florida at the UNF campus. There are many great speakers from around the SQL Server community that will be sure to increase your skill set. Here is a synopsis of my Introduction to Reporting Services 2008 presentation that I will be giving: SQL Server Reporting Services is a fundamental part of Microsoft’s Business Intelligence suite of products which as its name suggests provides a centralized robust reporting solution for orga...
On Tuesday Morning at 2:00 am EST time I will be speaking at the Auckland SQLUsers Group . This will be my first International speaking engagement. I will be doing a session on SQL Server Integration Services . Since I don’t have time to make the trip to Auckland, I will be presenting via live meeting. Presenting via Live Meeting can present unforeseen challenges. However, the challenge in this case is not live meeting; it is making sure my alarm is set to wake me up at 1:30 am so I can do the p...
In the BI environment , an incremental load of a Fact table (also called a delta) is a process for which Microsoft has not provided us with a prebuilt tool. I'm going to be writing an article comparing several methods for doing these incremental loads, what I've listed below are 4 example techniques, as well as a comparison to a 3rd party tool from Pragmatic works. OUT of scope are the Change Data Capture techniques that only draw changed data from the source system. The assumption here is that ...
Reporting Services 2008 R2 has many new great features that I’ve written about lately ( Data Bars and Indicators ). Today I wanted to tell you a little about another nice add called Report Parts. Report Parts allow you the developer to create an object (graphs, charts, tables, etc…) and then your power users that actually develop reports in Report Builder 3.0 to use those objects in the reports they develop. As the developer when you want to publish a Report part to the server. You will use the ...
As I move into a new career in the vast and somewhat confusing (at least to me . . . for now) world of SQL Server Business Intelligence I figured it might be a good idea to understand why BI is so important. Before that though here is a little about me. I just spent the last 18 months working in the Health Information Management field as a Release of Information Technician, and attended school at night trying to learn as much as I could about database administration. Before that I was an Airfram...
Lately, I have encountered a few clients that have had the need to always run Visual Studio as an administrator. This is usually environments in which developers have two profiles. Instead of having to right click the short cut and choose 'Run as Administrator' everytime (or forgetting to do that and then realizing it at a very inconvienient moment) there is a way to set it to launch as admin everytime. It's not very difficult or technical, but a handy little peice of info to remember. Navigate ...
This past week I had the opportunity to speak at three SQL Server R2 Launch events. I spoke in three cities in four days. On day one I spoke in Omaha, NE, the next day we stopped in Overland Park, KS and finally St. Louis, MO. Each event was held at a Microsoft facility. The largest crowd was in St. Louis, there were approximately 75 people in attendance. I traveled with one of our Salesmen and two Microsoft Employees. My role in each event was to do all the demonstrations. The topics that I cov...
Hi I've just posted a connect item, please go vote https://connect.microsoft.com/SQLServer/feedback/details/553088/dataflow-unused-columns-should-be-highlighed-in-bids
In preparing my blog last week on Passing date parameters to DB2 in an OLE DB source , I ran into an issue in BIDS that I didn't really address at the time, but I thought that it might be helpful to share a solution to this pretty common problem. Once you have set up your stored procedure that gives you your SQL query output and created your variable that will hold the SQL query, you have to configure the OLE DB source using the Advanced Editor. Well, you might notice that when you open the Adva...
After running into the problem of dividing by zero is SSRS, it was once again a mighty expression that saved the day…or at least partly. You can read Dustin’s Blog to see how to work around the problem. =iif(sum(Fields!Beta.Value)=0,0,sum(Fields!Alpha.Value)/iif(sum(Fields!Beta.Value)=0,1,sum(Fields!Beta.Value)) The thing is that I really wanted to get something beside 0 percent when the value in the divisor was zero. Anyway, what percent of 0 is 0? Great minds want to know. I decided to figure ...
Often with reporting on a data warehouse you see a common theme that many companies want to report on things with the context of my current date. Take for instance a sales report where you want to see this month’s sales activity. There are several ways to handle this but I always try to side with keeping my business logic in the data warehouse so I have one place to maintain this. So the method I prefer is having flags in the Date dimension to indicate current month, current year, current day et...
If you've ever tried to use an IIF statement expression to fix an error received by dividing by zero, you probably still received the divide by zero error. Very frustrating. An expression like this returns an error when Sum(Fields!Beta.value) = 0: =sum(Fields!Alpha.Value)/sum(Fields!beta.Value) So you, being the critical thinker that you are, try the following: =iif(sum(Fields!Beta.Value)=0,0,sum(Fields!Alpha.Value)/sum(Fields!Beta.Value)) Alas, this will not work. Even though SSRS may evaluate ...
April Webinar: MVP Series In these sessions you will learn how to design a data warehouse, load it using SSIS, place a cube on top of it using SSAS and report against it using SSRS. Finally, you'll see how to performance tune and scale the data warehouse using common techniques. All speakers have authored many of your favorite books from Wrox. All sessions are recorded in case you miss the date. Please be sure to install gotomeeting video codec’s , those unable to play webinar recordings should ...