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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy