Scripting tables inside of SQL Server Management Studio is a very simple task. Just right click and select script table. The problem is that, but default, the indexes on a table are not part of the script. Rather than scripting the indexes separately from the table you can tell management studio that you want the indexes included. Click on Tools then Options and expand the SQL Server Object Explorer section on the left side. From there select Scripting and scroll down to the section for "Table a...
Read More
This Saturday is Code Camp in Jacksonville, FL. There are a lot of great speakers lined up, some of which you may have seen at other events such as SQL Saturday. If your free on Saturday come on out and have fun! The event is free, just make sure to sign up. All the details are on http://jaxcodecamp.com/ and the most important information will be below. I will be presenting a session with Devin Knight on Intro to SSIS. There will be several other Pragmatic Works fellows there as well. Be there o...
SSIS packages need to have the ability to be dynamic. To an extent we are able to accomplish this through the use of Configuration Files, Execute SQL tasks with results written to variables and even the use of the script task. One great way to make an SSIS connection manager dynamic is through the use of Expressions. Kyle Walker recently posted a blog here on BIDN about setting the location of an Excel file in the connection manager using an expression. This expression basically uses a location ...
By default the functions MAX and MIN do not count NULL in their evaluation of your data. If we have a column containing only dates for instance and there is a NULL date, MAX and MIN will both ignore that value. For instance, take the following data set as an example. WorkID StoreID EndDate ----------- ----------- ----------------------- 1 50 NULL 2 10 1900-01-01 00:00:00.000 3 20 1925-01-01 00:00:00.000 4 30 1950-01-01 00:00:00.000 5 40 1975-01-01 00:00:00.000 6 19 2010-01-01 00:00:00.000 7 34 2...
Executing an SSIS package from within a stored procedure is not an uncommon request. People sometimes scratch their head when it comes to figuring out if that package ran successfully or not though. There may be a process that runs a stored procedure and kicks off a certain SSIS package depending on the situation. Maybe there are parameters that were used in the stored procedure that need to be passed to the SSIS package. Either way you’ll want to know if the package executed successfully or not...
On a recent project that I was working on we came across an issue where we needed to replicate several databases on a new server. Much of the ETL had places where tables were truncated and reloaded. Normally this is not an issue at all, simply TRUNCATE TABLE and away you go. This does however cause a problem when you have a replicated database. According to the Microsoft documentation, which I obviously haven't read all of, you can't use TRUNCATE TABLE where the tables are referenced by a foreig...
Recently while creating a script to migrate the new holding tables over to the stage and production machines I ran into a little issue. The holding table we are using in this case houses several different sets of data for our migration project. Tables that are used only for going from the old system to the new (which will be going away in a few months) and tables that will be used in the future while on the new system. In order to keep these tables separated we decided put them in different sche...
Want to get a little bit of free SSIS training? Have some free time during lunch to listen to me talk? Join me tomorrow as I present a lunchtime session for SQL Lunch at 12:30 pm Eastern, 11:30 am Central. The topic will be looping in SSIS. It is a great opportunity to learn a new topic if your new to SSIS or brush up on looping if you haven't done it in a while. You will find all the details below. Add to Outlook : Add to Calendar Join Meeting: https://www.livemeeting.com/cc/usergroups/join?id=...
There may be a day when you need to create your own comma delimited list from data contained in a SQL table. Sure you could build a cursor and go through each row adding a comma after each record. The good news is that there is an alternative. The better news is that this method is easier, faster and takes MUCH less code. COALESCE in SQL has many uses and building a list is one of them. To do this in our context use the following format: COALESCE(@Variable + Delimiter , WhatToDoWhenThereAreNoMor...
I recently came across a situation where I needed to find all fields in a database that contained a particular word. All the fields that were labeled Indicator were being changed to Flag. For instance, ActiveIndicator was changing to ActiveFlag. The first part of the challenge is finding all those columns. Luckily there is a way to query the metadata of your databases. There are several ways to do it; I will just show you two methods to do this from inside management studio. First Method: SELECT...
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. Welcome to SSIS from the Ground Up Part 3: Connection Managers. This week in our series we are going to be talking about connection managers (yes, I know that is very redundant, but I wanted to make sure ...
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 control flow is the portion of the package that shows when you first open a ...
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....
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...
It's fast, easy and free! Submit articles, get your own blog, ask questions & give answers in the forums, and become a better developer, faster.
enter your email address: