One thing you have to keep in mind when designing and creating a data warehouse is that you have to regularly backup your database. It's very frustrating to put in a long week of hard work into a project just to start over in the event that you lose your data. As with many operations in SQL Server, there are two different ways to backup your database: you can do it with a t-sql script or with an easy to use GUI in SQL Server Management Studio. To use the graphical user interface, right-click on ...
Read More
Using a Try/Catch/Finally within your Script Components and Script Tasks is just good coding practice. The thing you want to be able to do is push any errors you trap to a log file. The syntax is somewhat simple, but it took me some time to research it and it differs depending upon whether it's a Component or Task. I'll go over each. Within the Component there is no access to the Dts object, so you need to work through the ComponentMetaData object as shown below. Please note the pbCancel is defi...
In my previous post, I've shown how you could extract the time duration of a DTS. Assuming that you have selected the DTS witch is the slowest among the others, how could you know wich of the steps is the worst? Imagine that you have 20 steps inside the DTS, 19 are fast and just 1 is taking the whole time? I use the logging provided in the DTS. Just right click on control flow, select "Logging...", Add a new "SSIS log provider for text files" and select the log file in "Configuration"as shown be...
I just passed the Microsoft BI certification test 70-448 on SSIS, SSAS, and SSRS. It was a tough test and covers all aspects of the Microsoft BI stack. I used a lot of different study materials. Here is my assessment of the study materials I used and which ones helped the most. MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server 2008-Business Intelligence Development and Maintenance: MCTS Exam 70-448 (Self-Paced Training Kits) (Paperback) : This book was great, if you do all of the ...
Today, I finally started the first phase of data migration into Microsoft Dynamics CRM. I had to build a few packages in SSIS to make sure the data was in the proper format. If you read my last post, you remember I was trying to use some derived columns to generate some int data types to use in place of char. Well, scratch that idea. The expressions I had to write for those derived columns were getting too large. Instead, I decided on using look up transforms. The first thing I had to do was cre...
http://www.bidn.com//Assets/Uploaded-CMS-Files/2180394a-380c-419f-a0a6-2570eb8c76beAlteringForeignKeys_1_PrepTables.zip http://www.bidn.com//Assets/Uploaded-CMS-Files/631a6a0e-8cf1-494e-b609-094c7c08e9deAlteringForeignKeys_2_CreateAlter.zip This is going to be a very rough post, because it's friday night, and I'm almost done for the day :-) A little bit of background: I have a staging environment used for a project built on MS Sync Framework, and the tables currently have Foreign Keys in exactly...
Hmmmm. Just a quick anecdote about SQL's backup compression. I backed up a DB, tiny one, no compression, resulted in a size of 5636 KB. I was mailing it out, so I quickly 7Zipped it, and got a size of 434KB. Pretty impressive ratio actually, but I've always liked 7Zips native format. Later on, I made a foreign key reference change, and one stored proc change. MINOR changes. Backed up the DB again, but with backup compression. Results in a size of 909KB. Nice improvement on 5636, but nowhere near...
Hi. Sometime is very useful to know DTSs duration. In big DWs, we often get performance issues. Just for example, in one of my datamarts I have more than 110 DTS packages running each day. And with that information I can focus where to improve. I have 2 ways to do this: inside and outside DTS. In this thread I will only talk about the outside way. To achieve this information, every of my DTS package writes into a table. When DTS starts it open a log entrie and the log is closed when package fini...
The Merge component in SSIS will take two sorted sources and union them while maintaining the original sort order. The question arises, what about duplicate records. These duplicates do not get eliminated. Here are two tables with the ID 5 and the name Brian repeated on each. Here is the layout of the data flow. Both sources are using the same query with the different table names. Select ID,Name From Merge1 Order by ID Select ID,Name From Merge2 Order by ID Just having the sources sorted does no...
The SSIS expression language is a powerful tool for a developer that gives you one method of making a package dynamic. When writing an expression there are a few things to be careful of that could potentially cause a stumbling block while writing code. For example, a common reason you may write expressions is to populate a changing file name. Say you want to have a file loaded but the name of the file should change each day the SSIS package is ran to have the current date appended to the end of ...
If you want to create a column chart and have a line across the top showing the numbers in two different ways, it is easy to do in SSRS 2008. Just drag over the data into the data field twice. Right click on the data field on change the chart type to line and the other to column. Here is a table I used to show this. I created a Column chart and dragged over the date and numbers fields as shown. Notice I placed the Number data in the data field twice. Then I right clicked on the data field and se...
When using a stored procedure to call data in SSRS (reporting services) you will need to pass the parameter from the report to the stored procedure. This is easy when you are passing in a single value. When you want to use a multi value parameter you will need to use a split function to divide the multi values into a table. Here is the Table-Valued function I use: /****** Object: UserDefinedFunction [dbo].[Split] Script Date: 01/26/2010 10:22:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER...
If your building a new sql server, you'll need to move the SQL user accounts and their passwords to the new server. This works for sql 2005 and sql 2008 servers. Here is the link to the orginal article http://support.microsoft.com/kb/918992 This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, and Microsoft SQL Server 2008, on different servers. For more information about how to transfer the logins and the passwords between instances ...
In SSIS derived columns can be a very useful tool. You may notice, however, that it appears there is no function to compliment RIGHT. If you use the function RIGHT, SSIS will trim characters from the right a certain number of characters. For instance, (RIGHT, “Brad”, 2) will return Br. Let’s say you need to return a column that contains a store number and a UPC in the same column separated by spaces. All the store numbers are not the same length; some are 4 digits, others 5. On the other hand, a...
So last week, I was having issues with some fields that needed to be migrated from one CRM to another, but where different data types (see previous blog). Since the Scribe Insight formula editor couldn’t handle my monster SELECT CASE statements, I was forced to find another way. Today, I think I may have found it. I have decided that I need to pull the data into SSIS and manipulate it. I used a third party app to copy the data from one CRM and create the tables on my local machine. Then, I conve...
In some instances you will want to check to see if another package was successful or failed before running a second package. If you have it set up as a master package calling a child this is easy. What if the package is in a separate job, not associated with the first package at all? You could have the first package write the results to a database or file and then read that in with an execute SQL task in the second package. There is an easier way to accomplish this. Simply set up the first packa...
The problem I am trying to solve is a rather simple one. I have an incoming field from a database that looks like this: -S HCUS_SC12 -E -d COOP2000DB -q "EXIT(DECLARE @R INT EXEC @R=USP_LoadT800 SELECT @R)" What I am looking to do is parse out the some of the parameters, the -S and -d . I found it somewhat complex to do in T-SQL, because there are numerous variations in the above text, sometimes with slashes, other times without either an S or a D and it made the code somewhat difficult to read....
In a recent Data Warehouse Quick Start I was helping a company build a cube. They wanted to change the Name of the Data Source View. I knew this would cause a lot of other items to break in the project. One of the employees gave me a tip. Hit Ctrl+F and search the XML through the entire project and change the name any where you find it. This worked great and saved a lot of time. This will work with any project in visual studio. Be careful though, if you have other items with a similar name it wi...
In this blog I will shed some light on exactly what goes on behind the scenes when using data compression in SQL Server 2008. Hopefully this will better help you decide which option is best for your scenario without taking a stab-in-the-dark approach to it. When you add compression to an object in your database, you are storing all of the fixed length data types as variable length and then adjusting the data type by determining how much space is required for that value. In ROW compression, for e...
I am always looking to improve the performance of my SSIS packages. I try to do as much work within the package as possible, only going to external stores (databases, flat/raw files) as little as possible. There are a number of reasons for this. Raw files are incredibly fast and very good when you need to have multiple stages within your package. I use Raw Files to store about 20 GB of data which is broken out among 8 threads. This was necessary for a number of reasons one if them being the abil...
This past weekend I was a presenter in two sessions at the SQL Saturday in Tampa. First, I just want to say what an event. The lunch that was served set a new precedence for all of the upcoming SQL Lunches. We had a sit down meal with silverware and plates. Well over and beyond the sandwich, chips and cookie you will get when attending SQL Saturday in Baton Rouge, but I digress. Thanks to everyone that helped to put on such a successful event. My first session was IRON Chef America SQL. I acted ...
Well, this isn't going to be a long blog post. One of the tasks facing many of us is how to clear tables with foreign keys : we want to truncate those without any foreign keys referencing them, and delete the rest in the correct. Here's a script I've used as a basis to do this just that. Not 100% sure it's perfect yet, if I find issues I'll post them here. You pass the SP a schema, and it gives you a SQL statement to run. Some of the @temp table stuff could have been done with CTEs, but I saved ...
Several months ago I blogged about why Checkpoints are a great feature in SSIS to use. It seems with every feature there are potential problems you can run into and it is no different with Checkpoints. The major problem or what some people would call a feature is when Control Flow tasks are run in parallel checkpoints act a little erratically. The obvious solution for this is to not run tasks in parallel and instead have all your tasks kick off sequentially. Here’s an example of the problem: Fir...
For the past month, I have been working on a migration from a certain CRM system to Microsoft Dynamics CRM. The process has been long and grueling, but quite a interesting experience. For those of you that are not too familiar with CRM, it stands for customer relations management. It stores all of our sales leads, accounts, contacts, etc. We have managed to get the Dynamics up and ruinning, and all of our customized fields and requirements are in. This week, I have been working on migrating all ...
Over the next two weekends there will be three SQL Saturday’s held, in Tampa (1/23), Boston (1/30) and Richmond (1/30). I will have the privilege of not only attending two of the events, but I will be also speaking in Tampa and Richmond. If you have yet to attend a SQL Saturday there is time to make to any of these events. These events are free and a lot of fun. Visit the SQL Saturday home page for information about events in your area. For those attending the events in Tampa and Richmond I look...