October 8, 2007 at 7:41 pm
Hi,
I am writing a package that deals with file handling. After processing a file, I am to write the name of the file and the datestamp as the first line into it and the total number of rows as the last line. Do u recommend a script task or is there an easier way to get this done? If it has to be a script can someone please give me the code?
Regards,
Abhi
October 9, 2007 at 8:38 am
Use a script task i.e.
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
'Author: Tommy Bollhofer (tbollhofer2@gmail.com)
'Assumes the following variables are available:
'varFileName string
'varRowCount int32 * Use a RowCount transformation in DataFlow
Public Sub Main()
'Declaration of Variables
Dim strFileName As String, strRowCount As String
'Get Row Count as String
strRowCount = Dts.Variables("varRowCount").Value.ToString
strFileName = Dts.Variables("varFileName").Value.ToString
'Write the Variable to SSIS
Dts.Variables("varFileName").Value = strFileName & "_" & strRowCount & ".CSV"
'For Debugging Purposes
'System.Windows.Forms.MessageBox.Show(strFileName & "_" & strRowCount & ".CSV")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Tommy
Follow @sqlscribeOctober 9, 2007 at 5:56 pm
Thanks tommy! I figured i would have to use the row count transformation, but what is the syntax to write the timestamp of the process into the file?
Regards,
Abhi
October 10, 2007 at 9:56 am
NP - i.e.
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
'Author: Tommy Bollhofer (tbollhofer2@gmail.com)
Public Sub Main()
'
Dim strDate As DateTime = DateTime.Now
Dim strFileName As String
strFileName = Dts.Variables("varFileName").Value.ToString & "_" & + _
CDbl(Dts.Variables("varRowCount").Value.ToString) & "_" & + _
DatePart(DateInterval.Year, strDate).ToString & + _
DatePart(DateInterval.Month, strDate).ToString & + _
DatePart(DateInterval.Day, strDate).ToString & + _
DatePart(DateInterval.Hour, strDate).ToString & + _
DatePart(DateInterval.Minute, strDate).ToString & + _
DatePart(DateInterval.Second, strDate).ToString & ".CSV"
'Write strFileName to the varFileName variable
'Dts.Variables("varFileName").Value = strFileName
'For Debugging Purposes
System.Windows.Forms.MessageBox.Show(strFileName)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Tommy
Follow @sqlscribeOctober 11, 2007 at 12:29 am
Thank You Kindly!
Abhi
October 11, 2007 at 1:14 am
Check out the custom DateTime format strings if you want to shorten the code e.g. DateTime.Now.ToString("yyyyMMddhhmmss")
October 11, 2007 at 5:30 pm
Better Still! thanks.
Abhi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply