February 13, 2012 at 5:18 am
I was researching how I could automate delivery of CSV reports by FTP, rather than automating to a file share within SSRS then having to use external programs and batch files and stuff to FTP the files up.
I came across this: http://www.christiansteven.com/products/sql-rd/ it sounds pretty decent, just wondered if anyone else has used it?
For our purposes it might be a bit expensive though - looks like it's $995, which considering we'd only be using for FTP features might be hard to swing with those that pay the bills.
I'd be interested to hear from anyone else who's used this or who's done FTP deployment via SSRS using something else, it looks like a growing area for our company.
February 13, 2012 at 5:45 am
Rob I'm a developer, so the do-it-yourself options for me are much more palatable.
it's very very easy to do it all yourself you can use CLR.
I've got prototype CLR's which export data to a desired format, and another which does simple FTP upload and download.
SFTP is something i have not got going via CLR, I ended up getting lazy and bought CoreFTP so I could script command line arguments for that instead.
if you are a do-it-yourselfer, here's some links and code;
http://www.codeproject.com/Articles/192558/FTP-directly-in-SQL-Using-SQL-CLR
the trick for me for CLR is that it can read a temp table that is in scope.
so i can make a complex query dump data into a temp table, call CLR_ExportTableToCSV '#temp' , then call the CLR to FTP that file.
<Microsoft.SqlServer.Server.SqlProcedure()> _
Shared Function CLR_ExportTableToCSV(ByVal TableName As SqlString, ByVal FilePath As SqlString, ByVal FileName As SqlString, ByVal IncludeHeaders As SqlInt32) As Integer
Dim str As String = "SELECT * FROM {0}"
Dim _sFilePath As String = String.Empty
Dim _sFileName As String = String.Empty
Dim _sFileNameWithPath As String = String.Empty
Dim locFilePath As String = String.Empty
Dim locFileName As String = String.Empty
Dim MyConnection As New SqlConnection("context connection=true")
str = String.Format(str, TableName)
Try
locFilePath = FilePath.ToString.Trim
locFileName = FileName.ToString.Trim
str = String.Format(str, TableName)
MyConnection.Open()
Dim myCommand As New SqlCommand(str, MyConnection)
Dim myDataReader As SqlDataReader
myDataReader = myCommand.ExecuteReader()
Dim MyDataTable As New DataTable(TableName.ToString)
MyDataTable.Load(myDataReader)
'NOW we can, in theory, do something with the DataTable.
If Right(locFilePath, 1) = "\" Then
_sFilePath = locFilePath.ToString.Trim
Else
_sFilePath = locFilePath.ToString.Trim & "\"
End If
If Left(locFileName, 1) = "\" Then
_sFileName = locFileName.Substring(1, locFileName.Length - 1)
Else
_sFileName = FileName.ToString.Trim
End If
_sFileNameWithPath = _sFilePath & _sFileName
str = FormatDataTable(MyDataTable, ",", 1)
SaveTextToFile(_sFileNameWithPath, str)
FilePath = "Created: " & _sFileNameWithPath
Return 0
Catch sqlex As SqlException
Throw New Exception("FileName: " & _sFileNameWithPath, sqlex)
Catch ex As Exception
Throw New Exception("FileName: " & _sFileNameWithPath, ex)
End Try
Return 1
End Function
Friend Shared Function FormatDataTable(ByVal dt As DataTable, ByVal Delimiter As String, ByVal IncludeHeaders As Integer) As String
Dim results As String = ""
If IncludeHeaders <> 0 Then
'headers:
For Each col As DataColumn In dt.Columns
results = results & col.ColumnName & Delimiter
Next
End If
'trailing delimiter should be removed
Try
results = Microsoft.VisualBasic.Left(results, Len(results) - Len(Delimiter))
Catch ex As Exception
End Try
results = results & vbCrLf
'--now spew the results
For Each dr As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
results = results & GetString(dr(col)) & Delimiter
Next
'trailing delimiter should be removed
Try
results = Microsoft.VisualBasic.Left(results, Len(results) - Len(Delimiter))
Catch ex As Exception
End Try
results = results & vbCrLf
Next
Return results
End Function
Friend Shared Function SaveTextToFile(ByVal FullPath As String, ByVal strData As String, _
Optional ByVal ErrInfo As String = "") As Boolean
Dim bAns As Boolean = False
Dim objReader As StreamWriter
Try
objReader = New StreamWriter(FullPath)
objReader.Write(strData)
objReader.Close()
bAns = True
Catch Ex As Exception
ErrInfo = Ex.Message
End Try
Return bAns
End Function
Friend Shared Function GetString(ByVal objValue As Object) As String
If objValue Is Nothing OrElse Convert.IsDBNull(objValue) Then
Return ""
Else
Return Convert.ToString(objValue)
End If
End Function
Lowell
February 13, 2012 at 5:52 am
Thanks Lowell, I don't write anything except SQL so some of this is beyond my current skill set, however, the article is very interesting, some of our vb.net developers could probably use this as a great starting point and could perhaps lash something up for me to accomplish this.
I'll ping this over to them and see what they think.
Cheers,
Rob
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply