Configurations in SSIS are a lovely tool that when implemented correctly can greatly reduce package maintenance. It is very possible to over configure a package, so be sure that you only do what you need to in order to make life easier. Sometimes too much configuration can equal confusion. So the big questions, what is a configuration, when should I use it and how do I use it. Let’s tackle that first one then roll right into the other two. What is a configuration? In simplest terms a configurati...
Read More
One of the most annoying processes with SharePoint is backing up a site collection. There is a build in tool that you can use but it is kinda clunky and can take a while to perform the backup. I used it once and was not impressed. PowerShell provides a quick and easy way to do site collection backups. My favorite part is that you can do the entire backup with one, uno, un, 1 (that’s all I know for the number one so… yeah) line of code. Just a little something to be careful of, you need to use th...
It has been a few weeks since I last posted. I know, I'm a slacker, thanks for pointing that out. I have a little bit of a pipeline that I am working on getting populated and just wanted to toss out some of the topics that I have in consideration for upcoming content. If you happen to have something you would like to see on here please feel free to email me or leave a comment. The more feedback I get on what people want to see the better your experience will be coming to this site... and yes tha...
There will inevitably come a day when you want to take a look at the metadata of the tables inside a database. This can be accomplished one of many ways, the quickest and easiest is to expand the columns node inside the Object Explorer in SQL Server Management Studio (SSMS). What if you want to look at your entire database though? Running a query against INFORMATION_SCHEMA.COLUMNS is a great way to do this. This will get you much more in depth information about your tables with minimal work. I c...
I want to say a huge thank you to everyone who attended the webinar from Pragmatic Works ( website ) that I did on Tuesday. If you missed it the topic was Dynamic SSIS: Configurations, Expressions and Variables. I got a lot of good feedback from people who attended saying it was helpful. It’s always nice to know that someone got something out of a session. I know I said I was going to be posting some samples and that sort of thing along with the link to the recording but I haven’t quite gotten t...
If you were able to make it out to SQL Saturday in Pensacola this past weekend I want to say a big thank you! We had a great time at a very successful event. I was able to help out a little and take on an extra session from Julie ( Twitter | Website ) who wasn’t feeling well and was unable to make it down from Atlanta. One of the three sessions that I presented on was Dynamic SSIS where we talked about using SSIS configurations, expressions and variables to make everyone’s life easier. Consequen...
If you happen to be in the area of Pensacola this weekend, say Saturday, then you should hop on over to Pensacol State College, Main Campus, 1000 College Blvd, Pensacola, FL 32503. That just happens to be the location of SQL Saturday that will be held this weekend in Pensacola, FL. Looks like we have another great lineup this year for Pensacola so come out and get your free learning on. You can grab the event schedule from the SQL Saturday 77 event page. Below is all the important information. D...
Hopefully if you came to my session this year at SQL Saturday in Jacksonville, FL you came away with some small piece of knowledge you didn’t have when you walked into the room. If nothing else you now know that there is a phenomenon known as Robocop on a Unicorn . On to the good stuff, the recap. Don’t be afraid of SSIS, but don’t use it for everything. Be sure to choose the right tool for the job at hand. SSIS can do a lot of things very well, but that doesn’t mean that it SHOULD do certain th...
There will come a day, if it has not already come and gone, when you will want to search through your stored procedures because you don't remember which one did what you want to do. Did that make sense? Because I don't know if it did. Anyway, here is what we have... You A bunch of stored procedures Something you are looking for inside one or more of your stored procedures A long night ahead opening each one and using CTRL + F Luckily there are a couple of super easy solutions. Below are two such...
We are just about three weeks from SQL Saturday Jacksonville. It is going to be a wonderful time as usual. If you have never been to a SQL Saturday you are really missing out. It is a day of free training from people who work with this technology day in and day out. In addition to the wonderful free training that you will get it is a magical place to network with other professionals. I will be doing a presentation on SSIS. So if you are new to SSIS stop by and join me for about an hour. I may or...
Checkpoints in SSIS are a great tool and they definitely have their place. The downside to them is that they are not so good when you start talking about parallel processes or containers. What follows is a little bit of information and words of caution toward some situations where you may want to use this handy feature. Let's begin with parallel processes. A while back Devin Knight wrote up a blog on this subject and I just wanted to pass along some new information that I have found and expand u...
Perfmon is a great tool to get a closer look at what is going on in your environment as queries and packages run. There are a ton of counters ranging from OS to third party. In our case we are concerned with SQL Server. I know, that was pretty obvious. The problem is from time to time things break, and therefore counters can disappear. It could be something as simple as a registry key doesn't get updated correctly when a hotfix is installed or the common occurance when an in place upgrade from 2...
When doing calculations in SQL Server queries it is important to know that anything without a decimal is going to be treated as an integer and therefore will cause the result to be an integer as well. For instance, lets take 598/128 and look at some results. This will obviously give us a decimal for a result...or at least it SHOULD. SELECT 598/128 = 4 This is clearly NOT an even 4, however SQL has decided to round the result even without us telling it to. Now lets take a look at SELECT CONVERT(D...
I have recently been dealing with a situation with a client where the transaction log has become an issue. Naturally during the research portion of trying to figure out what is wrong I wanted to find things like the size of the transaction log or what the autogrowth is set to. You can find out much of this information through the UI relatively easily. However, it becomes a pain when you are checking on multiple databases to have to keep looking up the same information. Luckily everything you cou...
I just wanted to give everyone a quick update on some of the awesome things that are going on this year. One of the most rewarding parts of my job is that I get a lot of knowledge that I can go out and share with everyone to help others do their jobs a little better. There are a lot of wonderful ways to get involved in the SQL community and it is something that has helped me out many times over the ccourse of my career. I don't say this to make me sound like I am sooo smart, I am just like every...
After a quick note from Jorge Segarra aka SQLChicken I have made a quick change to the Full backup model below. Just a clarification and fix on the transaction log portion of the backup. Database recovery is a very important thing, especially to DBA’s, and the occasional developer who screws something up. Please note, I am a developer and I would NEVER mess ANYTHING up in a database. I treat all databases equally and with respect. Anyway, we are getting off track. There are three recovery models...
The problem once you get over the learning curve of SSIS is not the tool; it’s all the things that work with SSIS and that can be used by/with SSIS. For instance, there are a ton of things that you can do with an Execute Process task. The other thing is trying to make your packages dynamic. Combining those two hurdles can sometimes be a lot to overcome. I ran into one such instance recently. There was a set of packages that called batch files in order to do some downloading from an FTP. The issu...
If you have ever wanted to query the SQL Server job tables then you know they are a little cryptic. The following query will assist you in getting some useful information out of those tables. I recently wanted to be able to query these tables and get the error for when there was a failure during the running of the job. This can be accomplished using this query and just adding another couple of fields. You can add the job history (jh alias in this query) sql_severity and message columns. Then you...
This past weekend was SQL Saturday 62 in Tampa, FL. The event was great! There was a wonderful turnout and Jose and Pam did a great job on all aspects of the day. I got the wonderful opportunity to drive down Friday night after work and arrived around 12:45 am on Saturday morning. This wouldn't have been so bad, except I had the first session of the morning kicking off at 8:30. Enough complaining from me though. The cafeteria was full, probably with people wanting to get some doughnuts, but I wi...
Tomorrow is SQL Saturday 62 in Tampa, Florida. If you live in the area be sure to come out. There are going to be a lot of great speakers there covering a wide range of topics. The higlight of the day is sure to be the Iron Chef competition. Many of the people from Pragmatic Works will be there including myself, Devin Knight, Adam Jorgensen, Brian Knight, Mike Davis, Brain McDonald, Jose Chinchilla, Jorge Segarra and anyone else that I may have forgotten (SORRY if I forgot to mention you!!) In a...
Normal 0 false false false EN-US X-NONE X-NONE First and Foremost: What Is Parameter Sniffing? When a stored procedure is executed the first time the query analyzer compiles the stored procedure and creates an execution plan. This part of this involves looking at the parameters to see how they are used and optimizing the execution plan. This plan is then cached and used in subsequent runs of the stored procedure. The issue here is what if you pass in a parameter that brings back 10 records on th...
Normal 0 false false false EN-US X-NONE X-NONE CTEs are a great thing to use in your query. They are an excellent way to consolidate code and make it readable as well as create a “view” inside of your code. One great thing I have used them for many times was to pick out the max date associated with a set of key values. So let’s say there is a table that has a column called ID, Quantity and Date. And in this table there are duplicate IDs with different quantities and dates associated with them. S...
Normal 0 false false false EN-US X-NONE X-NONE There is an issue with script tasks in SSIS 2005 that could come up occasionally. It is generally an issue that shows up in a 64-bit environment where the script can’t be pre-compiled. The error that will show up states that “The task is configured to pre-compile the script, but binary code is not found.” There is not a fix for this inside SSIS as you can’t set the script to Pre-compile false as the error will just change to the script can’t be comp...
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...
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...