SSIS

Read Object variable inside SSIS Script Component


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 CreateNewOutputRows()
Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim row As System.Data.DataRow

olead.Fill(dt, Me.Variables.ObjTradeFeedData)

For Each row In dt.Rows
With Output0Buffer
.AddRow()
.TradeID = row("TradeID").ToString
.BookName = row("TradeBook").ToString
.PortfolioID = row("TradePortfolioID").ToString
.IssuerSDSID = row("UnderlyingSDS").ToString
.CreditRiskCurveId = row("CreditRiskCurveId").ToString
.Currency = row("Currency").ToString
.ValuationDate = row("ValuationDate").ToString
End With
Next
End Sub
End Class

Tags: , ,

Friday, March 2nd, 2012 SSIS No Comments

Increment a Variable Value in SSIS ScriptComponent


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
Public Sub New()
Counter = 0
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Counter += 1
Row.NewTenorID = Counter
End Sub
Public Overrides Sub PreExecute()
MyBase.PreExecute()
Counter = Variables.MaxTenorID
End Sub

End Class

Tags: , ,

Friday, March 2nd, 2012 SSIS No Comments

Process SSAS Measure Groups (Fact tables) using SSIS

for my own reference:

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

Public Class ScriptMain
Public Sub Main()
Dim strFactNames As String = Dts.Variables("FactNames").Value.ToString
Dim strCubeName As String = Dts.Variables("CubeName").Value.ToString
Dim bt(0) As Byte
Dim ExecutionSuccessfull As Boolean = True
Dim oConnection As ConnectionManager

oConnection = Dts.Connections("SSAS")
Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))

Dts.Events.FireInformation(-1, "Fact Tables to process: ", strFactNames, "", -1, True)
Dts.Log("Fact Tables to process:" + strFactNames, 0, bt)

Dim oServer As New Server
oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
Dim oDB As Database = oServer.Databases.FindByName(sDatabaseID)
Dim oCube As Cube = oDB.Cubes.FindByName(strCubeName)

Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull

If oDB Is Nothing Or oCube Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else

Dim oFact As MeasureGroup

oServer.CaptureXml() = True ' Start capturing XML.
For Each oFact In oCube.MeasureGroups
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If InStr(1, strFactNames, oFact.Name.Replace(" ", "")) > 1 Then
oFact.Process(ProcessType)
End If
Next
oServer.CaptureXml() = False ' Stop capturing XML

' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
Dim oResults As XmlaResultCollection
Dim oResult As XmlaResult

Dts.Log("Processing Facts Now", 0, bt)
Dts.Events.FireInformation(-1, "Process Facts Script: ", "Processing Facts Now", "", -1, True)
oResults = oServer.ExecuteCaptureLog(True, True, True)

Dim oMessage As XmlaMessage

'Log the errors and warnings
For Each oResult In oResults
For Each oMessage In oResult.Messages
If oMessage.GetType.Name = "XmlaError" Then
Dts.Log("XMLA Error while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Error while processing facts:", oMessage.Description, "", -1, True)
'The processing failed
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warning while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warning while processing facts:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
End If
Next oMessage
Next oResult
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done

If ExecutionSuccessfull Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

End Class

Tags: , ,

Friday, March 2nd, 2012 ssas, SSIS No Comments

Process SSAS Dimensions using SSIS

for my own reference:

populate DimensionNames var from a exec sql task:

Select CAST(REPLACE(STUFF(
(SELECT ',' + a.tablenamecube AS [text()]
from config.BusinessprocessTables a
WHERE TableType ='D' AND BusinessProcess = 'Metrics' for xml PATH('')),1,1,'' ),' ','') AS VARCHAR(1000)) AS DimensionNames


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

Public Class ScriptMain
Public Sub Main()
Dim strDimensionNames As String = Dts.Variables("DimensionNames").Value.ToString
Dim bt(0) As Byte
Dim ExecutionSuccessfull As Boolean = True
Dim oConnection As ConnectionManager
oConnection = Dts.Connections("SSAS")
Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))

