List All SSIS Packages Deployed On Your Integration Server

One way of checking wich SSIS packages you've deployed on your integration server and wich version build numbers they have is by running a TSQL query against the MSDB database on the server where your integration services  is running.

I found the following blogpost with a very usefull TSQL script:

http://blog.hoegaerden.be/2010/01/10/list-all-ssis-packages-deployed-on-your-integration-server/

NOTE: the query was written for SQL Server 2008. For SQL Server 2005: sysssispackagefolders => sysdtspackagefolders90 and sysssispackages => sysdtspackages90.

BI Architects visited the Microsoft SQL Server 2012 symposium

Yesterday a delegation of BI Architects visited the Microsoft SQL Server 2012 symposium at the Media Plaza in Utrecht.

During the symposium Microsoft presented the new version of their informationplatform SQL Server 2012. Richard Chin, general manager SQL Server strategy at Microsoft, kicked off the symposium with a keynote. After the keynote the symposium was divided into three blocks: Breakthrough insight with SQL Server 2012, Delivering Mission Critical Confidence to the Modern Enterprise and Building the Modern Enterprise with Hybrid IT.

A view at the stunning Media Plaza during the Microsoft SQL Server 2012 symposium.

As BI Architects we were mostly interested in the block concerning business intelligence, Breakthrough insight with SQL Server 2012. This block included a keynote from Marcel Westra, technology specialist BI at Microsoft NL, explaining the vision behind SQL Server 2012 and demonstrating the new features like PowerView and the integration of Hadoop (Big Data).

All of the speakers outlined five points off improvement for SQL Server 2012:

• Greater availability. Deliver the required 9s and data protection with AlwaysOn, delivering added functionality over CTP3 that allows customers to experience multiple, readable secondaries for distributed scale of reporting and backup workloads and support for FileTable and FILESTREAM which brings first-class HA to complex data types.
• Blazing-fast performance. More effectively govern performance in multi-tenancy environments like private cloud. Resource Governor enhancements include support for 64 resource pools, greater CPU usage control, and resource pool affinity for partitioning of physical resources and predictable resource allocation.
• Rapid data exploration. Empower end users with new insights through rapid data exploration and visualization. Discover new insights at the speed of thought with more feature/functionality in Power View, the highly interactive, familiar browser-based data exploration, visualization, and presentation experience for end users.
• Credible, consistent data. In addition to CTP3 functionality delivered for Data Quality Services and Master Data Services, customers can better support heterogeneous data within Data Warehouses through new Change Data Capture (CDC) support for Oracle.
• Optimized productivity. Optimize IT and developer productivity across server and cloud with the new SQL Server Data Tools, a unified development experience for database and business intelligence projects, and cloud-ready capabilities within SQL Server Management Studio for snapshot backups to the Windows Azure Platform. Additionally, SQL Server 2012 offers a new version of Express – LocalDB. SQL Express LocalDB is a lightweight version of Express with all its programmability features, yet runs in user mode with a fast, zero-configuration installation and short list of pre-requisites. For more information and to try it out, go here.

There are three main editions planned for SQL Server 2012: Standard, Business Intelligence (new) and Enterprise. Business Intelligence adds better reporting via PowerView, PowerPivot for SharePoint Server, Data Quality Services, Master Data Services, and an In-Memory Tabular BI Semantic Model.

For more information about the new features go here.

All of the keynotes will be posted online by Microsoft,  keep watching this blog to find out were you can watch them!

Update:
The videos aren't online yet, but Microsoft sended the presentation to us in the PDF file format. Download the 15 MB zip file from this link.

Query to find the size of the database and database file

Technique 1:


     EXEC sp_helpdb

Technique 2: For a specific database:

sp_helpdb'adventureworks'

And you get the following output: (this one gives you the database and data file information for a specific database)




Technique 3: be in the context of the database and run sp_spaceusedGives you the database size and certain more properties of it:


     EXEC sp_spaceused

This is what you get:

Technique 4: use sys.master_files


SELECTDB_NAME(database_id)AS DatabaseName,
Name AS LogicalName,
size*8/1024 Size_in_MB,
max_size
FROM sys.master_files
WHEREDB_NAME(database_id)='AdventureWorks'
GO




If you liked this post, do like us on FaceBook at http://www.facebook.com/pages/BI-Architects/249973345047634?sk=wall

Microsoft Visual Studio 11 Beta to Land on February 29th

Along with the release of Windows 8 Consumer Preview, Microsoft will also be unveiling Visual Studio 11's beta version next Wednesday.
Visual Studio 11 along with Team Foundation Server 11 has "hundreds" of latest features and Microsoft is addressing a very small selection before the release of the beta version. Visual Studio 11 will offer holistic solutions and also performance enhancements for various regularly performed tasks.

