MSBI (SSIS/SSRS/SSAS) Online Training

Friday, January 30, 2009

My Favorite XMLA-Script2 Of This Month:-

b).Measure Process with Partition- Measure Process Script:-


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain
Public Sub Main()

Dim temp1 As String
Dim temp2 As String

Dts.Variables.Item("strProcessScript1").Value = _
""
temp1 = " "
temp2 = " " + _
"-1" + _
"" + Dts.Variables.Item("strErrorLogLocation").Value.ToString + "Log.Log" + "" + _
"ReportAndContinue" + _
"ReportAndContinue" + _
"
"

Dts.Variables.Item("strProcessScript2").Value = _
"
"

Dts.Variables.Item("strMeasureProcess").Value = _
Dts.Variables.Item("strProcessScript1").Value.ToString + temp2 + temp1 + _
Dts.Variables("strProcessScript").Value.ToString + _
Dts.Variables("strProcessScript_Add").Value.ToString + _
" " + _
" " + _
Dts.Variables("strProcessScript_Bindings").Value.ToString + _
"
" + _
Dts.Variables.Item("strProcessScript2").Value.ToString

'MsgBox(Dts.Variables.Item("strMeasureProcess").Value)

Dim temp3 As String
temp3 = Dts.Variables("strProcessScript_Add").Value.ToString + _
Dts.Variables("strProcessScript_Bindings").Value.ToString
'MsgBox(temp3)

Dim f As File
f.WriteAllText("C:\test.txt", Dts.Variables("strMeasureProcess").Value.ToString())

Dts.TaskResult = Dts.Results.Success
End Sub
'Dts.Variables("strProcessScript").Value.ToString + _

End Class


*************************

Here
'ProcessScript =='Add' Type
Dts.Variables.Item("strProcessScript_Add").Value
'ProcessScript ==Bindings Type Dts.Variables.Item("strProcessScript_Bindings").Value
'ProcessScript ==Index Type Dts.Variables.Item("strProcessScript_Index").Value
Else Part

Dts.Variables.Item("strProcessScript").Value

Values are coming from for Eachloop container like the given below script:
This is for all the Measures along with Partition and Index Script:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain


Public Sub Main()

'ProcessScript =='Add' Type

If ((Dts.Variables.Item("ProcessType").Value.ToString) = "ProcessAdd") Then


Dts.Variables.Item("strProcessScript_Add").Value = Dts.Variables.Item("strProcessScript_Add").Value.ToString() + _
" " + _
" " + _
" " + Dts.Variables.Item("strDatabasename").Value.ToString + "" + _
" " + Dts.Variables.Item("strCubename").Value.ToString + "" + _
" " + Dts.Variables.Item("strMeasurename").Value.ToString + "" + _
" " + Dts.Variables.Item("strPartitionname").Value.ToString + "" + _
"
" + _
" " + Dts.Variables.Item("ProcessType").Value.ToString + "" + _
"UseExisting" + _
"
"

'ProcessScript ==Bindings Type

Dts.Variables.Item("strProcessScript_Bindings").Value = Dts.Variables.Item("strProcessScript_Bindings").Value.ToString() + _
" " + _
" " + Dts.Variables.Item("strDatabasename").Value.ToString + "" + _
" " + Dts.Variables.Item("strCubename").Value.ToString + " " + _
" " + Dts.Variables.Item("strMeasurename").Value.ToString + " " + _
" " + Dts.Variables.Item("strPartitionname").Value.ToString + " " + _
" " xmlns:xsi=" + """" + "http://www.w3.org/2001/XMLSchema-instance" + """" + _
" xsi:type=" + """" + "QueryBinding" + """" + ">" + _
" " + Dts.Variables.Item("strDataSourcename").Value.ToString + "" + _
" " + Dts.Variables.Item("strQueryDefinition").Value.ToString + "" + _
" " + _
"
"


'ProcessScript ==Index Type
Dts.Variables.Item("strProcessScript_Index").Value = Dts.Variables.Item("strProcessScript_Index").Value.ToString() + _
" " + _
" " + _
" " + Dts.Variables.Item("strDatabasename").Value.ToString + "" + _
" " + Dts.Variables.Item("strCubename").Value.ToString + "" + _
" " + Dts.Variables.Item("strMeasurename").Value.ToString + "" + _
" " + Dts.Variables.Item("strPartitionname").Value.ToString + "" + _
"
" + _
" ProcessIndexes" + _
"UseExisting" + _
"
"

Else

Dts.Variables.Item("strProcessScript").Value = Dts.Variables.Item("strProcessScript").Value.ToString() + _
" " + _
" " + _
" " + Dts.Variables.Item("strDatabasename").Value.ToString + "" + _
" " + Dts.Variables.Item("strCubename").Value.ToString + "" + _
" " + Dts.Variables.Item("strMeasurename").Value.ToString + "" + _
"
" + _
" " + Dts.Variables.Item("ProcessType").Value.ToString + "" + _
"UseExisting" + _
"
"

Dts.TaskResult = Dts.Results.Success
End If

End Sub

End Class



d).Partition –Measure Process –Index Script:-

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Public Sub Main()

Dim temp1 As String
Dim temp2 As String

Dts.Variables.Item("strProcessScript1").Value = _
""
temp1 = " "
temp2 = " " + _
"-1" + _
"" + Dts.Variables.Item("strErrorLogLocation").Value.ToString + "Log.Log" + "" + _
"ReportAndContinue" + _
"ReportAndContinue" + _
"
"

Dts.Variables.Item("strProcessScript2").Value = _
"
" + _
"
"

Dts.Variables.Item("strMeasureProcess_Add").Value = _
Dts.Variables.Item("strProcessScript1").Value.ToString + temp2 + temp1 + _
Dts.Variables("strProcessScript_Index").Value.ToString + _
Dts.Variables.Item("strProcessScript2").Value.ToString

'MsgBox(Dts.Variables.Item("strMeasureProcess_Add").Value)

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

My Favorite XMLA-Script1 Of This Month:-

XMLA

1. OLAP-Backup Script(DB backup)-VB?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dts.Variables.Item("strBackupScript").Value = _
"" + _
" " + _
" " + Dts.Variables.Item("strDatabasename").Value.ToString + "" + _
"
" + _
" " + Dts.Variables.Item("strBackupFolderFile").Value.ToString + Dts.Variables.Item("strDatabasename").Value.ToString + "_" + Format(Date.Now(), "yyyyMMddhhmmss") + ".abf" + _
" true" + _
"
"

'MsgBox(Dts.Variables.Item("strBackupScript").Value.ToString())
Dts.TaskResult = Dts.Results.Success

End Sub

End Class


2. OLAP-Rollback Script (DB Rollback)-VB?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain


Public Sub Main()
Dim dInfo As DirectoryInfo = New DirectoryInfo(Dts.Variables("strBackupFolderFile").Value.ToString())
Dim temp As String
Dim strlatestfile As String
Dim tmpLatestCreationTime As DateTime

temp = Dts.Variables("strDatabasename").Value.ToString()

tmpLatestCreationTime = Now.AddYears(-10)
For Each f As FileInfo In dInfo.GetFiles
If InStr(f.Name, temp) > 0 Then
If f.CreationTime > tmpLatestCreationTime Then
strlatestfile = f.Name
tmpLatestCreationTime = f.CreationTime
End If
End If
Next
'MsgBox(strlatestfile)

Dts.Variables.Item("strRestoreScript").Value = _
"" + _
" " + Dts.Variables("strBackupFolderFile").Value.ToString() + strlatestfile + "" + _
" " + Dts.Variables("strDatabasename").Value.ToString() + "" + _
" " + "true" + "" + _
"
"

'MsgBox(Dts.Variables.Item("strRestoreScript").Value.ToString())

Dts.TaskResult = Dts.Results.Success
End Sub


End Class

8. How to Process a cube?

a).Dimension Process

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain


Public Sub Main()
Dim temp1 As String
Dim temp2 As String

Dts.Variables.Item("strProcessScript1").Value = _
""
temp1 = " "
temp2 = " " + _
"-1" + _
"" + Dts.Variables.Item("strErrorLogLocation").Value.ToString + "Log.Log" + "" + _
"ReportAndContinue" + _
"
"

