SSIS Export to Excel Numbers Stored as Text

Like many DBA before me, I have battled SSIS Exports to Excel with valor and tenaciousness (word?). Sometimes I win, sometimes I lose (I think the score is SSIS 2 – Joe 5). I seem to remember having trouble exporting numbers to Excel in the past but it took me a while to figure it out again. So this time I am documenting it.

Assumes you have an existing package you are modifying. Works however with a new package destination as well.

1. Right Click on your Excel Destination and click Edit
2. Hit the New Button next to the Name of the Excel Sheet
3. You should get a Create Table Window. Copy this CREATE statement to your favorite Text Editor
4. Cancel out of the Excel Destination edit
5. Find your actual Excel Destination file on the OS and delete it
6. Now if you need to make changes to the worksheet you can using your text editor. Get the CREATE table setup like you want it. Make sure you define your number columns as Type Long and your Text Columns as LongText
7. Right Click on your Excel Destination and click Edit again.
8. You can now either use the CREATE TABLE that is generated by SSIS or you can use your CREATE TABLE Script you modified in Step 6
9. If you have added new columns manually to your CREATE Table statement you will need to go to your source and fix that. You may get some validation errors that you will need to resolve.
10. Click OK on the Create and then you should see your Sheet name in the drop down box.

This method will also remove any extra sheets in your Worksheet (Sheet1, 2, 3) and will create a new sheet based on the name in the CREATE TABLE Script.

SQL Server Job History

The following query will give you the job history for a specific job. You will need to find the job_id of the target job before executing and plug it in to the WHERE clause.

--FIND YOUR JOB ID FIRST, plug it in at the bottom
SELECT [job].[job_id] AS [JobID]
 , [job].[name] AS [JobName]
 , CASE
 WHEN [jh].[run_date] IS NULL OR [jh].[run_time] IS NULL THEN NULL
 ELSE CAST(CAST([jh].[run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([jh].[run_time] AS VARCHAR(6)), 6), 3, 0, ':') , 6, 0, ':') AS DATETIME)
 END AS [LastRunDateTime]
 , CASE [jh].[run_status]
 WHEN 0 THEN 'Failed'
 WHEN 1 THEN 'Succeeded'
 WHEN 2 THEN 'Retry'
 WHEN 3 THEN 'Canceled'
 WHEN 4 THEN 'Running' -- In Progress
 END AS [LastRunStatus]
 , STUFF(STUFF(RIGHT('000000' + CAST([jh].[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)]
 , [jh].[message] AS [LastRunStatusMessage]
FROM [msdb].[dbo].[sysjobs] AS [job]
 LEFT JOIN (SELECT
 [job_id]
 , [run_date]
 , [run_time]
 , [run_status]
 , [run_duration]
 , [message]
 , ROW_NUMBER() OVER (PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC) AS RowNumber
 FROM [msdb].[dbo].[sysjobhistory]
 WHERE [step_id] = 0
 ) AS [jh]
 ON [job].[job_id] = [jh].[job_id]
WHERE job.job_id = '30B151E5-A422-461C-B118-E856D4BED635'
ORDER BY LastRunDateTime desc

From there you can make charts and stuff. Man I like charts and stuff.

SET NOCOUNT ON Performance

I was a little skeptical of the NOCOUNT ON boosting query performance so I went to test it. My new job requires me to wear both DBA and DB Developer hats so I am trying to get up to speed on the basics.

I created a customer table with 3,000 rows. I used http://www.generatedata.com/#generator to generate some data. Love that site. You can quickly generate data free (I know there are probably better pay-to-play tools out there, but this is free).

I then created two very simple Stored Procedures.

SelectCustomer

create procedure SelectCustomer as
set nocount on
 select * from customer

SelectCustomer2

create procedure SelectCustomer2 as
select * from customer

I turn on query statistics using set statistics time on, and called the procs. Execution times vary, but one constant was the fact that SelectCustomer performed a lot better than SelectCustomer2. In 3 samples SelectCustomer performed 24%, 34%, and 35% better. These numbers seem skewed, and I would not go by the drastic performance gains of this little test case. But one thing for sure the SET NO COUNT ON does provide a performance boost. I imagine the performance boost on proc that returns several million rows could be drastic.

SQL Server Load testing

WARNING: I am not responsible for an out of control pack of wild honey badgers destroying your database. Harness the power of the Honey Badger at your own risk.

I wanted a quick and easy way to do some SQL Server load testing. So I came up with the Honey Badger Load Tester . It is a set of powershell scripts that will start a user-determined amount of sessions to connect and execute custom query payloads against your SQL Server database.

releaseHoneyBadgers.ps1
This is the main caller script. Here you specify a badger count…as in how many sessions would you like to generate.

$badgerCount = 15
$scriptFile = "c:\scripts\query.ps1"

$i = 1
while ($i -le $badgerCount) {
start-job $scriptFile
$i++
}

query.ps1
The query script will execute the query. The following variables can be set to meet your needs:

• loopCount – how many times you want each honey badger to loop through the payload
• Query1,2,3,x – Query you want each honey badger to execute. You can add as many as you want here.
• ServerInstance – set this to your SQL Server name
• Database – name of the database you want to connect to

/*
LOOP COUNT
Indicates how many times you want to execute the query loop. Default
is 5000 meaning it will run your query payload 5000 times
*/
$loopCount = 5000
/*
DATABASE CONNECTION INFO
Set your Database Properies here
*/
$ServerInstance = "SERVER1 "
$Database = "Db1 "
$ConnectionTimeout = 30
$QueryTimeout = 120

/*
QUERY LIST
Use this to direct your payload. You can of course add more queries here and then
call them in the while loop below.
*/
$Query1 = "SELECT * FROM Db1.[dbo].[table1]"
$Query2 = "SELECT * FROM Db1.[dbo].[table2]"
$Query3 = "SELECT * FROM Db1.[dbo].[table3]"
$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()

$i = 1
while ($i -le $loopCount) {
# QUERY 1
$cmd=new-object system.Data.SqlClient.SqlCommand($Query1,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

# QUERY 2
$cmd=new-object system.Data.SqlClient.SqlCommand($Query2,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

# QUERY 3
$cmd=new-object system.Data.SqlClient.SqlCommand($Query3,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

$i++

}

Here are some pics of the HoneyBadger in Action:

Executed the releaseHoneyBadgers.ps1 script with Badger count of 15.

Then I fired up 15 more badgers:

Laptop holding its own against the badgers: