I ran into an issue yesterday that turned out to be a small topic, but very important. The package that I had created was a group of loops to bring in files for an import process for the client I was working for. The files were compared to a list in a database and then processed. If there was no match found the files would be moved to an alternate location, an administrator notified and the process would start over 1 minute later. The problem occured when new files were added to the file system....
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...
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...
When running packages that contain executable and batch files you may sometimes come across an issue where you need to confirm that you want to open the file. This can cause an issue if you run the package unattended. Obviously, this is a security precaution that is built into the many of the newer versions of windows. You have to authenticate sessions, run as administrator, and confirm that you want to open and run a file that could possibly pose a security risk. We know that we want this file ...
Occasionally there may be the need to remove the date from the end of a string. This can be accomplished by using PATINDEX along with SUBSTRING. We will use PATINDEX to find where the date begins and then SUBSTRING to remove the date from the end of the file. We will use ClientName03302010.csv and we want to get ClientName.csv from that to process. PATINDEX requires an expression to search for and an expression to search from that we will be returned a number, the location of the searched for ex...
As you can see we have two tables, with the same values. Joining on these tables is no problem at all. Simple, right? Well, take a look at what happens when we try to join on the column containing the NULL values. The results are the following: As you can see, we are missing a row. The row containing the NULL values did not get picked up by our join. We should have all three rows in the result set. The join statement does not deal with NULL values well when joining. So we can use ISNULL to repla...
Package Configurations in SSIS are key to making your packaged portable across the entire development process. The problem you being to face with configuration files is the quantity that you collect. Recently I was working on a project where we had pacakge level configurations as well as a configuration for each connection. In order to to updates on this would have taken a very large amount of time, especially since I had to make configuration files for dev and production. I opted to go with con...
There are a number of ways that you can calculate dates using T-SQL. A common practice is to find the first day of the week which can be accomplished easily by using the following function. select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) However, you will notice that this returns the date for Monday as SQL Server uses Monday for the start of the week. If your week starts on Sunday or you need to get the date for the previous Sunday you can simply modify the DATEADD part of the query from adding ...
I recently came across a situation where I needed to make a pause in a package. There was a batch file that ran in the background and the client wanted the package to be sure the package didn’t proceed until that process was finished. So in the middle of this package I needed to make a 10 minute pause to be sure that the batch file had time to run. After doing a little thinking and looking I decided I didn’t want to write a script to do this. There is a function in SQL that will do this nicely f...
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 I ran into an issue where my configuration files were not properly passing values into a SSIS package. Upon further investigation I found out that the reason this particular task was not being configured correctly was because of a duplicate entry in the Configuration Table. This led me to look and see if there were more of these duplicate entries, to which I discovered over 60 of them! Rather than sorting your table by the...
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...
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 This past week I ran into an issue where I needed to use a .dbf destination in my SSIS package. All was going well; I set up the destination and pressed the preview button to make sure everything was great, and BOOM! Blew up in my face. Not cool. So if you happen to be having issues setting up a connection for a .DBF here is what you need to do. Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 1. U...
After my first day of SSIS training my boss walked into the office and said to me, "how was the first day of class". To which I responded, "great!" Then asked me, "so what is the difference between the control flow and data flow?" This posed a simple, but foundational concept, of SSIS. There are a few key things to remember when talking about the differences between control flow and data flow, and not just the completely obvious statement: data flow deals with data. Hopefully if you are beginnin...
If your like me, then you may have a little OCD and need everything to be completely organized in your environment. BIDS has been annoying me in this respect lately. I am working on creating a data warehouse and will be starting on the next portion of the project shortly. I wanted to keep everything for it in the same solution, my warehouse ETL as well as the work on the Cube. So I figure, create the solution and add a new project for the Cube. So I create a new project and much to my dismay the...
I recently created a maintenance plan for our server to backup all the databases on a daily basis. In the cleanup step I set it to delete backups older than 7 day. I let the plan run for a week and checked on it to make sure the old backups were being deleted. This would be kind of a useless post if everything was working correctly, so as you guessed, they were not being deleted. I check everything on the plan: Username, folder name, first level subfolders, permissions on the backup folder, sql ...
You may need to do an update on joined tables to get a more conditional update. For instance, I have a Student table as well as an AcademicStatus table. The Student table contains all the students (profound, I know) and the AcademicStatus table tells if a student is in good standing, at risk, or has dropped out based on a StandingID. The Student table also lists a graduation date and a current bit to show if the student is currently enrolled. While generating data for these particular tables rec...
During the recnet launch of the new community site www.BIDN.com I found a few features in Google Chrome while I was testing that I wanted to bring to Firefox. Specifically the search bar functionality. In Google Chrome the address bar doubles as the search bar. This is and is not the case with Firefox. In Firefox if you type a search into the address bar it will do a Google Search and bring you to the first search result. In effect it is like doing a Google search and clicking "I'm Feeling Lucky...
I was recently working with some virtual machines during testing and needed to copy one of the VPCs to a developer. He brought me his hard drive and I proceeded to copy the Virtual Hard Drive onto his external USB drive. The operation failed and I was not sure why. It was a big mistake to assume the drive was formatted in NTFS format, which I figured "why wouldn't it be", as it was actually in FAT32 which has a file size limitation of 4GB. So he changed the drive to NTFS and everything worked pe...
Have you ever opened your report server only to find a blank section of nothing-ness where your reports should be? The solution to your problem is more than likely in the permissions. The easiest fix for this situation is to right click on IE and select Run As Administrator. There are, however, ways around having to run IE as an administrator each time you want to view reports. If disabling UAC is not an option you can try to do the following: 1. Run Internet Explorer as Administrator (Right Cli...
Designing a database can be a daunting task, especially on a large scale. There are several ways inside of SQL Server Management Studio to do design. You could create your database and right click on the ‘Tables’ node and enter all the information there. Column names are listed out followed by their data types with all the properties being set below. This is a quick and easy way to edit your tables as well as design them. The issue occurs when you start to set table relationships. A lot of peopl...
Have you ever created a table thinking everything looked amazing and was designed perfect only to come back and need to add to it? Maybe you need to change a data type, add a column, delete a column or move a column. It's an easy fix, right. Just right click on the table, go to Design mode and make your changes. Well, this would be a pretty useless story if all that worked flawlessly. As you close the table to save all your great, wonderful, glorious changes you are greeted not with a screen tha...
64-bit or 32-bit, Windows Vista or Windows XP? These are just a couple of the questions asked by today's product testers. With the computing world we are in today, you cannot expect an initial build of a product to work in every environment. In order to make sure your products operate as planned in the real world you must test! Product testing is a key part of the development process. The right setup and software environment can save time and money during this critical step. We opted to go with ...