Dts.Variables.Item("strProcessScript2").Value = _
"
" + _
"
"
Dts.Variables.Item("strDimensionProcess").Value = _
Dts.Variables.Item("strProcessScript1").Value.ToString + temp2 + temp1 + _
Dts.Variables.Item("strDimProcessScript").Value.ToString + _
Dts.Variables.Item("strProcessScript2").Value.ToString

'MsgBox(Dts.Variables.Item("strDimensionProcess").Value)

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

*************************

Here
Dts.Variables.Item("strDimProcessScript").Value.ToString

Value is coming from for eachloop container like the given below script:
This is for all the dimensions

Dts.Variables.Item("strDimProcessScript").Value = Dts.Variables.Item("strDimProcessScript").Value.ToString() + _
" " + _
" " + _
" " + Dts.Variables.Item("strDatabasename").Value.ToString + "" + _
" " + Dts.Variables.Item("strDimensionname").Value.ToString + "" + _
"
" + _
" " + Dts.Variables.Item("ProcessType").Value.ToString + "" + _
"UseExisting" + _
"
"

My Favorite Script1 Of This Month:-

VB Script


Difference between expression and vb.script –to get a portion from a string

SUBSTRING( @[User::strPackageName] , FINDSTRING( @[User::strPackageName] , "ETL_Extract",1) + 12 , LEN( @[User::strPackageName] ) )


Dts.Variables("strPkgName").Value = Mid(Dts.Variables("strPackageName").Value.ToString, InStrRev(Dts.Variables("strPackageName").Value.ToString, "\", -1) + 1, Len(Dts.Variables("strPackageName").Value.ToString))


1. How to write a variable value into the file + VB?

Dim f As File
f.WriteAllText("C:\test.txt", Dts.Variables("strMeasureProcess").Value.ToString())

2. How to delete files from the folder,if the files are older than 7 days + VB?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim dInfo As DirectoryInfo = New DirectoryInfo(Dts.Variables("strBackupFolderFile").Value.ToString())
Dim NumOfDays As Integer = 7
For Each f As FileInfo In dInfo.GetFiles

f.Refresh()

If DateDiff(DateInterval.Day, f.CreationTime, Date.Now) > NumOfDays Then
f.Delete()
End If
Next

Dts.TaskResult = Dts.Results.Success
End Sub

End Class




3. How to find out latest files from the folder, based on the timestamp and name + VB?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim dInfo As DirectoryInfo = New DirectoryInfo(Dts.Variables("strBackupFolderFile").Value.ToString())
Dim temp As String
Dim strlatestfile As String
Dim tmpLatestCreationTime As DateTime

temp = Dts.Variables("strDatabasename").Value.ToString()

tmpLatestCreationTime = Now.AddYears(-10)
For Each f As FileInfo In dInfo.GetFiles
If InStr(f.Name, temp) > 0 Then
If f.CreationTime > tmpLatestCreationTime Then
strlatestfile = f.Name
tmpLatestCreationTime = f.CreationTime
End If
End If
Next
'MsgBox(strlatestfile)


Dts.Variables("strRestoreFile").Value = "USE [MASTER] RESTORE DATABASE " & Dts.Variables("strDatabasename").Value.ToString() & " FROM DISK = N" & "'" & Dts.Variables("strBackupFolderFile").Value.ToString() & strlatestfile & "' WITH FILE = 1, NOUNLOAD, STATS = 10"

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

4. Maintenance-Backup Script (DB backup)-VB?
Dts.Variables("strBackupFile").Value = " BACKUP DATABASE " & Dts.Variables("strDatabasename").Value.ToString() & " TO DISK = N" & "'" & Dts.Variables("strBackupFolderFile").Value.ToString() & Dts.Variables("strDatabasename").Value.ToString() & "_" & Format(Date.Now(), "yyyyMMddhhmmss") & ".BAK' WITH NOFORMAT, NOINIT, NAME = N" & "'" & Dts.Variables("strDatabasename").Value.ToString() & "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
5. Maintenance-Rollback Script (DB backup)-VB?
Dts.Variables("strRestoreFile").Value = "USE [MASTER] RESTORE DATABASE " & Dts.Variables("strDatabasename").Value.ToString() & " FROM DISK = N" & "'" & Dts.Variables("strBackupFolderFile").Value.ToString() & strlatestfile & "' WITH FILE = 1, NOUNLOAD, STATS = 10"

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

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

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

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

Creating an FTP connection manager thru the Script Task:-

Public Sub Main() Dim result As Integer Dim manager As ConnectionManager Dim ftpClient As FtpClientConnection Dim foldersList As String() Dim filesList As String() manager = Dts.Connections("FTP") ftpClient = New FtpClientConnection( _ manager.AcquireConnection(Nothing)) Try If ftpClient.Connect() Then Call ftpClient.SetWorkingDirectory("/") Call ftpClient.GetListing(foldersList, filesList) ' Store files list in package variable. Call Dts.VariableDispenser.LockOneForWrite( _ "ResultVar", _ vars) Try vars("ResultVar").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 = resultEnd Sub ' Main

Sample VB.Scripts

Creating an FTP connection manager thru the Script Task:-Public Sub Main() Dim result As Integer Dim manager As ConnectionManager Dim ftpClient As FtpClientConnection Dim foldersList As String() Dim filesList As String() manager = Dts.Connections("FTP") ftpClient = New FtpClientConnection( _ manager.AcquireConnection(Nothing)) Try If ftpClient.Connect() Then Call ftpClient.SetWorkingDirectory("/") Call ftpClient.GetListing(foldersList, filesList) ' Store files list in package variable. Call Dts.VariableDispenser.LockOneForWrite( _ "ResultVar", _ vars) Try vars("ResultVar").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 = resultEnd Sub ' Main


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" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property FtpConnection() As String Get FtpConnection = m_ftpConnection End Get Set(ByVal value As String) m_ftpConnection = value End Set End Property ' FtpConnection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ Public Property RemotePath() As String Get RemotePath = m_remotePath End Get Set(ByVal value As String) m_remotePath = value End Set End Property ' RemotePath ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property ResultVariable() As String Get ResultVariable = m_resultVariable End Get Set(ByVal value As String) m_resultVariable = value End Set End Property ' ResultVariable#End Region ' Properties#Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property FtpConnectionType() As String() Get FtpConnectionType = New String() {"FTP"} End Get End Property ' FtpConnectionType#End Region ' Internals#Region "Attributes" Private m_ftpConnection As String Private m_remotePath As String Private m_resultVariable As String#End Region ' AttributesEnd Class ' ScriptMain


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


My favorite blogs - Of This Month

1.To get the script to findout latest file from the folder(based on the time stamp)
http://www.vbforums.com/showthread.php?t=552984
How To Process a Cube through SSIS Package
http://www.ssas-info.com/VidasMatelisBlog/11_ssis-package-that-process-all-partitionsmeasure-groupscubes-in-one-database

SSIS-sQL SERVER 2008 Data Profiling Task:

http://www.simple-talk.com/content/article.aspx?article=560http://www.rhsheldon.comhttp://blogs.conchango.com/jamiethomson/archive/2008/03/02/ssis-data-profiling-task-part-1-introduction.aspx

FOR ALL DOWNLOADS-SAMPLE DATABASES AND SAMPLE BI - PROJECTS

http://www.codeplex.com/SqlServerSamples

MSBI -SSIS,SSAS,SSRS VIDEOS SITES

MSBI -SSIS,SSAS,SSRS VIDEOS SITES
http://dougbert.com/blogs/dougbert/archive/2008/11/18/see-all-the-ssis-new-articles-videos-amp-samples-from-microsoft.aspx
http://www.jumpstarttv.com/channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd
http://www.learnintegrationservices.com/VideoTutorials/
http://www.jumpstarttv.com/default.aspx

MSBI-SSAS VIDEOS FREE DOWNLOAD FROM
http://www.learnmicrosoftbi.com/
SSRS-2008(VIDEO'S)
http://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx

How take a cube Backup using SSIS Packages

http://davefackler.blogspot.com/2008/03/ssis-package-to-back-up-ssas-databases.html
http://dwbijourney.blogspot.com/2008/01/ssas-database-synchronization-for.html