Monthly Archives: January 2009

SSIS Importing Files

So I ran into an interesting problem that I haven’t found before on an SSIS import for SQL Server 2005. I was trying to import a large (2+ mil rows) text file that was a Ragged Right format and for some reason after 1.8 mil rows it would fail with the following error message.

[DTS.Pipeline] Error: Column Data for Column "Client" overflowed the disk I/O buffer

[DTS.Pipeline] Error: The PrimeOutput method on component "Client Source" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

I like any person trouble shooting something went out to google and searched for “overflowed the disk I/O buffer”. I found a few good links on it

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/e312612c-acbf-45e3-b773-3fd84466b4fb/

http://www.bigresource.com/MS_SQL-Column-overflowed-the-disk-I-O-buffer-V666XXcc.html

But none of these worked for me. I attempted to change the [LF] to [CR] to fix my problem. I tried different nvarchar and Unicode strings to allow these fields to come in but nothing ended up working.

Finally I had to open the file and look at it. I couldn’t easily open a 2 GB text file on this server through notepad so I wanted to find something else that could open the file quickly and let me see what was in the file. I asked on Twitter what others had used and several came up with UltraEdit. I went ahead and downloaded the trial and opened up the file it opened very quickly and worked perfectly to quickly show that at the 1.8 millionth row that it had been failing on it didn’t actually end at all. There was no [CR] or [LF] and the rest of the file was just filled with empty space so this data was trying to import the empty rows into all one field causing the overflow.

I’m not trying to make this a commercial for UltraEdit but I would suggest some sort of editor that can help you out when you’ve run through all your other methods. If you import lots of files with SSIS and many different formats then you should consider some sort of editor that can save you some time.

I’m working on a follow up post on my “typical” things I do in SSIS to try and get files to import.

Pat

Evaluation Edition(Days Remaining)

So I’ve run into an interesting problem that I don’t think I’ve had to deal with to many times and hopefully most of you have not either. How do you find how many days left in your trial of SQL Server?  I am in the process of purchasing a MSDN license through my company so I’ve been running on an Eval copy of SQL 2008.  One that I got from one of the many MS events I attend.  I have no doubt that I will be moving to 2008 but since I didn’t have an MSDN license the only thing I could use was a Eval copy. 

Unfortunately it’s taking a long time to get the purchase through so I was curious to see how much time I had left before my copy expires.  Unlike many products that tell you every time they open how many days they have left SQL Server does not.  I was able to locate it though in Help and About. As shown below though it’s not very helpful since it cuts off the column telling you how many days are left. 

image

So you can either choose the copy info button and paste it into some text file.  Or just Expand the column at the Top this will tell you how many days are remaining. 

Hopefully you never find yourself to close to the end of an evaluation of software but just in case you do now you have a way to check. 

pat

2009 New Year Resolutions

Ok this post is a little misleading since I don’t make resolutions.  I make goals for the year.  So I’ll share those here in each of my different area’s. 

Computer/SQL/Professional

1.  I will deep dive farther into SSIS and DW to truly produce an end to end BI solution for my company.

2.  Manage my team to become more effective.

3.  Train my team better and give them the tools they need to succeed.

4.  Deep Dive into SQL 2008 and future SQL products I can get my hands on. 

Volunteer/Blogging

1.  Change the volunteer program for PASS to better suit the needs of the organization.

2.  Blog at least 2 times weekly. 

3.  Speak more and Help more at the SQL Server user group. 

4.  Hold 3 code camp events.  (Utah code camp spring and fall, and a PASS Community Connection(march 2009) )

5.  Re-start the SLC IT pro user group. 

6.  Write at least 2 articles.  I’m not a good writer so this will be hard for me.

As you can see some of these are vague while others are specific.  I know that you should try and make goals specific to follow. For me  sometimes it doesn’t go the way I intended but it still ends up getting what’s done and what is needed so my goals are similar in that they give me the direction I want to go but not necessarily the entire map.  When I sit down everyday I try and review these items and find things that help me to make that map and get to my goals.  Hopefully this year I will be able to meet all of these.  The hardest one for me will be the writing.  But I’m already working on a solution to that.