SSIS Dynamic Flat File Destinations with SQLCMD

Our group uses SSIS exclusively for both ETL and standard tasks. Without getting into all the specifics of why we’ve chosen this technology, at the heart is the ability for any team member to troubleshoot someone else’s code, and if designed right gives the ability to create generic packages with an OOP approach. It’s this last point that has lead to challenge in design.

We wanted to design a package where we could drop any SQL statement into a file, and allow the package to dynamically connect to SQL Server execute the statement, and export it to a flat file. As we found the only way to accomplish this through the native SSIS components is tweak the metadata of the package (XML Description) prior to the execution. The reason for this is the metadata of the connection (Source/Destination) are baked into the package itself. The workaround we found was the sqlcmd utility. Before getting into the details let me state I understand that there are other utilities such as BCP and that using SSIS in this way may not be the best way to go. My answer is in our case we need to maintain SSIS for conformity with our other processes and the ability to leverage other tools within the SSIS framework. This blog doesn’t go into the extra tools required, but simply focus’s on the nuts and bolts of the package.

Using SQLCMD
Below is the following core statement that will be used. Many of these parameters will equate to variables within your package. By keeping these as variables it allows you to make your package generic.

sqlcmd -Slocalhost -dAdventureWorks -i"C:\testing\inputquery.sql" -o"c:\testing\outfile.txt" -h-1 -s"," -W


-S[Server] – Is the server your running against and maps to package variable ServerName
-d[Database] – Is the database your statement will execute from. This is mapped to the package variable DBName.
-i[Input File] - This the location you drop the sql file. This is mapped to the package variable QueryFile
-o [OutputFile] – Location of the flat file your exporting to. This is mapped to the package variable QueryOutPutFile
-h – Is the header parameter with a value of “-1” that translates to no header.
-s – Is the column separator parameter with a value of “,” which means use a column separator of a comma
-W – Trims any trailing spaces.

Since sqlcmd is an executable you’ll need to use a process task component to execute it. The process task is separated into two main properties. The executable name, and the arguments it accepts. The arguments are represented above, but since most are input variables they need to be dynamic at runtime. For cleanliness I create the arguments inside a package variable in this case called Args. The expression looks like so.

"-S" + @[User::ServerName] + " -d" + @[User::DBName] + " -i \"" + @[User::QueryFile] + "\" -o\"" + @[User::QueryOutPutFile] + "\" -h-1 -s\",\" -W"

Which evaluates to:
-Slocalhost -dAdventureWorks -i "c:\testing\inputquery.sql" -o"c:\testing\outfile.txt" -h-1 -s"," –W




Once this argument is in place assign the variable to the process task’s argument property, and the sqlcmd to the executable property.

For testing attach to your local Adventureworks database, insert a query into the inputfile specified in the QueryFile Variable (Select * from Sales.SalesPerson) and select a proper output file. If all went well you’ll notice a nice comma delimited resultset, except at the bottom where you’ll see the amount of rows spit out, by the query.

“(17 rows affected)”

This will obviously blowup your format. In order to work around this you need to make sure your query has the command of “set nocount on” as the first command in the statement. So you’ll need to make sure your developers write their query in this fashion. Yes that is a joke, for developers to code against the DBA’s standards, we know they’ll forget at least half the time. So in order for this to happen you need to build a component that will automatically insert this as the first statement. Prior to running the process task, create a script task, that does just this. The following code writes this statement out to the beginning of the file prior to execution.


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text

Public Class ScriptMain

Public Sub Main()
Dim bt As Byte()
Dim fs As FileStream
Dim fPath As String
Dim b(1024) As Byte
Dim temp As UTF8Encoding
Dim queryHold As String
fPath = Dts.Variables("QueryFile").Value.ToString

queryHold = "Set Nocount on " & vbCrLf & File.ReadAllText(fPath)
fs = File.OpenWrite(fPath)
bt = New UTF8Encoding(True).GetBytes(queryHold)
fs.Write(bt, 0, bt.Length)
fs.Close()

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

With this place your all set.