Who is online?  0 guests and 0 members
Home  »  Blogs  »  kylewalker

Communifire Blogs

Blogs RSS Feed

kylewalker : Most Recent postings

kylewalker

Kill them all... Your SPIDs of course!

6/25/2012 by kylewalker  -  Comments: 1  -  Views: [2763]

I had a pretty common task to perform the other day. And that task was to simply restore a database on a development server. Now, restoring a database in and of itself is a very simple thing to do. So simple, it's probably one of the very first things you learn to do when learning SQL Server. However, when you're learning SQL Server on your personal laptop or desktop, you don't have a number of other people connected to the database. You may have others connected when in a work environment. When...

Read More

kylewalker

Pull Data from a Sharepoint List with Task Factory's Sharepoint Source!

8/17/2011 by kylewalker  -  Comments: 0  -  Views: [3722]

With the increasing popularity of Sharepoint sites in a lot of shops, wouldn’t it be great to have an easy way to extract data from your Sharepoint lists!? …Well yea. That would be awesome! Fortunately, Pragmatic Works offers such an option for you. Ever since December of last year, Pragmatic Works’ Task Factory product has offered a Sharepoint Source component as one of over twenty-five tasks and components that were developed to make your life, as an SSIS developer, a whole ...

Read More

kylewalker

Job Step Settings for Executing Packages Containing Excel Componenets on a 64-bit Machine

8/16/2011 by kylewalker  -  Comments: 0  -  Views: [2948]

Many of the SSIS packages you'll see on the job might use Excel workbooks as a source or destination for your data. Another trend that's becoming increasingly common is the use of 64-bit machines for your ETL processing. However, with the combination of these two things comes a little quirk with SSIS. Quite simply, the Excel data provider doesn’t work with 64-bit machines. Now, as many of you know, there is a way to force your package (that is on a 64-bit machine) to run in 32-bit mode whi...

Read More

kylewalker

HRESULT E_FAIL Error when trying to add Breakpoints

8/12/2011 by kylewalker  -  Comments: 0  -  Views: [5057]

I ran into a small problem this morning when trying to test an SSIS package. I simply wanted to add a breakpoint to a Foreach Loop Container to pause the package at the beginning of each iteration of the loop. Pretty straight forward, yes? Well I thought so too. And what made this case all the more confusing is that I had done testing on the same package the day before and I was able to add the breakpoint with no issues. So here's what happened. I right-clicked on the Foreach Loop and clicked Ed...

Read More

kylewalker

Parameters Don't Always Work in your Execute SQL Task

8/5/2011 by kylewalker  -  Comments: 0  -  Views: [6945]

Chances are, if you've used SSIS in any capacity, you've used an Execute SQL Task. I would say that about 95% of all the packages I write include at least one. And if you're a fan of making your packages dynamic, chances are you've also probably used the Parameter Mapping tab in the task. Well most of the time this is fairly straight forward and easy to use... For example, you can simply place a question mark in your SQL statement on the general tab and then select the package variable you want ...

Read More

kylewalker

Free Webinar on SSRS Report Subscriptions! Brought to you by Pragmatic Works

7/13/2011 by kylewalker  -  Comments: 0  -  Views: [1882]

Hello everybody! I wanted to take a minute tonight to remind you all of an opportunity this Thursday (July 14th) to see a free webinar on Report subscriptions. This webinar is part of "Training on the T's" series offered by Pragmatic Works to the SQL Server and business intelligence community free of charge. This Thursday you'll get a chance to see some real world subscription requests and how to implement them. I will also be coving some neat tricks that incorporate some SSIS packages, so I'm s...

Read More

kylewalker

How to Match Your Subcription Names with its Job Id

6/28/2011 by kylewalker  -  Comments: 2  -  Views: [6148]

If you've worked on any reporting solution using SSRS, you may have been able to take advantage of subscription feature. Subscriptions allow you to schedule a repeating or one time request to receive a specific report at a specific time. Now, you create the subscription in a very simple, straight-forward interface on the report manager, but what you're actually doing is creating a job on your report server with a schedule associated with it. Just like a job you would create for you SSIS packages...

Read More

kylewalker

SQL Server Compatibility Issues with SSIS Packages Converted From DTS

6/14/2011 by kylewalker  -  Comments: 0  -  Views: [5766]

If your business is in the process of converting old DTS packages to new SSIS packages, you will quickly notice that many of the components used in DTS are not easily converted to SSIS native components. Something you may also want to keep in mind is that even the easy to convert components might have content within them that will need some modification. For example, a DTSExecuteSQLTask from you DTS package can easily be converted to a native SSIS component (Execute SQL Task). However, there are...

Read More

kylewalker

