SSIS imports Excel with some blank columns

I am working with SSIS to import an Excel file into SQL Server. Not being an SSIS expert, I usually use the import wizard in SSMS which lets me create the destination table, load the data, and save the package to the file system. Then I will review the wizard-created table, modify any data types as needed, and then generate a proper CREATE TABLE statement.

In some cases however, the wizard does not assign the correct data types, and the saved package does not work - one or more columns will be blank. This is caused by the baked-in behavior of Excel, where it examines the beginning of the data & guesses the data types. If the first few rows are not fully populated with the appropriate data, that's a problem.

The fix for this is remarkably simple, but took me lots of Googling to find the solution Import Excel in SSIS and get Null values in stead of Numeric values posted by Kasper de Jonge...

When you open the package in SSIS, in the Excel connection manager you simply add "IMEX=1" so it looks this:

Excel 8.0;HDR=YES;IMEX=1

which forces SSIS to read the Excel file using the data types you specify in the package.

SSMS Utility Explorer

One of the features of SSMS 2008R2 is the Utility Explorer window, which "connects to Database Engine instances to provide a tree view of all the objects in the SQL Server Utility." (https://msdn.microsoft.com/en-us/library/ee210556%28v=sql.105%29.aspx)

This feature is available in the Enterprise & Developer Editions.

Out of curiosity, I opened that window to check it out, and then closed the window. Thought that would turn off the feature. However, every time I opened SSMS, the UE window opened too. Easy enough to close it with a single click, but could not turn it off.

I found that there is a config file windows.prf located here:
C:\Users\first_name.last_name\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell

(whew!)

... and luckily there was a backup file in there, so all I had to do is close SSMS, rename the windows.prf to something else, and then rename the backup to windows.prf and Voila! the Utility Explorer window does not open when you launch SSMS.

TortoiseSVN and SSMS icon overlays

Tortoise SVN is one of the popular source-control systems, which we use at our office. If you've used this product, when you commit you'll sometimes see a message that a new version is available; I had 1.8.x installed, so this time I did the update but - got bit by version 1.9.2 ...

This is using a recent Windows 7 x64 machine, by the way.

After the update, the Windows folders all had the familiar icon overlays, but when I launched SQL Server Management Studio & hit the File Open dialog, none of the colored icons were present. Hmmm. Google'd about looking for a solution & found numerous registry hacks involving icon overlay keys, keys that were added by/for Groove and/or Windows 10; finally found the answer: version 1.9.2 seems to have a problem.

So I uninstalled the 1.9.2 client and downloaded the previous 1.9.1 version, and Voila! the icons all came back. Good times.

sp_who2 with sql text

SQL Server has an undocumented stored procedure sp_who2 that provides information about the connections to the database.

Recently I have been using sp_who2 to track down connections that are "suspended" which in my case means that our MS-Access application is not processing all of the requested data in a timely manner. This can interfere with other users, even though there is no actual blocking going on. Another issue that we're seeing is that users will log-in to their terminal server environment, launch the application & then leave it open for days at a time. Even though the connections are sleeping, they still require some resources on the SQL server.

The task at-hand is 1) determine which connections (SPID's) are suspended & what SQL was running, and 2) determine which connections have been sleeping for more than a day. The first item will reveal the SQL so we can find an alternate solution; the second will allow us to identify the sleepers so we can close them out.

I started by creating a temp table & inserting the sp output, and then running some code to look up the SQL text. . .

DECLARE
    -- set this to the name of your database
    @database sysname = 'my_database',
    @not_sleeping int,
    @not_today int,
    @spid int,
    @sqltext varbinary(128),
    @sql_command nvarchar(4000),
    @today nvarchar(50)

-- LastBatch only provides month & day
SELECT @today = LEFT(CONVERT(nvarchar,GETDATE(),101),5)

-- create temp table to receive sp output, and execute
IF OBJECT_ID(N'tempdb..#sp_who2', N'U') IS NOT NULL
DROP TABLE #sp_who2

SET NOCOUNT ON

CREATE TABLE #sp_who2
    (
    SPID int,
    Status nvarchar(1000) NULL,
    Login sysname NULL,
    HostName sysname NULL,
    BlkBy sysname NULL,
    DBName sysname NULL,
    Command nvarchar(1000) NULL,
    CPUTime int NULL,
    DiskIO int NULL,
    LastBatch nvarchar(1000) NULL,
    ProgramName nvarchar(1000) NULL,
    SPID2 int,
    REQUESTID int NULL
    )

INSERT #sp_who2
EXEC sp_who2

-- look up the sql text for each SPID
ALTER TABLE #sp_who2 ADD sql_command nvarchar(4000)
-----------------------------------------------------------
WHILE EXISTS (SELECT 1 FROM #sp_who2 WHERE sql_command IS NULL)
BEGIN
    SELECT @spid = spid FROM #sp_who2 WHERE sql_command IS NULL

    SELECT @sqltext = sql_handle
    FROM sys.sysprocesses
    WHERE spid = @spid

    SELECT @sql_command = TEXT
    FROM sys.dm_exec_sql_text(@sqltext)

    UPDATE #sp_who2
    SET sql_command = @sql_command
    WHERE spid = @spid
END
-----------------------------------------------------------
SELECT @not_sleeping = COUNT(*)
FROM #sp_who2
WHERE DBName = @database
 AND status <> 'sleeping'

SELECT @not_today = COUNT(*)
FROM #sp_who2
WHERE DBName = @database
 AND    LEFT(LastBatch,5) <> @today

-- display a list of all SPID's of interest
IF @not_sleeping <> 0 OR @not_today <> 0
BEGIN
    SELECT
        SPID,
        Status,
        Login,
        HostName,
        BlkBy,
        DBName,
        Command,
        CPUTime,
        DiskIO,
        LastBatch,
        ProgramName,
        SPID2,
        REQUESTID,
        sql_command
    FROM
        #sp_who2
    WHERE
        DBName = @database
     AND    (
        Status <> 'sleeping'
        OR
        LEFT(LastBatch,5) <> @today
        )
END
---------------------------------------

DROP TABLE #sp_who2