SSIS: Export more than 255 columns from SQL table to Excel
Written by: Mr.ML
A few weeks ago, I was looking to export more than 255 columns from a table in SQL to Excel using SSIS. I thought this task was going to a piece of cake because in SSIS, within the dataflow task, you get an Excel Destination task and this will take care of all my woes! But alas, it didn’t and I have spent the past 2.5 weeks, not all 8hrs a day, looking for a solution.
The setup: A table has over 255 columns and I’m using office 2010.
I went all over the place looking for a so...
SQL: Working with daylight savings
Written by: Mr.ML
There are a lot of scripts out there that deals with daylight savings. I never really had to deal with it because I’ve always just use UTC/GMT and now that I’m in Arizona, daylight savings … ha … what’s that all about?! Unforunately, I have to deal with it recently in a project I’m working on. The hours we get from the source is in PST and the client, they want it converted to eastern time. As I said, there are many scripts out there that does the trick an...
adding timestamp to a filename base on some criteria
Written by: Mr.ML
This morning, I focused on coming up with something for a client. For the files that we generate, they wanted the timestamp to be either rounded up or down. If it is less than 15 minutes, round down to the nearest hour; else round up to the nearest hour.
So the logic I can up with is as followed:
declare @a datetime select @a = dateadd(ms, dbo.fn_GetOffSet(4, getutcdate()), getutcdate()) select filestamp = case when DATEPART(hh, @a) between 01 and 09 and DATEPART(mi, @a) < 15 then conve...
adding timestamp to a filename base on some criteria
Written by: Mr.ML
This morning, I focused on coming up with something for a client. For the files that we generate, they wanted the timestamp to be either rounded up or down. If it is less than 15 minutes, round down to the nearest hour; else round up to the nearest hour.
So the logic I can up with is as followed:
declare @a datetime select @a = dateadd(ms, dbo.fn_GetOffSet(4, getutcdate()), getutcdate()) select filestamp = case when DATEPART(hh, @a) between 01 and 09 and DATEPART(mi, @a) < 15 then conver...
SSIS: adding a dynamic date to filename
Written by: Mr.ML
This past week, I worked a lot with SSIS, more often that I’m used too and I foresee working on it a lot more in the future. It is actually a pretty cool application. I don’t know why I shyed away from it before.
So this past week, I had a bunch of files that were being created via SSIS and one of the requirements were that the file names had to have a specific timestamp as its suffix followed by a .xml extension.
In SSIS, I created a variable and gave it the file name. In the f...
Networking equipment
Written by: Mr.ML
Yesterday, I was talking to co-workers about network cables and such. I wanted to know if there was a place where I can get some cat5e or cat6 cables for cheap. I also wanted to know the difference between the 5e and 6 because I have fiber optic running straight into my house and right now, the switch between my router and the fiber is only a 10/100.
I was told that the best place to get inexpensive but good quality cables and 1gb switches was at monoprice.com. I have never heard of them u...
Networking equipment
Written by: Mr.ML
Yesterday, I was talking to co-workers about network cables and such. I wanted to know if there was a place where I can get some cat5e or cat6 cables for cheap. I also wanted to know the difference between the 5e and 6 because I have fiber optic running straight into my house and right now, the switch between my router and the fiber is only a 10/100.
I was told that the best place to get inexpensive but good quality cables and 1gb switches was at monoprice.com. I have never heard of them u...
hahaha: update statement
Written by: Mr.ML
This afternoon, I had to put together a SSIS package and inside it, I needed to write an UPDATE statement. The only problem was that I haven’t written an UPDATE statement in quiet a while and had to pull up SQL BOL.
It was pretty funny and embarrassing at the same time.
Fix long running jobs without database modification
Written by: Mr.ML
During an interview, the following question usually comes up, “How would you troubleshoot a long running job?” and most of the time the answers goes into details such as:
1. open up perfmon and identify the bottlenecks
2. use sql profiler or server side trace to identify the offending queries
3. obtain an execution plan and determine if any index are missing or one need to be created
4. create the needed index and test to see if it helps.
Those are some of the acceptable answers; ho...
New job, new challenges?
Written by: Mr.ML
I left my old employer after 6 months and started a new one yesterday. The old one had potential to be a great except that the projects kept getting sided line leaving with not very much to do. And without doing, you start to forget what you know, which then weakens you.
When I took my old job, I was very excited. This new job, I will withhold judgement until I get to know the team, workload, and environment. There won’t be any more posts from me for a while until I either learn or...