Unlike Visual Studio 2010, which is full of attractive colours, the new Visual Studio 11 is more "grey" to look at. For improving the scope to focus on text without any distraction the company decided to get rid of bright colours on the Integrated Development Environment (IDE), reported ARS Technica.
The new version is simpler as well as flatter as it does not have subdivisions, gradients and any other unnecessary details. This Metro designed version looks a lot like Visual Studio 2003, Office XP and Expression Blend. However, there is another version having darker theme but same simple and flat design.
Microsoft confirmed that Visual Studio 11 beta and .NET 4.5 beta will launch on 29th February.

The default toolbars in VS 2010

The default toolbars in VS 11

For more information about the GUI go to MSDN.

Setting the Protection Level of Packages

The following table describes the protection levels that Integration Services provides. The values in parentheses are values from the DTSProtectionLevel enumeration. These values appear in the Properties window that you use to configure the properties of the package when you work with packages in Business Intelligence Development Studio.

Protection level	Description
Do not save sensitive (DontSaveSensitive)	Suppresses the values of sensitive properties in the package when the package is saved. This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.
	When used with the dtutil utility (dtutil.exe), this protection level corresponds to the value of 0.
Encrypt all with password (EncryptAllWithPassword)	Uses a password to encrypt the whole package. The package is encrypted by using a password that the user supplies when the package is created or exported. To open the package in SSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. Without the password the user cannot access or run the package.
	When used with the dtutil utility, this protection level corresponds to the value of 3.
Encrypt all with user key (EncryptAllWithUserKey)	Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility.
	When used with the dtutil utility, this protection level corresponds to the value of 4.
Encrypt sensitive with password (EncryptSensitiveWithPassword)	Uses a password to encrypt only the values of sensitive properties in the package. DPAPI is used for this encryption. Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution fails.
	When used with the dtutil utility, this protection level corresponds to the value of 2.
Encrypt sensitive with user key (EncryptSensitiveWithUserKey)	Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption.
	When used with the dtutil utility, this protection level corresponds to the value of 1.
Rely on server storage for encryption (ServerStorage)	Protects the whole package using SQL Server database roles. This option is supported only when a package is saved to the SQL Server msdb database. It is not supported when a package is saved to the file system from Business Intelligence Development Studio.

SSIS Carriage Return in MesageSource 'Send Mail Task'

Yesterday I faced a layout problem within the 'Send Mail Task' in BIDS. The task had to report status information in a mail in a readable manner. I composed the context of the mail in a variable wich is set bij an 'Execute SQL Task' in a 'Foreach Loop Container' which fills the variable by means of a Result Set.

Initially I composed the status information one after another and it became one big lumb of text which was not readable. So I had to insert 'Carriage Returns' in order to get new status information on a new line.

By adding CHAR(13) to a text in a SQL statement you can add 'Carriage Returns' to a text so you can make it more readable.

Example:
SELECT 'This is a example text.' + CHAR(13) + 'And this must be on a new line!' + CHAR(13) + 'And this must be on another new line!'

If you put this in a variable and put the content of this variable into the MessageSource property of the 'Send Mail Task' through a exception you will get the following result:

This is a example text.
And this must be on a new line!
And this must be on another new line!

Hope this helps!

SSIS datatypes VS SQL Server datatypes

Often it can be extremely confusing when you encounter SSIS data types.  At first glance they seem to be nothing like SQL Server data types.  That's why I wanted to write this blogpost,  below a conversion chart of SSIS data types to SQL Server data types.  This information is readily available on MSDN but it always seems difficult to find.  

SSIS Data Type	SSIS Expression	SQL Server
single-byte signed integer	(DT_I1)
two-byte signed integer	(DT_I2)	smallint
four-byte signed integer	(DT_I4)	int
eight-byte signed integer	(DT_I8)	bigint
single-byte unsigned integer	(DT_UI1)	tinyint
two-byte unsigned integer	(DT_UI2)
four-byte unsigned integer	(DT_UI4)
eight-byte unsigned integer	(DT_UI8)
float	(DT_R4)	real
double-precision float	(DT_R8)	float
string	(DT_STR, «length», «code_page»)	char, varchar
Unicode text stream	(DT_WSTR, «length»)	nchar, nvarchar, sql_variant, xml
date	(DT_DATE)	date
Boolean	(DT_BOOL)	bit
numeric	(DT_NUMERIC, «precision», «scale»)	decimal, numeric
decimal	(DT_DECIMAL, «scale»)	decimal
currency	(DT_CY)	smallmoney, money
unique identifier	(DT_GUID)	uniqueidentifier
byte stream	(DT_BYTES, «length»)	binary, varbinary, timestamp
database date	(DT_DBDATE)	date
database time	(DT_DBTIME)
database time with precision	(DT_DBTIME2, «scale»)	time(p)
database timestamp	(DT_DBTIMESTAMP)	datetime, smalldatetime
database timestamp with precision	(DT_DBTIMESTAMP2, «scale»)	datetime2
database timestamp with timezone	(DT_DBTIMESTAMPOFFSET, «scale»)	datetimeoffset(p)
file timestamp	(DT_FILETIME)
image	(DT_IMAGE)	image
text stream	(DT_TEXT, «code_page»)	text
Unicode string	(DT_NTEXT)	ntext

Hope this helps!