Want the Functionality of a CASE Statement in a WHERE Clause?

5/10/2011 by kylewalker  -  Comments: 1  -  Views: [5519]

You may find yourself wanting to apply the functionality of a CASE statement inside a WHERE clause in one of your SQL scripts or stored procedures. Although you might get you case statement working with a lot of finagling (I have never got it to work the way that I wanted and I'm not entirely sure that it's possible at all), you may want to consider just pursuing an alternative method. You can apply carefully placed "and" and "or" logic to your WHERE clause that can achieve the same goal. For ex...

Read More

kylewalker

Query to Find a List of Jobs Running on your Server

4/21/2011 by kylewalker  -  Comments: 0  -  Views: [10568]

Usually I decide what to blog about by running across something cool or challenging during work. But every once in a while I like to share something that I've received a number of questions about in a short period of time. Ya never know who else out there has the same question and might not have someone around to ask. A couple weeks ago, on two separate occasions, I was asked how you can get just a simple list of all the jobs running on your server. I know a lot of times it's easy to right click...

Read More

kylewalker

Action Packed Maps in Your Reports

3/31/2011 by kylewalker  -  Comments: 4  -  Views: [3693]

A couple weeks ago I went over some tips for customizing your maps in Reporting Services 2008 R2. This week I'm going to stay on the same topic, but instead of customizing your legend ranges , I thought I'd go over a pretty cool interactive option that is available. Now, the action I'm talking about is just like any other action that you can set to any object within your report, but the reason I like this so much is that you can apply the action to a spatial representation of your data. The firs...

Read More

kylewalker

Custom Distributions for Map Legends in SSRS

3/16/2011 by kylewalker  -  Comments: 1  -  Views: [10576]

If you haven't experimented with maps in Reporting Services 2008 R2, I strongly encourage you to find some sample data and play around with the map options that are available. Nowadays, most shops deal with some sort of geographic or spatial data, whether it's power usage by county, sales by state, or even something like social networking activity by country. And the fact of the matter is, a plain tablix can be pretty bland. Adding even the simplest of maps could liven up your report and grab th...

Read More

kylewalker

Cleaning and Separating Data Using an SSIS Script Component

3/7/2011 by kylewalker  -  Comments: 2  -  Views: [9138]

A couple weeks ago we received a forum post here on BIDN about a messy column coming out of a flat file source. The example of the value given looked like this, "1a2bb3ccc4ds5fsdf6ter", and the desired output was all of the letters in one column and all of the numbers in a different column. So "abbcccdsfsdfter" in Column1 and "123456" in Column2. The most straight forward method I could think of was to accomplish this was by using a Script Component. And here are the steps to do it. First of all...

Read More

kylewalker

My Connection Manager isn't Saving my Password

2/22/2011 by kylewalker  -  Comments: 1  -  Views: [12470]

When you're working in SSIS, there are definitely some quirks that can drive you crazy. Luckily, most of them have a pretty simple resolution. One example of this is Connection Manager passwords not being saved in your package. Let's say you set up a connection to an Oracle database that looks something like this: Now, when you test the connection, it tests successfully. But when you apply a configuration to this connection (on the Connection String), the password is not saved. So if you try to ...

Read More

kylewalker

Help Determine the Winners of the SQL Rally Race!

2/4/2011 by kylewalker  -  Comments: 1  -  Views: [1766]

For all of you who are planning on coming down to Orlando for SQLRally, May 11th through the 13th, it's not too late to help pick the sessions you'll get to see! We need your help to make the lineup for the inaugural PASS SQLRally conference one for the record books. You can find a list of the sessions that are in the running here . And once you've taken a look at all the sessions, you can vote for 2 sessions in each category that you would like to see at the event here . But hurry! Because voti...

Read More

kylewalker

Delete All Files Except with a Certain File Extension in SSIS

1/25/2011 by kylewalker  -  Comments: 5  -  Views: [10336]

I came across a situation today where I needed to prep a file directory for some work later on in an SSIS package. I needed to delete all the files in a folder that did not end in a specific file extension. I know a lot of people would probably take this opportunity to write a really cool VB script. However, I was not in a VB script writing mood. But I did need a Script Task. What you can do, is use a Foreach Loop Container to loop through ALL of the files in your directory and go ahead and thro...

Read More

kylewalker

Using Accented Characters in MDX Queries

1/20/2011 by kylewalker  -  Comments: 0  -  Views: [2474]

Have you ever been coding away on an MDX query and then out of nowhere, something just stops you dead in your tracks? It happened to me today, but it wasn't a syntax problem or anything like that. It was an accented character in a name that I was referencing. Take the Adventure Works cube for example, there is an employee in the "Employees" hierarchy (in the Employee Dimension) named "Ken J. Sánchez". Unfortunately, if you need to use Mr. Sánchez as an endpoint for a range or a reference of any ...

