What’s new in Denali CTP3? -- SSIS

1. All Tasks and Transformations are with new look and feel (round corners)

2. Now we can use UNDO if we delete the control object.

3. Toolbox comes now empty
To get the list of all tool box items need to click on right top side

4. Magical Zoom In/Out Scale added

5. New Look and Feel of the Tool Box (Divided on Category base)

6. New Tab added for Parameters

7. New Tab window added (Getting Started) for online learning.

8. Name is coming with * (star) means it is modified but not saved.

9. Scope of variable is package level (By default now) and we can change it any time now.
Now want to change the scope so select the variable and click second button next to create variable to move the varible from Package level to Item level like this.

10. Few new Tasks added e.g. Expression Task

11. Addition of DataViewer is simple and we can disable as well.

12. Development in VS2010

13. Even deleting flow lines (in disconnected mode) we can open the following transformations.

14. After changing in the source (Table Field or Table name changed) we can map the fields by clicking on the first flow line instead of opening each and every control/Transformation.
After setting (mapping) in the Resolve References options rest all the transformation with update automatically.

15. New group feature added in Data Flow table as well (if we want to keep all/some of the transformations in one group now we can do). It is somehow similar to container but now possible in Dataflow Tab.

16. Shared DataSource Connection: If we create Shared datasource it automatically comes (appears) in every package (by default). Note: We cannot do undo once the connection created/deleted.

17. While executing and after completion the package execution now the symbol changes.

18. Work Offline Connection Manager:
Now we can set connection manager as offline but this will be activate till the package is open. Once the package closed and reopen then connection will turn on automatically.
Many more properties also added like Convert to Project Connection and Test Connectivity.

19. We can maintain up to 10 version of the SSIS package now.
a. Once the package is deployed in SSISDB under integration Services folder then we can maintain the package version up to 10 versions. If we want we can change the version nos.
b. Not only version but we can modify the connection manager, variable default values and 32 bit compatibility and move the package.
c. We can run the reports as well on top of our packages

20. Package Reference (scope) and Package variable scope enhanced.

Some Video URLS:

1. http://www.youtube.com/watch?v=JF_LjLy32j8&feature=related (Introduction to SSIS)
2. http://www.youtube.com/watch?v=5rUyQdCeYx4(Variable scope, Expression task)
3. http://www.youtube.com/watch?v=5N0Ub77ss0g&feature=related(What's New in SSIS Denali)
4. http://www.youtube.com/watch?v=5N0Ub77ss0g (SSIS Training)
5. http://www.youtube.com/watch?v=sD-gX3ixKAg&feature=related (3 new features in Denali)
6. http://www.youtube.com/watch?NR=1&v=UX2pfGoTDHc (Create SSISDB -I)
7. http://www.youtube.com/watch?v=UPTI4-94O_I&feature=related(Create SSISDB -II)
8. http://www.youtube.com/watch?v=vZa3-dx8WV8&feature=related (SSIS Version maintenance)
9. http://www.youtube.com/watch?v=kw8k8effv5U&feature=related(Working with Integration Services projects)

What’s new in Denali CTP3? -- SQL Server DB

1. New DB default size increased by 1 MB

2. Now Integration Service connection introduced in SSMS:
Click right mouse button to create SSIS catalog with some password. If we see there is no database related to SSIS in Database folder as well as in Integration Services folder so now when we create a SSIS catalog and that SSIS catalog DB will be created in Databases folder as well.
3. New Functions in SQL Server:

--String
Select Concat ('Vinod ', 'K', ' Sharma ') as FullName

DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/MMM/yyyy', 'en-US' ) AS Result

SELECT FORMAT (25.456, '0.00') as MyAmount

--Logical
SELECT Choose (4,'Red','Blue','Greeen', 'Gray','White','Orange') as MyColor
SELECT IIF(1<0,'-ve','+ve') as Result

--Conversion
SELECT Case When Try_Convert(Float,'Vinod') IS NULL
Then 'Cast Failed'
Else 'Cast Succeeded'
End as TryConvert

SELECT Try_Parse('1234' as money using'fr-FR') as Result
SELECT Try_Parse('01/01/2011' as datetime2) as Result
SELECT PARSE('Monday, 13 December 2010' as DateTime2) as Result

--Date and Time
SELECT Eomonth('08/29/2011') as LastDayofMonth
SELECT Eomonth('08/29/2011',3) as LastDayofMonth
SELECT TIMEFROMPARTS(23,59,59,0,0) as Result
SELECT SMALLDATETIMEFROMPARTS(2011,7,4,00,00) AS Result
SELECT DATEFROMPARTS(2011,12,31) as Date
SELECT DATETIMEFROMPARTS(2011,12,31,23,59,59,0) as DateTime
SELECT DATETIME2FROMPARTS (2010, 12, 31, 23, 59, 59, 0, 0) AS DateTime
SELECT DATETIMEOFFSETFROMPARTS (2010, 12, 31, 14, 23, 23, 0, 12, 0, 7) as DateTime

