you're reading...
Database, IT-Center, SQL Server

Export Data to Text File Using SSIS Ms SqlServer 2005

1. Open Ms Visual Studio 2005, Create new project > Bussines Intelligence Project > Integration Services Project
2. Drag OleDBSource and connecting to Script Component ( as destination )

3. Set Your Connection and SQL query that data want to export

4.Adding script to Write output data and Create Text File.
Open script editor, with double click in script component and find script menu in left. Copy the code below

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Public Class ScriptMain
 Inherits UserComponent
 Private textWriter As StreamWriter
 Dim i As Integer = 0
Private fs As FileStream
 Private strFileName As String

 Public Overrides Sub PreExecute()
 strFileName = Me.Variables.Location & "\ssis_lat.txt"
 fs = New FileStream(strFileName, FileMode.Create) '--> menunjukkan TextFormater ini urutan pertama dipanggil

 textWriter = New StreamWriter(fs)
End Sub

 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
 ' Add your code here
 i += 1
 With textWriter
 .Write(i & ".")
 .Write(Row.FieldName & " ")
 .Write(Row.AppID & " ")
 .Write(Row.BICode & " ")
 End With
 End Sub

 Public Overrides Sub PostExecute()
 End Sub
End Class

5. Variable can be defined using dtsconfig like “Me.Variables.Location”. Define your variable in ScriptComponent Properties > ReadOnlyVariables label

6. Create dtsConfig
In design mode, click menu header SSIS > Package Configuration
7. Create Logging <effective using text log>, in SSIS menu > Logging


About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 10 other followers

December 2011
« Nov   Jan »


Web Statistic

Blog Stats

  • 160,987 hits
%d bloggers like this: