IS - Expressions

@[User::ExceCreateTolPath]+@[User::ClientName]+_NewPlace_+((DT_WSTR, 4) YEAR( Getdate()   )  +Right("0"+ (DT_WSTR, 2) MONTH(  Getdate()    ) ,2) +Right("0"+ (DT_WSTR, 2) DAY(  Getdate()   ) ,2) )+"_"+ @[User::SchoolOPEID] +".xls"

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

IS–Parameters and Return Codes in Execute SQL Task

Connection Type	Parameter marker	Parameter name	Example SQL command
ADO	?	Param1, Param2, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET	@<parameter name>	@<parameter name>	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL and OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

Excel - Tips to Use.

Some of the tricks, which are usefull in tips, i will discuss in this post.

1) The following is one function usefull when we want to concate the value with any leading values.
=Right(Concatenate("00",A1),8)
=Right(Concatenate("xx",A1),8)

 

2) Replace is the one good function we can use as follows:
=REPLACE(A1,1,2,"A")     i.e [Replace(OldValue,StartNumber,NumberOfCharecters,NewValue)]

 

 

 

 

 

3) Convert date to text format as follows:

=TEXT(A1,”YYYY-MM-DD”)

=TEXT(A2,”YYYY/MM/DD”)

AS - TopDown & BottomUp

Comming Soon.... :)

RS - Using IIF Statement

IIF statement is pretty much easy if it is a straight forward single condition. If it is nested IIF statement (multiple conditions), it might some time takes a little time to understand and use it.
Ex: If there are 4 conditions to check then use only 3 IIF conditions.
     This is an example of cell Background Color Expression in SSRS.

= IIF(Fields!Data1.Value=”Lite Turquoise”,”#CCFFFF”, (IIF(Fields!Data1.Value="Ivory","#FFFFCC",   (IIF(Fields!Data1.Value=”Plum”, “#993366”, “#9999FF”)))))

Hope this article helps...
Thank you.

RS - Color Pallette codes

This pallette, shows the color names and they significant HTML color code/ BGColor.

Note: This above images taken from http://www.mvps.org/dmcritchie/excel/colors.htm

Hope this article help you to use colors/color codes.
Thank you. :)

TSQL - BETWEEN - DateTime

Convert the Date in to integer:

SELECT GETDATE()                                             ---- 2010-11-29 10:12:13.880

SELECT    (DATEPART(YY,GETDATE())*10000)  + 
               (DATEPART(M,GETDATE())*100)      +
               (DATEPART(DD,GETDATE()))                              ---- 20101129 

TSQL - BETWEEN - DateTime

I was using BETWEEN statement with the column that has data type of DATETIME. When debugging or validating data, i found out that the count is mismatch due to the DATETIME.
Ex:

Create table #BetweenDateTime (RowNumber INT Identity(1,1), DateTimeTest DateTime)

Insert into #BetweenDateTime values ( '2010-07-21 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-21 00:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-20 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-23 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-25 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-26 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-23 16:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-28 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-29 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-29 15:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-30 10:11:50.760')
From the above table:

Select * From #BetweenDateTime Where DateTimeTest Between '2010-07-21' And '2010-07-29'

It retrieve the following data:

RowNumber   DateTimeTest
1                     2010-07-21 17:11:50.760
2                     2010-07-21 00:11:50.760
4                     2010-07-23 17:11:50.760
5                     2010-07-25 17:11:50.760
6                     2010-07-26 17:11:50.760
7                     2010-07-23 16:11:50.760
8                     2010-07-28 17:11:50.760
the above retrieved data was invalid.
Solution:

Select * From #BetweenDateTime

Where Dateadd(Day, Datediff(Day, 0, DateTimeTest), 0) Between '2010-07-21' And '2010-07-29'

This returns the following data:
RowNumber  DateTimeTest
1                  2010-07-21 17:11:50.760
2                  2010-07-21 00:11:50.760
4                  2010-07-23 17:11:50.760
5                  2010-07-25 17:11:50.760
6                  2010-07-26 17:11:50.760
7                  2010-07-23 16:11:50.760
8                  2010-07-28 17:11:50.760
9                  2010-07-29 17:11:50.760
10                2010-07-29 15:11:50.760
i.e.  DATEADD(Day, DATEDIFF(Day, 0, DateTimeTest), 0) condition to round the DATETIME value to DATE.

try this for rounding values:
Select GetDate()
Select Dateadd(D, DateDiff(D, 0, GetDate()), 0)
Select Dateadd (M, DateDiff (M, 0, GetDate()), 0)
Select Dateadd (YYYY, DateDiff (YYYY, 0, GetDate()), 0)
Select Dateadd (Hour, DateDiff (HOUR, 0, GetDate()), 0)
I hope this article helps you!!!..
Thank you. :)

My First Post.....

Hi everyone!!!... This is my first post. I thought many times to create a blog to share my experiences. May be i was busy or lazy, but i couldn't start until today. Hopefully with full energy i started my blog BiSpecialist. I will update my blog with new item with simple to complex problems with solutions.

References

http://www.mssqltips.com/tip.asp?tip=1893