--Math Function
Select LOG(10,2) as LogBase2

4. Sequence Function Introduction:
Create Sequence S0
GO
Select Next Value For S0
----------------------
Create Sequence S1
As Int
Increment By 1;
GO
Select Next Value For S0
--------------------
Create Sequence S2
As Int
Start with 1
Increment By 1;
GO
Select Next Value For S2
-----------------------
Create Sequence S3
As TinyInt
Start with 1
MinValue 1
MaxValue 5
Cycle; -- like a loop but with reset option
GO
Select Next Value For S3

--Altering the Sequence
ALTER SEQUENCE S2 RESTART WITH 1
GO
SELECT NEXT VALUE FOR S2

--Dropping the Sequence
DROP SEQUENCE S2
GO
SELECT NEXT VALUE FOR S2
GO

5. Software Installation Steps added (few new options to install)

6. Debugging is also easy in Denali

7. Index enhancement in Denali:
a. We can use Varchar(max), NVarchar(max) and Varbinary(max) columns

8. New concept of Column stored Index (first time in the Database world)

9. Query windows arrangement (the way we want, drag to anywhere)

10. Table Partition Modification.
a. Earlier there were 1000 partition By Default and can be increased up to 15000 BUT now in Denali CTP3 have 15000 partitions by Default.

11. New feature like Surrounded with (Ctrl+K, Ctrl+S) and Insert Snippets(Ctrl+k, Ctrl+X)

12. New intellisense

13. High query performance enhancements

Some Video URLs:

1. http://www.youtube.com/watch?v=AQ6UxKKRCh8 (Denali CTP3 Installation)
2. http://www.youtube.com/watch?v=t4JjyWCiv08&feature=related (Sequence Object)
3. http://www.youtube.com/watch?v=eshmqji8Wso&feature=related (Debugging In SQL Server Denali)
4. http://www.youtube.com/watch?v=zCENtf-uJgc&feature=related(Coding In SQL Server Denali)
5. http://www.youtube.com/watch?v=kwV7Ht67qMY&feature=related(Partition limit enhancement)
6. http://www.youtube.com/watch?v=jOwgT_xkneU&feature=related(Index enhancement)
7. http://www.youtube.com/watch?v=eCie3Jkryf8&feature=related(Denali Introduction & Network Configuration)
8. http://www.youtube.com/watch?v=XvEsVxpNadc&feature=related(Data Quality Services)
9. http://www.youtube.com/watch?v=LTpIb9j06qI&feature=related (Denali Beyond Relational)
10. http://www.microsoft.com/sqlserver/en/us/future-editions.aspx (Denali download)

Highlights on Denali (SQL Server 2011) Features

SQL SERVER:

http://www.youtube.com/watch?v=dSlOIMuG-k4

14 New Functions in SQL Server Denali CTP3 (Based on Category these are into 5)

String
Concat ('Vinod ', 'K ', ' Sharma ') as FullName
Format (@StartDate,'yyyy/mmm/dd') as Date
FORMAT (25.456, '0.00') as MyAmount


Logical
Choose (4,'Red','Blue','Greeen', 'Gray','White','Orange') as MyColor
IIF(1<0,'-ve','+ve') as Result


Conversion
Select Case When Try_Convert(Float,'test') IS NULL
Then 'Cast Failed'
Else 'Cast Succeeded'
End as TryConvert

Select Try_Parse('1234' as money using'fr-FR') as Result
Select Try_Parse('01/01/2011' as datetime2) as Result
Select PARSE('Monday, 13 December 2010' as DateTime2) as Result


Date and Time
Select Eomonth('07/19/2011') as LastDayofMonth
OR
Select Eomonth('07/19/2011',3) as LastDayof Month

Select TIMEFROMPARTS(23,59,59,0,0) as Result

Select SMALLDATETIMEFROMPARTS(2011,7,4,00,00) AS Result

Select DATEFROMPARTS(2011,12,31) as Date
Select DATETIMEFROMPARTS(2011,12,31,23,59,59,0) as DateTime
Select DATETIME2FROMPARTS(2011,12,31,23,59,59,0) as DateTime
Select DATETIMEOFFSETFROMPARTS(2011,12,31,23,59,59,0,5,7,9) as DateTime

Math Function
Select LOG(10,2) as LogBase2


---------------------------------------------------

SSIS:

http://www.youtube.com/watch?v=JF_LjLy32j8&feature=related
http://www.youtube.com/watch?v=5rUyQdCeYx4
http://www.youtube.com/watch?v=5N0Ub77ss0g&feature=related


1. Now we can use UNDO if we delete the control object.
2. Magical Zoom-In/Out Scale added
3. New Look and Feel of the Tool Box (Divided on Category base)
4. New Tab added for Parameters
5. New Tab window added (Getting Started) for online learning.
6. Name is coming with * (star) means it is modified but not saved.
7. Scope of variable is package level (By default now) and we can change it any time.
8. Few new Tasks added e.g. Expression Task
9. Addition of DataViewer is simple and we can disable as well.
10.Development in VS2010
11.Even deleting flow lines (in disconnected mode) we can open the transformations.
12.After changing in the source (Table Field or Table name changed) we can mapping the fields by clicking on the first flow line instead of opening each and every control/Transformation.
13. New group feature added (if we want to keep all some of the transformations in one group now we can do). It is somehow similar to container but now possible in DataFlow Tab.
14. Shared DataSource Connection: If we create Shared datasource connect it automatically comes in every package.

Simple way to take DB Backup

backup database Northwind to Disk = 'C:\FolderName\Northwind.bak'

Different ways to Create SQL Table

Create Table Category2
(
ID INT Primary Key,
NAME VARCHAR(50)
)
Insert into Category2 (ID, Name)
Select CategoryID, CategoryName from Category
GO
Select ID, Name from Category2
GO


--OR


Select CategoryID as ID, CategoryName as Name INTO Category2 from Category
Select ID, Name from Category2


--OR


Create table #Category2
(
ID INT Primary Key,
NAME VARCHAR(50)
)
Insert into #Category2
Select CategoryID, CategoryName from Category

Select ID, Name from #Category2
GO

--OR

Create View Category2
as
Select CategoryID as ID, CategoryName as Name from Category
GO
Select ID, Name from Category2
GO

--OR

Declare @Category2 Table
(ID INT Primary Key,
NAME VARCHAR(50)
)
Insert into @Category2 (ID, Name)
Select CategoryID, CategoryName from Category

Select ID, Name from @Category2
GO

--OR


With Category2
( ID, Name)
AS
(
Select CategoryID, CategoryName from Category
),

Select * from Category2
GO

How to populate Time Dimension:

with mycte as
(
select cast('12:00 AM' as time) starttime
union all
select dateadd(s,1,starttime)
from mycte
where dateadd(s,1,starttime) < cast('11:59 PM' as time)
)
select starttime AS Time,datepart(hh,starttime) as Hour,datepart(mi,starttime) as Minutes,datepart(s,starttime) as Seconds
from mycte
OPTION
(MAXRECURSION 0)

How to Limit the No of Records Per Page in SSRS

e.g. (Columns)
DepartmentName, EmployeeId, Employee Name, Designation, Reporting Manager

We want to show 4 DepartmentName per Page so here is the solution:
Need to use DepartmentName in group (Add a group as parent group, Group by DepartmentName) .
Once it is done then go again in the group properties and change the expression like Change Group on value with following mentioned expression and use group by: (Change in Exp with)

=Ceiling((RowNumber(Nothing)) / 4)
OR
=Floor((RowNumber(Nothing) - 1) / 4)

NOTE: Now you have to add DepartmentName as normal column to show values in the report so set the width of group column to 0 and change the text color, background color (if set) to none (white) and font size to 1, remove borders so that this column should not apper in the report (Please do not hide it otherwiswe values will not appear in the report).

How to reset Identity value in SQL Server Table

DBCC CHECKIDENT ('your table name',RESEED,new_seed_value)

Tables or SP structure modified (IF)

-- tables Structure modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='U'


-- Stored procedures modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='P'

-- Tables structure modified in last 7 days
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-7,GETDATE())
AND type='U'

How to findout which table modified Today

--This query will show the tables and their insert/update records for current date but only for those tables who have ModifiedDate Column in the table

CREATE TABLE #TEMPRECORD
(
ID INT IDENTITY,
TABLENAME VARCHAR(200)
)

DECLARE @I INT =1,
@TOTAL INT =0,
@SQL VARCHAR(500),
@TNAME VARCHAR(200),
@COLUMNNAME VARCHAR(100)

SELECT @COLUMNNAME = 'MODIFIEDDATE'

INSERT #TEMPRECORD (TABLENAME)
SELECT DISTINCT O.NAME FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C
ON O.OBJECT_ID = C.OBJECT_ID
WHERE O.TYPE ='U'
AND C.NAME = @COLUMNNAME

SELECT @TOTAL = COUNT(*) FROM #TEMPRECORD
WHILE @I <= @TOTAL
BEGIN
SELECT @TNAME = NAME FROM SYS.OBJECTS WHERE NAME = (SELECT TABLENAME FROM #TEMPRECORD WHERE ID =@I )
SELECT @SQL ='SELECT * FROM '+@TNAME +' WHERE CONVERT(VARCHAR(12),'+@COLUMNNAME+',101) = CONVERT(VARCHAR(12),GETDATE(),101)'
SELECT @TNAME AS [TABLE NAME]
EXEC (@SQL)
SET @I=@I+1
END

GO

DROP TABLE #TEMPRECORD

GO