Importing Excel Data with 64-Bit SSIS

Posted on September 19, 2012 by Brian

I wanted to run a simple import of an Excel worksheet to a SQL Server table this morning, only to find the data flow errored out because I happened to be running a 64-Bit install of Visual Studio (SSIS).

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Turns out there is a simple work-around should you want to just one a 1-time import like I did.  In the project properties, under “Debugging” you can flag if the process should run as 64-Bit or not (See Below).  If you need to do this on a regular basis, just make sure you execute your package using the binaries in “Program Files (x86)” and you should be just fine.  For my needs the 1-time tweak sufficed.

Posted in ETL, SQL Server, SSIS | Leave a comment

SQL Server: Count Available Working Days

Posted on August 8, 2012 by Brian

I wanted a quick and easy way to determine working days for a given date range.  I use this most often in BI/reporting applications specifically when dealing with trending and forecasting but I would assume it has a host of various applications.  In the hope that is the case, I thought I would throw it out here for the benefit of all.

Prerequisites

I want to account for both weekends AND scheduled days off (holidays).  To accomplish that, you will need a simple “holidays” table in the DB to do the final filter against.  Here is the structure I used:

CREATE TABLE [dbo].[ctrl_Holidays](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NULL,
[DATE] [smalldatetime] NOT NULL,
CONSTRAINT [PK_ctrl_Holidays] PRIMARY KEY CLUSTERED

THE CODE

To get the working days, I built a stored proc in SQL Server that accepted a start and an end date.  My calculator is inclusive, meaning the start and end days are counted.  The process is straightforward enough that you could easily exclude one or both of those if needed.

The SP accepts two parameters: @startDate & @endDate, both of type smalldatetime.  Here is the high-level process flow:

  1. Build a temp table to hold each date in the range and a bit indicating if it is a work day.
  2. Determine if start day is a weekend day (using DATEFIRST = 1, Monday) and if it is not, add it to the table
  3. Next, loop while the date is less than the end date specified inserting all dates into the table (Nothing to do with the work day bit is processed, it is NULL at this point)
  4. After looping to create each date, then update the temp table to flag non-work days (in this case DATEPART dw equals 6 or 7 (Saturday or Sunday if DATEFIRST equals 1).
  5. Then take a COUNT of work days from the temp table and subtract it from the count of days falling in the requested range from the Holiday table.  There you have it.

From there you can see the meat of the SP below.

DECLARE @WorkDays INT
DECLARE @BusinessDays INT
DECLARE @dayTracker TABLE (calendarDate datetime, isWorkDay bit);

SET DATEFIRST 1
-- insert our starting date
IF DATEPART(DW, @startDate) = 6 OR DATEPART(DW, @startDate) = 7
INSERT INTO @dayTracker
VALUES (@startDate, 0);
ELSE
INSERT INTO @dayTracker
VALUES (@startDate, 1);

-- add dates into table from start to end date
WHILE (SELECT MAX(calendarDate) FROM @dayTracker) < @endDate
INSERT INTO @dayTracker
SELECT DateAdd(dd, 1, MAX(calendarDate)), NULL FROM @dayTracker

-- update table to tag work days
UPDATE @dayTracker
SET isWorkDay = CASE
WHEN DatePart(dw, calendarDate) IN (6, 7)
THEN 0
ELSE 1
END
WHERE isWorkDay IS NULL

SELECT @WorkDays = COUNT(*)
FROM @dayTracker
WHERE isWorkDay = 1;

SELECT @BusinessDays = @WorkDays - COUNT(*)
FROM ctrl_Holidays h
WHERE h.[DATE]
BETWEEN @startDate AND @endDate;

RETURN @BusinessDays;
Posted in SQL Server | Leave a comment

SQL Server: Fetch all rows from the current hour

Posted on July 20, 2012 by Brian

I wanted to be able to fetch each and every row from the current hour, but I did not know of a way to set the time portion of a DateTime filter back to the top of the hour without doing some clumsy string manipulation (which lacks any elegance whatsoever). The SQL turned out to be fairly straightforward. I just use the code below to set a variable in any query where I need a “Current Hour” filter. Given there is no string manipulation here, it should translate for use in any locale. This is especially useful in transactional contexts.

Using the DateTime (or SmallDateTime) column on your table, just filter for all rows greater than the result of the filter shown below.  It keeps things quite clean and readable.

This code is specifically for SQL Server, however something similar should work fine in your DB of choice.

Enjoy.

CODE

DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)

Posted in SQL, SQL Server | Leave a comment

Adding an automatic refresh to a SharePoint 2007 List View

Posted on July 17, 2012 by Brian

I got a request this week to make a list view on SharePoint 2007 automatically refresh after a certain period of time had elapsed.  This is great for a list a user might leave open on the desktop all day monitoring for updates.  It turns out this was actually pretty straightforward thanks to the Content Editor Web Part.  I was able to add a CEWP to the page, embed some simple JavaScript and I was off to the races.

I should note that for those of you on SharePoint 2010, you do not need this post at all, any web part can be set with its own independent refresh cycle in the web part’s properties.

The Fix

  1. Navigate to the page you want to add the auto refresh to, enter Edit mode
  2. Add a Content Editor Web Part tot he page (it does not matter where on the page it is added)
  3. Open the Source Editor and paste the code below as-is.
    1. If you want to change the refresh cycle to something shorter or longer alter the last line and change the 300 to something larger or smaller.  You are specifying seconds in that instance.
  4. Set the web part to “Hidden” under the layout properties.
  5. Save it all and exit Edit mode.

That is it.  Leave the page open and it will refresh on whatever interval you defined.

Code

<script type="text/javascript" language="javascript">
    var countdownActive = null;
    var sURL = unescape(window.location.pathname);
    var refreshInterval = 300;

    function setRrefresh(secs) {
        if (arguments.length == 1) {
            if (countdownActive) clearTimeout(countdownActive);
            countdownActive = setTimeout("setRrefresh()",
                    Math.ceil(parseFloat(secs)*1000));
        }
        else {
            countdownActive = null;
            window.location=sURL
        }
    }

    setRrefresh(refreshInterval);
</script>
Posted in SharePoint 2007 | Leave a comment

Making the attachments “clip” clickable in a SharePoint list

Posted on June 29, 2012 by Brian

We had a requirement to make the “clip” icon that indicates that a list item in SharePoint has an attachment clickable, enabling the opening of one of the attachments associated with that list.  This ability seemed easy to me when it was requested, but it turned out to be somewhat harder than planned.

All the code for this was taken from this EXCELLENT post:  Making the paperclip clickable – From SharePoint JavaScripts.  The code provided by this site worked for me as-is.  It is available from the author in versions that support SP2007 & SP2010.  The reason for this post is that being as new to SharePoint as I am, it was not clear to me how to use the code that was provided.  So this post details how I used the code referred to above.  Please visit that site directly to get the latest code directly from the author, it is worth the trip.

The Solution

You will get two components from the source referenced above.  A JS file to be added to your SharePoint server and a small snippet of JavaScript to be added to each SharePoint page you want to enable this functionality on.

JavaScript File

The first step is to deploy the JavaScript file to the server so it can be referenced from your web part(s) that may need it.  There are probably numerous ways to do this.  I chose to create a document library and add the JS file to that library.  I was sure to configure the document library not to be visible from the site lest a user decide to go snooping.  With the file in the library, it is easy to get a direct link to that file that we could then use in the web part to reference the required functions.

The Web Part

After deploying the JavaScript, go to the page you want to add this functionality to and shift it to Edit Mode.  After the last list you want to make clickable, insert a Content Editor Web Part.  This web part accepts raw HTML (including JavaScript).  You can set the source of the web part to the script snippet provided making sure to update the reference  to “ListAttachments.js” to point to your locally-deployed copy.  That should be the only change you need to make.  It is not required to hide the new web part, but there is nothing there to view so I would hide it (in the Layout section of the Web Part editor).

Notes

  1. You need the ID on any view this is to interact with.  Without the ID field, you will see an error reported from the scripting layer.  The tool give you the ability hide the ID, so adding it will not affect the user experience.
  2. Applying the JavaScript-based Web Part will affect all list views above it so even on a page with several list views you only need one Content Editor Web Part to get this to work.
  3. If a list item has more than one attachment, that is handled fine with no extra work on your part (see the screen grab above).

That is it, another thing that probably should just be “there” in SharePoint, but is not.  Luckily as I found out, getting it added is a 15-minute job thanks to this shared code.


Posted in SharePoint 2007 | 1 Comment