Mahipal Padigela's Blog

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: DTEXEC, SQLCMD

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: excel, Microsoft.Office.Interop.Excel, SSIS

Speedup SSMS and Visaul Studio

SSMS

“C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe”  /nosplash /S MYSERVER /E /D MYDB

so, no splash screen, connects to MYSERVER server,  MYDB database using Integrated Security.

VS

“C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe”  /nosplash

Tags: SSMS, Visaul Studio