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

4) DBCC CHECKIDENT  (Database Console Command)

http://msdn.microsoft.com/en-us/library/aa258817(v=sql.80).aspx

DBCC CHECKIDENT
( table_name
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]
) [ WITH NO_INFOMSGS ]

Arguments:

Argument	Description
table_name	Is the name of the table for which to check the current identity value. The table specified must contain an identity column. Table names must comply with the rules for identifiers.
NORESEED	Specifies that the current identity value should not be changed.
RESEED	Specifies that the current identity value should be changed.
new_reseed_value	Is the new value to use as the current value of the identity column.
WITH NO_INFOMSGS	Suppresses all informational messages.

Parameter Specifications:

DBCC CHECKIDENT statement	Identity correction(s) made
DBCC CHECKIDENT ('table_name', NORESEED)	The current identity value is not reset. DBCC CHECKIDENT returns a report indicating the current identity value and what it should be.
DBCC CHECKIDENT ('table_name') or DBCC CHECKIDENT ('table_name', RESEED)	If the current identity value for a table is lower than the maximum identity value stored in the column, it is reset using the maximum value in the identity column.
DBCC CHECKIDENT ('table_name', RESEED,new_reseed_value)	Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.         If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions: If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values. If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

Exceptions: The following table lists conditions when DBCC CHECKIDENT does not automatically reset the current identity value and provides methods for resetting the value.

Conditions	Reset Methods
The current identity value is larger than the maximum value in the table.	Execute DBCC CHECKIDENT (table_name, NORESEED) to determine the current maximum value in the column, and then specify that value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) command. (or)                                                         Execute DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) with new_reseed_value set to a very low value, and then run DBCC CHECKIDENT (table_name, RESEED) to correct the value.
All rows are deleted from the table.	Execute DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) with new_reseed_value set to the desired starting value.

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