IS - Package Configurations

Here I would like to post a topic where the most of the environments face problem with SSIS Variable and connection managers..Etc when moving a SSIS package from one environment to another (Ex: Dev to Production). I have seen environments where the Package Configurations were maintained and also some not maintained. I personally like to maintain the Package Configurations. It is very time consuming and sometimes false databases and servers to be pointed when Package Configurations are not maintained.   Whether a package is small or complex, but still maintaining Package Configurations is make your work easy.

Type	Description
XML configuration file	An XML file contains the configurations. The XML file can include multiple configurations.
Environment variable	An environment variable contains the configuration.
Registry entry	A registry entry contains the configuration.
Parent package variable	A variable in the package contains the configuration. This configuration type is typically used to update properties in child packages.
SQL Server table	A table in a SQL Server database contains the configuration. The table can include multiple configurations.

IS - Regular Expressions Reference

Regular expressions and Functions :

http://msdn.microsoft.com/en-us/library/ms141827.aspx

IS - Connection Strings

Connection Strings property is to construct the bridge between two data flow task (sources). In this post i will bring the different Connection Strings at one place for future reference.
http://www.mssqltips.com/tip.asp?tip=1405

http://technet.microsoft.com/en-us/library/ee692867.aspx

http://technet.microsoft.com/en-us/library/ee332540.aspx

http://p-sql.spaces.live.com/Blog/cns!CADFA1CB6D2C0E32!349.entry?sa=669353575

Excel Connection String:Microsoft Jet OLE DB 4.0

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\FlatFiles\FlatFile.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
http://support.microsoft.com/kb/194124

Excel 2007:


http://msdn.microsoft.com/en-us/library/bb385832.aspx

TSQL - IDENTITY Property

IDENTITY is pretty much a very important property of a database. In post i will different places the use of (Creation, Seeding and Reseeding) IDENTITY property.
1) SQL Server allows only ONE identity column per table.
       Ex:
       Create Table #Identity_Post (RankNumber int identity(1,1),
                                                    First_Name varchar(40))
2) Identity(seed,increment)
      Ex: identity(1,1) means starting number is 1 and it will add 1 to next number
      identity(1000,5) means starting number is 1000 and it will add 5 to next number

3) SET IDENTITY_INSERT [Table_Name] [ON/OFF]
This property is one very usefull, When we want to insert a deleted rowid  (identity number) again in the table we can use this property.
     Ex:
     CREATE TABLE #TempTable (Rowid int IDENTITY(1,1), Name nvarchar(20))
     INSERT INTO #TempTable (Name) VALUES ('BiSpecialist')
     INSERT INTO #TempTable (Name) VALUES ('BwSpecialist')
     INSERT INTO #TempTable (Name) VALUES ('IsSpecialist')
     INSERT INTO #TempTable (Name) VALUES ('RsSpecialist')

     ----Delete a row so that we can create a gap in between the rows.

     DELETE from #TempTable WHERE Name = 'RsSpecialist'

     SELECT * FROM #TempTable

     ----Now it will through an error    

     INSERT INTO #TempTable (Rowid, Name) VALUES(4, 'AsSpecialist')

     ----SET IDENTITY_INSERT to ON.    

     SET IDENTITY_INSERT #TempTable ON

     ----Now its a identity magic    

     INSERT INTO #TempTable (Rowid, Name) VALUES(4, 'AsSpecialist')

     ----Then SET IDENTITY_INSERT  OFF, so that next time it won't allow the explicit insert.    

     SET IDENTITY_INSERT #TempTable OFF

     SELECT * FROM #TempTable

     DROP TABLE #TempTable

IS - File Formats

Comming Soon...  :)

IS - DelayValidation

Comming Soon...  :)

IS - Containers

Comming Soon...  :)

IS - ForEachLoop Task

Comming Soon... :)

IS - ScriptTask - Protective Excel

I have fallen in to a scenario where i needed to create multiple Excel data files  from a source.   This task i was enjoyed doing. Because of a Script Task which is totally on creation of Protetctive Excel sheets, Locking some columns and coloring required column cells. I thought it is a good project to share with you all.
--> Source DB: Adventureworks database
--> Table that i would like to split in to multiple ExcelSeets:
--> Create multiple Excel sheets bassed on distinct Item. ex: File name will be like :   Item_20101105.xls


Option Strict Off
Option Explicit On

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

Public Class ScriptMain
    Public Sub Main()

    Dim xlApp As Object

    Dim xlBook As Object

    Dim xlWorkSheet As Object

    Dim objRange As Object

    Dim strExcelFile As String

    Dim fullfilename As String

    Dim ClientName, CurrentDate, SchoolOPEID As String

    ClientName = CStr(Dts.Variables("ClientName").Value)

    CurrentDate = CStr(Dts.Variables("CurrentDate").Value)

    SchoolOPEID = CStr(Dts.Variables("SchoolID").Value)

    fullfilename = ClientName + "_NewPlace_" + CurrentDate + "_" + SchoolID + ".xls"

    Try

    strExcelFile = "C:FlatFileSource\" + fullfilename

    xlApp = CreateObject("Excel.Application")

    xlBook = xlApp.Workbooks.Open(strExcelFile)

    If WorksheetExists(xlBook, "Excel_Destination") Then

       xlWorkSheet = xlBook.Sheets("Excel_Destination")

                ' Color and format title row

       objRange = xlWorkSheet.Range("A1", "BW1")

       objRange.Font.Size = 11

       objRange.Font.Bold = True

       objRange.Interior.ColorIndex = 16

       objRange.Font.ColorIndex = 1

                'Adjust titles to show up

       objRange.EntireColumn.Autofit()

                'Color required field using following code.

                ' Dim myCount As Integer

                ' myCount = xlWorkSheet.UsedRange.Rows.Count

       Const xlEdgeLeft = 7

       Const xlContinuous = 1

       Const xlAutomatic = -4105

       Const xlThin = 2

       Const xlGray16 = 17

       Const xlHairline = 1

       objRange = xlWorkSheet.Range("E1:K1", "E65535:K65535")

                'objRange.Interior.ColorIndex = 3

       objRange.Borders.LineStyle = xlContinuous

       objRange.Borders.ColorIndex = 3

       objRange.Borders.Weight = xlThin

       xlWorkSheet.Columns("AI:AI").NumberFormat = "0,#"

       objRange = xlWorkSheet.Range("BX:ET", "BX65535:ET65535")

       objRange.Columns.Delete()

                ' Following will lock all columns except specified.

       xlWorkSheet.Unprotect()

       Dim strmypassword

       xlWorkSheet.Protection.AllowEditRanges.Add("FirstSet", xlWorkSheet.Columns("E:AP"))

       xlWorkSheet.Protection.AllowEditRanges.Add("SecondSet", xlWorkSheet.Columns("BE"))

       xlWorkSheet.Protect(strmypassword)

       xlBook.Save()

     End If

       Catch e As Exception

       MsgBox("ERROR:" & e.ToString, MsgBoxStyle.Critical)

        Finally

            If Not xlBook Is Nothing Then

                xlBook.Close()

                xlBook = Nothing

            End If

            If Not xlApp Is Nothing Then

                xlApp.Quit()

                xlApp = Nothing

            End If

        End Try

        Dts.TaskResult = Dts.Results.Success

    End Sub

    Function WorksheetExists(ByRef xlWorkbook As Object, ByVal strWorksheetName As String) As Boolean

        Dim xlWorksheet As Object

        If xlWorkbook Is Nothing Then

            WorksheetExists = False

        Else

            xlWorksheet = xlWorkbook.Sheets(strWorksheetName)

            WorksheetExists = Not xlWorksheet Is Nothing

        End If

    End Function

End Class