Read More

kylewalker

JSSUG meeting this Wednesday (1/19/2011)

1/18/2011 by kylewalker  -  Comments: 0  -  Views: [1744]

Hey Everybody, Just wanted to throw out a reminder that the Jacksonville SQL Server User Group is starting back up this Wednesday after a short break for the holidays. If you're in the area you should check it out! We will be starting at 6:00 in Room #236 of the Bank of America Bldg 500 (off of Southside Blvd.). As Scott Gleason informed me, we tend to kick off each new year with an SSIS presentation. So in fear of breaking one of the most time-honored JSSUG traditions, I will be presenting on s...

Read More

kylewalker

Creating a One Row List from Multiple Rows with a Common ID Using a Cursor

1/5/2011 by kylewalker  -  Comments: 2  -  Views: [4577]

A couple weeks ago I ran into a situation where I needed to create a comma delimited list of accounts that shared the same ID. For example, I needed this... to look like this... Now, I'm sure that there is more than one way to go about doing this, but the solution I chose to go with was a cursor. For this example, I have a source table called "Accounts" (looks like the first data set up there) and a destination table called "MultipleAccounts" (will eventually look like the second data set) that ...

Read More

kylewalker

Management Studio not Allowing you to Save Design Changes to your Tables?

12/21/2010 by kylewalker  -  Comments: 1  -  Views: [2939]

This is one that gets me just about every time I start new project... I'll have a table that I created days before, and wouldn't ya know it, I left out a column or two. Then I right-click on the table and select "Design" and insert the column. And I when I want to save my changes, I get the following message: The Management Studio default is to not allow you to make changes to a table that require you to drop and re-create that table. So all you have to do to disable this setting is, go to "Opti...

Read More

kylewalker

Add Line Numbers to your Query Windows

12/21/2010 by kylewalker  -  Comments: 0  -  Views: [3487]

This one may seem like a no-brainer, but it's probably the most asked question I get while I'm on web meetings with clients. I hear it so often that it makes me wonder why it's not enabled by default. And that question is, "how do I get line numbers on my query windows?". Well, it's just one simple setting... Start with the "Options..." box ...and open the "Text Editor" tab and select "Transact-SQL". There, you will have to click the check box next to "Line numbers" Click OK, and then... Voila! ...

Read More

kylewalker

Microsoft Office 12.0 Access Database OLE DBO Connection Manager Error

12/9/2010 by kylewalker  -  Comments: 1  -  Views: [10609]

If you have ever tried to use a Microsoft Office 12.0 Access Database Engine OLE DB Provider Connection Manager to pull data from a .xlsx file, you may have received the following error message when you try to test your connection... If this is the case, there is a simple solution, but one that may not be all that obvious. All you have to do is click on the "All" tab of the Connection Manager and select the "Extended Properties" option. Enter "Excel 12.0" and then try testing your connection aga...

Read More

kylewalker

Changing the Default Script Language on your SSIS Script Task

11/16/2010 by kylewalker  -  Comments: 1  -  Views: [11847]

On many occassions I have worked on SSIS packages that need Script Tasks. And in an attempt to get the package done quickly, I drag over the Script Task open it up and immediately click on the edit button... And right about that time, I realize that I have opened a Microsoft Visual C# script when I wanted to open a Visual Basic script. Unfortunately, once you select "Edit Script...", you cannot go back and change it, you're forced to delete the Script Task and drag over a new one. One thing you ...

Read More

kylewalker

SSIS Build Failing With No Error Messages

10/27/2010 by kylewalker  -  Comments: 2  -  Views: [6857]

Today I ran into a small problem when trying to deploy my SSIS packages to a QA server. I chose to use the deployment utility in BIDS to move my packages, but when I hit "Build" to create the deployment manifest, I was getting a "Build failed" message at the bottom left hand corner of my screen, but no helpful error messages to help find out why the build failed. It turns out the solution was pretty simple. The .dtsx file for one of the packages was removed from the file system, but the package ...

Read More

kylewalker

Show Me What You're Workin' With (the SQL Server Versions Remix)

10/21/2010 by kylewalker  -  Comments: 3  -  Views: [2928]

There may come a time, where you will want to find out what version of SQL Server you're working with. To do this, there are a couple things you will need to know: 1) You'll have to either run a quick script or examine your SQL Server connection to find a version number. And 2) Unless you know every version number for every version and update ever released, you'll need a reference to find out what the version number means. Both steps are pretty simple. For the first, you can run the following sc...

Read More