Feeds:
Posts
Comments

Posts Tagged ‘Create a text file using script task is SSIS’

 

Create
a dummy or EOF (End of File) as flag to show completion of certain process,
this is very important when you are sharing same FTP location across many Users/Clients

 

One
way is to copy from a source location and rename that file

 

But
same can achieved using below

 

1.
Drag a Execute SQL Task

 

2.
Create a variable BatchID having data type as string

 

3.
Use below SQLin Execute SQL Task

 

DECLARE @DATE DATETIME

 

SET   @DATE =  GETDATE()

 

SELECT      ‘OUTPUTFILENAME’

           
+ CONVERT(VARCHAR(4),DATEPART(YY,@DATE))

           
+ CASE

           
WHEN LEN(CONVERT(VARCHAR(2),DATEPART           
(MM,@DATE)))>1

           
THEN CONVERT(VARCHAR(2),DATEPART         
(MM,@DATE))

           
ELSE

           
‘0’+CONVERT(VARCHAR(2),DATEPART          
(MM,@DATE))

           
END

           
+ CONVERT(VARCHAR(2),DATEPART(DD,@DATE))

           
+‘_’+ CONVERT(VARCHAR(2),DATEPART        
(HH,@DATE))

           
+ CONVERT(VARCHAR(2),DATEPART       (MINUTE,@DATE))

 

 

4.
Use BatchID in Result Set tab of Execute SQL Task to hold value retuned by
above

 

 

5.Drag
a script component and choose BatchID as readonlyvaraibles in script task

 

Then
use below code inside script task

 

 

Imports System

 

Imports System.Data

 

Imports System.Math

 

Imports
Microsoft.SqlServer.Dts.Runtime

 

Imports System.Data.SqlClient

 

Imports System.Text

 

Imports System.IO

 

 

Public Class
ScriptMain

 

   
Public Sub
Main()

 

 

       
Dim BatchID As String

 

       
BatchID = CStr(Dts.Variables("User::BatchID").Value)

       
‘BatchID is ReadOnlyVariable

       
‘Assuming BatchID having value like ‘OUTPUTFILENAME20110317_1752’

 

 

       
Using Out As
StreamWriter = New StreamWriter("C:\" & BatchID & ".eof")

 

           
Out.WriteLine("End of File")

 

           
Out.Close()

 

       
End Using

 

 

       
Dts.TaskResult = Dts.Results.Success

   
End Sub

 

End Class

 

Note: On can rename โ€œeofโ€ in above code with any other type of file depending
upon your requirements

 

 

Read Full Post »

%d bloggers like this: