SSIS
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
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
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
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!!
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’.
——————————
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