Step 2: Place OLEDB DataSource component
Step 3: Place a Script component and connect the Input.
Step 4: Right Click the Script Component, go to Connection Managers Tab.
Step 5: Click Add and Select your database connection from the dropdownlist .
Step 6 : Go to Script tab and click the Design Script option. It will open the Visual Studio for Applications.
Imports System
Imports
System.Data
Imports
System.Math
Imports
Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports
Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports
System.Data.SqlClient
Public Class ScriptMain
Inherits
UserComponent
Private
connMgr As IDTSConnectionManager90
Private
Conn As SqlConnection
Private Cmd
As SqlCommand
Private
sqlReader As SqlDataReader
Public Overrides Sub
AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.MyDatabaseConnection 'This is the
connection to your connection manager.
Conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub
PreExecute()
Dim cmd
As New
SqlCommand("select getdate()",
Conn)
sqlReader = cmd.ExecuteReader
End Sub
Public Overrides Sub
PostExecute()
sqlReader.Close()
End Sub
Public Overrides Sub
ReleaseConnections()
connMgr.ReleaseConnection(Conn)
End Sub
Public Overrides Sub
Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If
sqlReader.HasRows Then
System.Windows.Forms.MessageBox.Show(Convert.ToString(sqlReader(0)))
End If
End Sub
End Class
AcquireConnections :
This method is used to connect the data source
PreExecute
Override PreExecute method to execute the database query
ProcessInputRow
In this method, process the output of the SQLCommand. In our case, we need to process the SQLDataReader.
PostExecute
This method is used to do cleanup database connection, if anything.
ReleaseConnection
This method is used to release the database connection.
Mani LPosted Apr 16, 2021, 2:18 PM
IDTSConnectionManager100 connMgr = this.Connections.ADONetAppStaging ; //this we need to give name in connection manager in script component SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection)(connMgr.AcquireConnection(null)); //Read data from table or view to data table string query = "Select top 10 * From ##AP_Stagging_Temp_ExportWODuplicates Order by 1,2,3 asc "; // string query = "Select * From ##AP_Stagging_Temp_For_JLL_ExportWODuplicates order by 1,2,3 asc "; SqlDataAdapter adapter = new SqlDataAdapter(query, myADONETConnection); DataTable dtExcelData = new DataTable(); adapter.Fill(dtExcelData); myADONETConnection.Close();