As stated above, our revised example contains three script parameters (FtpConnection, RemotePath, and ResultVariable). Here is the improved script:-
Public Class ScriptMain ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub Main() Dim result As Integer Dim manager As ConnectionManager Dim ftpClient As FtpClientConnection Dim foldersList As String() Dim filesList As String() Dim vars As Variables ' Get FTP connection client. manager = Dts.Connections(Me.FtpConnection) ftpClient = New FtpClientConnection( _ manager.AcquireConnection(Nothing)) Try If ftpClient.Connect() Then ' Set current working directory. Call ftpClient.SetWorkingDirectory(Me.RemotePath) ' Get remote files list. Call ftpClient.GetListing(foldersList, filesList) ' Store files list in package variable. Call Dts.VariableDispenser.LockOneForWrite(Me.ResultVariable, vars) Try vars(Me.ResultVariable).Value = filesList Finally Call vars.Unlock() End Try End If Catch ex As Exception result = Dts.Results.Failure Call Dts.Events.FireError( _ 0, _ String.Empty, _ ex.Message, _ String.Empty, _ 0) Finally Call ftpClient.Close() End Try Dts.TaskResult = result End Sub ' Main#Region "Properties" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ARRAY DECLARATION SCRIPT:à
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Collections.Generic
Public Class ScriptMain
Inherits UserComponent
Dim ArrayOutputIDs As List(Of Integer)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim CalendarID As Int32 = Nothing, EventVisitID As Int32 = Nothing
If Not Row.fCalendarID_IsNull() Then
CalendarID = Row.fCalendarID
End If
If Not Row.fEventVisitID_IsNull Then
EventVisitID = Row.fEventVisitID
End If
Select Case True
Case CalendarID = 1 And EventVisitID <> Row.fEventVisitID
Dim i As Int32 = 1, EORow As Int32 = 0
ArrayOutputIDs(i) = Row.fCalendarID
Case Else
Dim i As Int32 = 1
i = i + 1
ArrayOutputIDs(i) = Row.fCalendarID
End Select
Select Case True
Case CalendarID = Nothing And EventVisitID <> Row.fEventVisitID
Case Else
Dim EORow As Int32 = 0
EORow = EORow + 1
CalendarID = ArrayOutputIDs(EORow)
End Select
End Sub
End Class
DATE CONVERSION SCRIPT:à
' 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
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim month As String
Dim day As String
Dim j As Int32
month = Row.CalendarMonth.ToString
For j = 1 To (2 - Len(month))
month = "0" & month
Next
day = Row.CalendarDayMonth.ToString
For j = 1 To (2 - Len(day))
day = "0" & day
Next
Row.DateId = CInt(Row.CalendarYear.ToString + month + day)
End Sub
End Class
An other option is a script task. Instead of pulling the variables from the child package, you can push them from the parent package:-
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
' Call child package and pass through parameters / variables
' The child package is called with the same connection
' manager as the Execute Package Task would do
Try
' Configure the child package
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
Dim package As Package
package = app.LoadPackage(Dts.Connections("child.dtsx").ConnectionString.ToString(), Nothing)
package.Variables("User::variable1").Value = "some value"
' Execute package and return result
Dim result As DTSExecResult
result = package.Execute()
Dts.TaskResult = result
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class
Script for SurrogateKyey Genarate:-
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim counter As Integer = 0 ' User code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
Row.mycount = counter ' User code
counter = counter + 1 ' User code
End Sub
End Class
ScriptFor Backup:-
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim sSSASServerName As String = CStr(Dts.Variables(”SSASServerName”).Value)
Dim sBackupLocation As String = CStr(Dts.Variables(”BackupLocation”).Value)
If Right(sBackupLocation, 1) <> “\” Then sBackupLocation = sBackupLocation + “\”
Dim Locations() As Microsoft.AnalysisServices.BackupLocation
Dim oServer As New Microsoft.AnalysisServices.Server
oServer.Connect(sSSASServerName) ‘ connect to the server and start scanning down the object hierarchy
Dim oDB As Microsoft.AnalysisServices.Database
For Each oDB In oServer.Databases
‘If oDB.Name = “AdventureWords” Then ‘ here you can include or excluded databases
oDB.Backup(sBackupLocation & oDB.Name & “_” & Now().ToString(”yyyyMMdd_hhmmss”) & “.abf”, True, False, Locations, True)
‘End If
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
**************8
----------ALL-SCRIPT-FROM-IS2005SBS-------------
8************
ERROR-HANDLING SCRIPT:-
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Data.SqlClient
Public Class ScriptMain
Public Sub Main()
Dim fileName As String = "C:\Employees.txt"
Const CONNECTION_STRING As String = _
"Data Source=localhost;" & _
"Initial Catalog=is2005sbsDW;Integrated Security=SSPI;"
Const SQL_SELECT_EMPLOYEE As String = _
"SELECT" & _
" FirstName, LastName, MiddleName, Gender, Title, " & _
" DepartmentName, HireDate, LoginID, EmailAddress, Phone " & _
"FROM DimEmployee " & _
"ORDER BY LastName"
Const RECORD_SEPARATOR As String = _
"----------------------------------------------------------"
Dim writer As StreamWriter
Dim con As SqlConnection = New SqlConnection(CONNECTION_STRING)
Try
Dim adapter As New SqlDataAdapter(SQL_SELECT_EMPLOYEE, con)
Dim ds As New DataSet
adapter.Fill(ds)
writer = New StreamWriter(fileName, False)
writer.AutoFlush = True
With ds.Tables(0)
For row As Integer = 0 To .Rows.Count - 1
For col As Integer = 0 To .Columns.Count - 1
writer.WriteLine("{0}: {1}", _
.Columns(col).ColumnName, .Rows(row)(col))
Next col
writer.WriteLine(RECORD_SEPARATOR)
Next row
End With
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
Finally
If writer IsNot Nothing Then
writer.Close()
End If
End Try
End Sub
End Class
-------------------
EVENT-HANDLER SCRIPT:-
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Data.SqlClient
Public Class ScriptMain
Public Sub Main()
Dim fileName As String = "C:\Employees.txt"
Const COMPONENT_NAME As String = "Export Employees"
Const CONNECTION_STRING As String = _
"Data Source=localhost;" & _
"Initial Catalog=is2005sbsDW;Integrated Security=SSPI;"
Const SQL_SELECT_EMPLOYEE As String = _
"SELECT" & _
" FirstName, LastName, MiddleName, Gender, Title, " & _
" DepartmentName, HireDate, LoginID, EmailAddress, Phone " & _
"FROM DimEmployee " & _
"ORDER BY LastName"
Const RECORD_SEPARATOR As String = _
"----------------------------------------------------------"
Dim writer As StreamWriter
Dim con As SqlConnection = New SqlConnection(CONNECTION_STRING)
Try
Dim adapter As New SqlDataAdapter(SQL_SELECT_EMPLOYEE, con)
Dim ds As New DataSet
adapter.Fill(ds)
Dts.Events.FireInformation(0, COMPONENT_NAME, _
String.Format("Opening output file '{0}'", fileName), _
"", 0, True)
writer = New StreamWriter(fileName, False)
writer.AutoFlush = True
With ds.Tables(0)
For row As Integer = 0 To .Rows.Count - 1
For col As Integer = 0 To .Columns.Count - 1
writer.WriteLine("{0}: {1}", _
.Columns(col).ColumnName, .Rows(row)(col))
Next col
writer.WriteLine(RECORD_SEPARATOR)
Dts.Events.FireProgress("Exporting Employee", _
CInt((row + 1) / .Rows.Count * 100), row + 1, _
.Rows.Count, COMPONENT_NAME, True)
Next row
End With
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(0, COMPONENT_NAME, ex.Message, "", 0)
Dts.TaskResult = Dts.Results.Failure
Finally
If writer IsNot Nothing Then
writer.Close()
End If
End Try
End Sub
End Class
------------------------
LOG-SCRIPT:--
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Data.SqlClient
Public Class ScriptMain
Public Sub Main()
Dim fileName As String = "C:\Employees.txt"
Const COMPONENT_NAME As String = "Export Employees"
Const CONNECTION_STRING As String = _
"Data Source=localhost;" & _
"Initial Catalog=is2005sbsDW;Integrated Security=SSPI;"
Const SQL_SELECT_EMPLOYEE As String = _
"SELECT" & _
" FirstName, LastName, MiddleName, Gender, Title, " & _
" DepartmentName, HireDate, LoginID, EmailAddress, Phone " & _
"FROM DimEmployee " & _
"ORDER BY LastName"
Const RECORD_SEPARATOR As String = _
"----------------------------------------------------------"
Dim writer As StreamWriter
Dim con As SqlConnection = New SqlConnection(CONNECTION_STRING)
Try
Dts.Log("Opening database connection: " & _
con.ConnectionString, 0, Nothing)
Dim adapter As New SqlDataAdapter(SQL_SELECT_EMPLOYEE, con)
Dim ds As New DataSet
adapter.Fill(ds)
Dts.Log(String.Format( _
"Retrieving {0} data, opening output file '{1}'", _
ds.Tables(0).Rows.Count, _
fileName), _
0, Nothing)
Dts.Events.FireInformation(0, COMPONENT_NAME, _
String.Format("Opening output file '{0}'", fileName), _
"", 0, True)
writer = New StreamWriter(fileName, False)
writer.AutoFlush = True
With ds.Tables(0)
For row As Integer = 0 To .Rows.Count - 1
For col As Integer = 0 To .Columns.Count - 1
writer.WriteLine("{0}: {1}", _
.Columns(col).ColumnName, .Rows(row)(col))
Next col
writer.WriteLine(RECORD_SEPARATOR)
Dts.Events.FireProgress("Exporting Employee", _
CInt((row + 1) / .Rows.Count * 100), row + 1, _
.Rows.Count, COMPONENT_NAME, True)
Next row
End With
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(0, COMPONENT_NAME, ex.Message, "", 0)
Dts.Log("Exception detected: " & ex.ToString(), 0, Nothing)
Dts.TaskResult = Dts.Results.Failure
Finally
If writer IsNot Nothing Then
Dts.Log("Closing output file", 0, Nothing)
writer.Close()
End If
End Try
End Sub
End Class
SSIS: Initialise variables the easy way :- Public Sub Main() Dim vars As Variables Dts.VariableDispenser.LockForWrite("User::Variable") Dts.VariableDispenser.GetVariables(vars) vars("User::Variable").Value = "Some silly string" vars.Unlock() Dts.TaskResult = Dts.Results.Success End Sub SSIS: Create Analysis Services partitions from a SSIS package Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports Microsoft.AnalysisServices Public Class ScriptMain Const subComponentName As String = "Partition Creator" Public Sub Main() Dim server As New Server Dim databaseName As String Dim cubeName As String Dim measureGroupID As String Dim partitionID As String Dim mg As MeasureGroup Dim cube As Cube Dim vars As Variables Try 'Lock variables Dts.VariableDispenser.LockForRead("User::OLAPDatabaseName") Dts.VariableDispenser.LockForRead("User::OLAPMeasureGroupID") Dts.VariableDispenser.LockForRead("User::OLAPPartitionID") Dts.VariableDispenser.LockForRead("User::OLAPCubeName") Dts.VariableDispenser.GetVariables(vars) databaseName = vars("User::OLAPDatabaseName").Value.ToString() cubeName = vars("User::OLAPCubeName").Value.ToString() measureGroupID = vars("User::OLAPMeasureGroupID").Value.ToString() partitionID = vars("User::OLAPPartitionID").Value.ToString() server.Connect(Dts.Connections("olap").ConnectionString) cube = server.Databases(databaseName).Cubes(cubeName) mg = cube.MeasureGroups(measureGroupID) Dts.Events.FireInformation(1, subComponentName, "Server=" + server.Name + " Database=" + databaseName + " Cube=" + cubeName + " MeasureGroup=" + measureGroupID, "", 0, True) 'The real work goes on here. It checks to see if the given measure group has a partition ' named for the given week. If it doesn't then it creates it. If (Not PartitionExists(mg, partitionID)) Then If CreatePartition(mg, partitionID) Then Dts.Events.FireInformation(1, subComponentName, "Creating partition '" + partitionID + "' on measure group '" + mg.ID + "'", "", 0, True) 'Commit changes to the cube cube.Update(UpdateOptions.ExpandFull) Else Dts.Events.FireInformation(1, subComponentName, "Creation of partition '" + partitionID + "' on measure group '" + mg.ID + "' failed!", "", 0, True) End If Else Dts.Events.FireInformation(1, subComponentName, "Partition '" + partitionID + "' on measure group '" + mg.ID + "' already exists!", "", 0, True) End If Catch ex As Exception Dts.Events.FireError(-1, subComponentName, ex.Message, "", 0) Finally vars.Unlock() End Try Dts.TaskResult = Dts.Results.Success End Sub Function CreatePartition(ByRef mg As MeasureGroup, ByVal partitionID As String) As Boolean Dim partition As Partition Dim queryString As String Try 'Clone the default partition and then change its name and ID ' Assumption made here that the measure group has a default partition with the same name partition = mg.Partitions(mg.ID).Clone() partition.ID = partitionID partition.Name = partition.ID queryString = "---" 'Set query string here as appropriate CType(partition.Source, QueryBinding).QueryDefinition = queryString Dts.Events.FireInformation(1, "", "Changing query string of Partition '" + partitionID + "' to '" + queryString + "'", "", 0, True) 'Add the new partition to the measure group mg.Partitions.Add(partition) 'Commit the changes mg.Update(UpdateOptions.ExpandFull) Return True Catch ex As Exception Dts.Events.FireError(-1, subComponentName, ex.Message, "", 0) Return False End Try End Function Function PartitionExists(ByVal mg As MeasureGroup, ByVal partitionID As String) As Boolean Dim returnVal As Boolean Dim partition As Partition returnVal = False 'Loop over partitions. If it finds the one its after, flip a boolean flag For Each partition In mg.Partitions If partition.ID = partitionID Then returnVal = True End If Next Return returnVal End FunctionEnd Class BLOGS SCRIPTS:-
Supported date formats:
YYMMDD
YY-MM-DD
YYYYMMDD
YYYY-MM-DD
Of course it's possible to add code for more date formats yourself. If you want to, copy and paste your code in a comment. I will then add the code to this blog.
---------------------------------------------------------------------------------------------------------------------------
Public Shared Function GetDateFromString(ByVal stringDate As String) As DateTime
Dim datetimeResult As DateTime
Try
Dim centuryToAdd As Integer = 1900
If (Convert.ToInt32(stringDate.Substring(0, 2)) < 80) Then
centuryToAdd = 2000
End If
If (stringDate.Length = 6) Then
'Format is: YYMMDD
datetimeResult = New DateTime((centuryToAdd + Convert.ToInt32(stringDate.Substring(0, 2))), Convert.ToInt32(stringDate.Substring(2, 2)), Convert.ToInt32(stringDate.Substring(4, 2)), 0, 0, 0)
Return datetimeResult
End If
If (stringDate.Length = 8) Then
If (stringDate.IndexOf("-") > 0) Then
'Format is: YY-MM-DD
datetimeResult = New DateTime((centuryToAdd + Convert.ToInt32(stringDate.Substring(0, 2))), Convert.ToInt32(stringDate.Substring(3, 2)), Convert.ToInt32(stringDate.Substring(6, 2)), 0, 0, 0)
Return datetimeResult
End If
'Format is: YYYYMMDD
datetimeResult = New DateTime(Convert.ToInt32(stringDate.Substring(0, 4)), Convert.ToInt32(stringDate.Substring(4, 2)), Convert.ToInt32(stringDate.Substring(6, 2)), 0, 0, 0)
Return datetimeResult
End If
If (stringDate.Length = 10) Then
'Format is: YYYY-MM-DD
datetimeResult = New DateTime(Convert.ToInt32(stringDate.Substring(0, 4)), Convert.ToInt32(stringDate.Substring(5, 2)), Convert.ToInt32(stringDate.Substring(8, 2)), 0, 0, 0)
Return datetimeResult
End If
Return Convert.ToDateTime(stringDate)
Catch e As Exception
End Try
'No date format found: Return unknown(1/1/1900)
datetimeResult = New DateTime(1900, 1, 1, 0, 0, 0)
Return datetimeResult
End Function
No comments:
Post a Comment