SSIS output parameters

Work network filter is preventing me from connecting to an image
hosting service so I'm letting blogger do the dirty work for me.
Evaluation operation: Expression and Constraint
Value: Success
Expression: @ado_val == 5
* Logical AND


Datetime precision lost with default conversion to varchar

Or as I like to call it, where'd my seconds go?

I wish I wasn't learning this nugget: the default conversion from datetime to varchar results in a precision to the minute. Seconds, milliseconds? Sure hope you didn't need those, because they're gone.

A less than talented person designed managed to bang on enough keys to write a proc that takes as a parameter this datetime value. However, they declared it as a varchar(50). No problem, I think, it's just because he was too lazy to figure out how to cast it properly when he uses it with all this dynamic sql he's building in this proc. While I'm sure the latter portion is true, a subtle error was introduced by the implicit conversion, we lost the seconds and milliseconds for this timestamp. Which might have not been so bad, were that period not used in 8 other tables to group transactions together.

SELECT 
    cast(current_timestamp AS varchar(50)) AS default_cast
,   convert(varchar(50), current_timestamp, 100) AS explicit_convert
,   current_timestamp AS default_select_display

default_cast	explicit_convert	default_select_display
Mar 15 2011 10:41PM	Mar 15 2011 10:41PM	2011-03-15 22:41:21.960

The format for default_select_display is the native display for a datetime value. See those last five numbers? That's the time between 00.000 seconds and 59.997 seconds which is now lost to the ether when you cast or convert to character data type without specifying that you want it in a sane format, like 121 (which is the lossless format used for default_select_display).

The really sad this is that rather than fix this the right way, which would involve rewriting the "proc with cursor which calls second proc which calls the same non-inline table valued function twice with identical parameters to build a dynamic SQL statement just to do a simple insert" with a nice set-based method. Instead, this works "good enough" until I get the time to clean it up proper.

SET @bloodyStupidHack = convert(varchar(50), @rptg_prd_dt, 121)

</rant>

BOL Reference

CAST and CONVERT

Powershell export query to csv

This morning I saw a tweet from Matt Velic (blog|twitter) and he was trying to run a query, dump the results to a csv and then ftp the file.

sqlcmd csv export

Assuming no commas in the source data, one can do this without using powershell with our old friend SQLCMD.

sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd.csv"

powershell export query results to csv

I'm still a neophyte at this powershell stuff, but here's my 30 second take at it. I like this solution better than the sqlcmd simply because the export library looks cleaner.

# 2011-03-01
# Bill Fellows
#
# This PowerShell script is designed to demonstrate how to run a query
# against a database and dump to a csv
#
# Usage:  Save this file as C:\sandbox\powershell\databaseQuery.ps1
# Start Powershell (Win-R, powershell)
# Execute the script (C:\sandbox\powershell\databaseQuery.ps1)

#
# If the above fails due to
# "cannot be loaded because the execution of scripts is disabled on this system"
# run this command within Powershell
# Set-ExecutionPolicy RemoteSigned
# See also http://technet.microsoft.com/en-us/library/ee176949.aspx


# http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
$server = "localhost"
$database = "master"
$query = "SELECT D.* FROM sys.databases D"

# powershell raw/verbatim strings are ugly
# Update this with the actual path where you want data dumped
$extractFile = @"
C:\test.csv
"@

# If you have to use users and passwords, my condolences
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# dump the data to a csv
# http://technet.microsoft.com/en-us/library/ee176825.aspx
$DataSet.Tables[0] | Export-Csv $extractFile

Update: Export to CSV from SSMS via PowerShell. This modification to the script allows you to select a query within SSMS and export it to a CSV