Dynamic filename for SSRS report subscriptions

Step to save the incremental files through SSRS report subscription.

File Name : @timestamp_Filename

Connection managers in SSIS 2012

		Connection Managers
		OLE DB	ADO.NET	ODBC	ADO
Control Flow	Execute SQL Task	×	×	×	×
	Bulk Insert Task	×
	Data Profiling Task		×
	CDC Control Task		×
Data Flow	OLE DB Source/Destination	×
	SQL Server Destination	×
	ADO.NET Source/Destination		×
	ODBC Source/Destination			×
	CDC Source		×
	Lookup Transformation	×
	OLE DB Command	×
	Fuzzy Lookup Transformation	×
	Fuzzy Grouping Transformation	×

Encrupt and Decrypt GPG files in SSIS

This summary is not available. Please click here to view the post.

New Features in SQL Server integration services 2012(SSIS 2012).

10 new Features in SSIS2012  

1.       ODBC Support

Sql server 2012 is the last version to support OLEDB.  In future it will be ODBC. ODBC is the only set of APIs that are available on all platforms including non-Windows platforms.

2.       Change Data Capture for SSIS

change data capture offers an effective solution to the challenge of efficiently performing incremental loads from source tables to data marts and data warehouses.

3.       Support for Variable Number of Columns in a Flat File

 Flat file with variable number of columns can be used as source, For example

 Id,name,rank,score

1,ramesh,1,100

2,ramya,2

3,darshita

4,aashrita,1,99

4.        Revamped Configurations(project level parameters)

Now we can define project level parameters and that can be accessed in the all the packages . 

5.       Script Component – you can debug it, finally!

Now you can debug script component, hurray and no need to use the message box for debug.

6.       Undo and Redo Features

This is one of the best feature was missing in earlier version of SSIS. This is much relief to the developers.

7.       Project level deployment

You can deploy the packages from project in a same way like reporting services and analytical services.

8.       Data Quality Services

·         Build a knowledge base and use it to perform a variety of important data quality tasks, such as data correction, enrichment, standardization, monitoring, and de-duplication.

·         Perform data cleansing and other data quality tasks through cloud-based reference data services provided by numerous data providers.

·         Use the profiling feature that's integrated into DQS's data quality tasks. This feature analyzes the integrity of your data and provides suggestions for updating it, along with the percent of confidence that the changes are correct. 

9.       A built-in logging infrastructure., Version Control With the new built-in logging infrastructure, you might not need to create your own SSIS framework. Information (e.g., what packages were executed, how long they took to run, error messages) is automatically logged to the SSISDB catalog. There are built-in reports that display all the details. When you deploy a project to the SSISDB catalog, the previous version of the project is kept on the server. Accessing the prior version is as easy as right-clicking the name of a project in Object Explorer to access the Versions menu

10. Connection Managers node. The new Connection Managers node lets you share connection managers across multiple packages. Thus, you don't need to create duplicate connections in each package.

 

Multidimensional vs Tabuar model difference(Sql server 2012)

Tabular	Multidimensional
In memory technology	Pre aggregated data
Can store large amount of data	Can store very large amount of data
No agreegation, column based storage	Uses agreegate to increase performance
Data compression 10X	Data compression 3x
Tablular will be faster at low Granularity	Its faster since aggregated
Data is less in gb	Data in terra bytes
Far less time to develop	Requires lot of planning
No dimensional modeling required	Dimensional modeling required
Upgrade from a power pivot	No upgrade path
Hierarchy creation is easy	Duplicate attribute key- create a key column