Dts.Events.FireInformation(-1, "Dimensions to Process:", strDimensionNames, "", -1, True)
Dts.Log("Dimensions to Process:" + strDimensionNames, 0, bt)

Dim oServer As New Server
oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
Dim oDB As Database = oServer.Databases.FindByName(sDatabaseID)

Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull 'ProcessUpdate

If oDB Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else
Dim oDim As Dimension

oServer.CaptureXml() = True ' Start capturing XML.
For Each oDim In oDB.Dimensions
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If (oDim.MiningModel Is Nothing) Then
If InStr(1, strDimensionNames, oDim.Name.Replace(" ", "")) > 1 Then
oDim.Process(ProcessType)
End If
End If
Next
oServer.CaptureXml() = False ' Stop capturing XML

' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
Dim oResults As XmlaResultCollection
Dim oResult As XmlaResult

Dts.Events.FireInformation(-1, "Process Dimensions script", "Processing Dimensions ow", "", -1, True)
Dts.Log("Processing Dimensions Now", 0, bt)
oResults = oServer.ExecuteCaptureLog(True, True, True)

Dim oMessage As XmlaMessage

'Log the errors and warnings
For Each oResult In oResults
For Each oMessage In oResult.Messages
If oMessage.GetType.Name = "XmlaError" Then
Dts.Log("XMLA Errors while processing Dimensions:" + oMessage.Description, 0, bt)
'The processing failed
Dts.Events.FireInformation(-1, "XMLA Errors while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warnings while processing Dimensions:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warnings while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
End If
Next oMessage
Next oResult
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done

If ExecutionSuccessfull Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

End Class

Tags: , ,

Friday, March 2nd, 2012 SSIS No Comments

sqlcmd & dtexec params and syntax

SQLCMD
sqlcmd -S MySERVER -E -d master -i APPLY_DB_SECURITY.sql -o LOG\APPLY_DB_SECURITY.log

DTEXEC
dtexec /FILE “\\MYSERVER\BI Environments\myfolder\ETL\SSIS\MasterETL\LoadControl.dtsx”
/CONNECTION SSISControl;”\”Data Source=MYSERVER;Initial Catalog=mydb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\”” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF
/SET “\Package.Variables[User::RootPath].Properties[Value]”;”\\MYSERVER\BI Environments\mpadigela_dev1\ETL”
/SET “\Package.Variables[User::ServerName].Properties[Value]”;”MYSERVERNAME”
/SET “\Package.Variables[User::EnviromentName].Properties[Value]”;”myEnvironment_”
/REPORTING EWCDI

Tags: ,

Tuesday, December 9th, 2008 SQL Server, SSIS No Comments

Manipulating, Programming EXCEL in SSIS(2005) using ScriptTask -II

Following on from my previous post, this is how you’d automate Excel using SSIS. The following sample code inside a ScriptTask extracts a given worksheet from each workwook into another consolidated workbook. The ScriptTask is placed inside a ForEachLoop container that loops through given folder structure for .xls files.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel
Imports System.IO

Public Class ScriptMain

    Public Sub Main()
        'FolderPath,FullFilePath,CombinedFilePath
        Dim FolderPath As String = Dts.Variables("FolderPath").Value.ToString
        Dim FullFilePath As String = Dts.Variables("FullFilePath").Value.ToString
        Dim CombinedFilePath As String = Dts.Variables("CombinedFilePath").Value.ToString
        Dim fi As FileInfo
        Dim FileCreateddate As String

        If FullFilePath <> CombinedFilePath Then  'exclude combined file that we are building

            Dim app As Microsoft.Office.Interop.Excel.Application
            Dim SourceWb As Workbook
            Dim CombinedWb As Workbook
            Dim ws As Worksheet
            Dim FileName As String
            Dim i As Integer = 1

            fi = New FileInfo(FullFilePath)
            FileCreateddate = CStr(Format(fi.CreationTime, "yyyymmdd"))
            FileName = Replace(fi.Name.Substring(0, fi.Name.Length - 4), " ", "") ' exclude .xls

            If FileCreateddate >= CStr(Format(Today, "yyyymmdd")) Then  ' file satisfies required criteria

                app = New Microsoft.Office.Interop.Excel.Application
                app.DisplayAlerts = False 'suppress excel warnings/alers
                SourceWb = app.Workbooks.Open(FullFilePath, False, True)
                CombinedWb = app.Workbooks.Open(CombinedFilePath, False)

                For Each ws In SourceWb.Worksheets
                    If ws.Name.ToString = "Rates for SB" Then
                        ws.Name = FileName
                        ws.Copy(After:=CombinedWb.Sheets(i))
                        Exit For
                    End If

                Next

                SourceWb.Close(False) ' close source wb without saving
                CombinedWb.Save()
                CombinedWb.Close()
                SourceWb = Nothing
                CombinedWb = Nothing
            End If

            'cleanup
            app.Quit()
            If Not app Is Nothing Then
                Runtime.InteropServices.Marshal.ReleaseComObject(app)
                GC.Collect(0) 'because it's not a .net library, call GC to cleanup
            End If

        End If
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Tags: , ,

Tuesday, December 9th, 2008 SSIS No Comments

Manipulating, Programming EXCEL in SSIS(2005) using ScriptTask – I

This can be achieved using Office Primary Interop Assemblies (PIAs).

Office XP PIAs can be downloaded from

http://www.microsoft.com/downloads/details.aspx?familyid=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en

STEPS:

  • Instal Microsoft.Office.Interop.Excel.dll to GAC (C:\WINDOWS\assembly)
  • Copy Microsoft.Office.Interop.Excel.dll to C:\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx (so SSIS ScriptTask can list it under References dialog)
  • Register excel assembly — regedit /s Microsoft.Office.Interop.Excel.dll.reg

now we can add a reference to this library from ssis ScriptTask and start coding using excel objects!!

Tags: , ,

Wednesday, November 26th, 2008 SSIS No Comments

Connecting to Excel 2007 from SSIS

Create OLE DB Connection using Microsoft Office 12.0 ACE (Access Database Engine) OLEDB provider.

 

Use the following Extended properties:
Excel 12.0;HDR=YES
HDR –> 1st row is HEADER row (column Headings)



*****************without extended properties, you might get this error**********************
TITLE: Connection Manager
——————————

Test connection failed because of an error in initializing provider. Could not find file ‘C:\temp\Sample.xlsx’.

——————————

Tags: , , , ,

Wednesday, May 28th, 2008 SSIS No Comments

Start/Stop windows services from SSIS

We had a requiremnet to stop Reporting Services windows service(ReportServer) at the beginning of ETL load and start it at the end. I used a couple of Script tasks (one at the beginning and one at the end of SSIS Master Package) with code something like this:

drag a script task onto control flow and add the following code to stop the service

'stop ReportServer service script task
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ServiceProcess

Public Class ScriptMain

    Public Sub Main()
        Dim controller As New ServiceController
        controller.MachineName = "."
        controller.ServiceName = "ReportServer"

        Dim status As String = controller.Status.ToString

        If status.ToLower = "running" Then
            controller.Stop()
        End If

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class


drag a script task onto control flow and add the following code to start the service

'start ReportServer service script task
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ServiceProcess

Public Class ScriptMain
    Public Sub Main()

        Dim controller As New ServiceController
        controller.MachineName = "."
        controller.ServiceName = "ReportServer"

        Dim status As String = controller.Status.ToString

        Do While Not status.ToLower = "running"
            Try
                controller.Start()
            Catch e As Exception
                controller.Refresh()
                status = controller.Status.ToString
            End Try
        Loop

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

Tags: ,

Saturday, February 16th, 2008 SSIS, SSRS No Comments