May 2011

You are currently browsing the monthly archive for May 2011.

In this post I’m actually just going to repeat what I answered to a question on Experts Exchange.

The OP was having an issue getting a record count of all tables in his database.  It was working through the Management Studio, using the undocumented but well-known sp_MSforeachtable stored procedure.

exec sp_MSforeachtable 'select ''?'' TableName, count(*) Cnt from ?';

However, the OP needed the list of record counts inside an Integration Services package.  And this didn’t work out as expected, because sp_MSforeachtable uses a temporary table, something that SSIS does not like.

So I proposed to use a script similar to the following, possibly in a stored procedure.

/*
DESCRIPTION: Returns a list of record counts for each table in the database
AUTHOR:         Valentino Vranken
CREATED:     2011/05/21
VERSION:     1.0
COPIED FROM: http://blog.hoegaerden.be
*/
declare @tempTable table
(
    TableSchema nvarchar(256),
    TableName nvarchar(256),
    Cnt bigint
);

declare @sql nvarchar(4000);
declare @tableSchema nvarchar(256);
declare @tableName nvarchar(256);
declare @columnName sysname;
declare @cnt bigint;

declare tableCursor cursor for
    select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE = 'BASE TABLE';

open tableCursor;

fetch next from tableCursor into @tableSchema, @tableName;

while @@FETCH_STATUS = 0
begin
    set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName + ']';

    exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

    insert into @tempTable select @tableSchema, @tableName, @cnt;

    fetch next from tableCursor into @tableSchema, @tableName;
end;

close tableCursor;
deallocate tableCursor;

select * from @tempTable;

Yeah, I know, cursors are bad… But hey, at least it works!  Just don’t schedule it for execution every five seconds.

I seem to be repeating myself.  I wrote something similar when I posted the script about finding empty columns many months ago.

The most important part of the script is the following:

select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE';

This is using one of the information schema views to retrieve a list of tables in the database.  The INFORMATION_SCHEMA.TABLES view contains a list of tables and views, so the where clause is needed to filter out the views.  INFORMATION_SCHEMA.TABLES is just one of many interesting views available in SQL Server, and should always be used in favor of the sys tables.

What does the output look like?  Here’s part of the output when executed on the AdventureWorks2008R2 database:

Record count of all tables in the AdventureWorks2008R2 database (although not all are shown, you get the idea)

Have fun!

Valentino.

Share

Tags: , ,

Haven’t got any plans next Thursday evening (May 19th, 2011)?  Then I’ve got an interesting SQL Server User Group event for you!

Session Content

The next version of Analysis Services will offer the BI Semantic Model (BISM) that is based on Vertipaq, the same engine that runs PowerPivot. DAX and PowerPivot have been created as tools for Excel users but in Denali they will be available in Microsoft’s Corporate BI technology stack, as part of Analysis Services.

The impact of this technology is huge, because many assumptions that are made today regarding OLAP cubes (star schema models, surrogate keys and so on) might be no longer applicable in terms of “the optimized way” to design a BI solution.

This session is about this impelling change: after an initial introduction about PowerPivot, DAX and Vertipaq changes that are relevant to this topic and some consideration about design impact on Data Warehouse, Data Mart and ETL pipeline, the session will become an open discussion with all attendees, in order to share experience, needs, technical challenges and understand future directions in the corporate BI world.

The Speakers

Marco Russo

Marco Russo is a Business Intelligence consultant and mentor.

His main activities are related to data warehouse relational and multidimensional design, but he is also involved in the complete development life cycle of BI solutions. He has particular competence and experience in sectors like financial services (such as complex OLAP designs in banking area), manufacturing and commercial distribution.

Marco is also a book author and, apart from his BI-related publications, he even wrote some books about .NET programming. He is also a speaker at international conferences like the European PASS Conference and PASS Summit. He is an MCT and has several certifications (MCPD, MCIP, MCTS, MCAD and MCDBA).

Alberto Ferrari

Alberto Ferrari is a Business Intelligence consultant.

He his interests lie in two main areas: BI development life cycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors.

He is also a speaker in international conferences like the European PASS Conference and PASS Summit.

Registration

More info and registration on the SQLUG website!

PS: although I’m sure I would really have enjoyed this session, unfortunately I can’t make it there, gotta stay with the kids… :-(

Those that are able to go: do so, and have fun!

Valentino.

Share

Tags: ,

The following is based on recent experience when I needed to call a web service to send out emails from Integration Services 2008 R2.

You probably already know that there are two components in SSIS that allow you to write custom .NET code.  In the Control Flow we’ve got the Script Task, while the Data Flow offers us the Script Component.

In my case I wanted to encapsulate the code that uses the external web service into a custom .NET DLL, aka assembly.  The code to send out emails is needed in several SSIS packages, so to be able to handle changes easily and to promote reuse, this really needed to get encapsulated into one library.

Furthermore, the library is configuring the binding with the web service completely through code so that no app.config file is needed.  The assembly is developed using Visual Studio 2008 and needs the 3.5 .NET Framework.

Assuming that the assembly is already developed and fully tested, open up a package in the BIDS and add a Script Task to the Control Flow.  Open up the Visual Studio Tools for Applications 2.0 (aka VSTA) development environment by clicking the Edit Script… button in the Script Task Editor.

Right-click the References node in the Project Explorer and select Add Reference….

Adding a reference to the Script Task

In the Add Reference popup window, select the Browse tab and select your custom assembly.  After clicking OK, you’re getting the following warning:

Warning when adding a custom assembly to a Script Task in Integration Services

It says that your assembly, or one of its dependencies, requires a later version of the .NET Framework than the one specified in the project.  Click the No button so that we can first set the target framework to the expected version.

To do that, right-click the project node in the Project Explorer and select Properties.  In the Application page, you can see that Target Framework is set to .NET Framework 2.0.

By default, the Target Framework for the Script Task in SSIS 2008 is set to .NET Framework 2.0

Apparently by default the Script Task in Integration Services 2008 R2 still targets the 2.0 Framework.  Change that setting to .NET Framework 3.5.  You’ll get asked if it’s okay to close and reopen the current project, which is needed to change the Target Framework.  That’s okay, so click the Yes button.

Target Framework Change: requires a close/reopen of the current project

With this setting modified you can now add the reference to your custom assembly and start using it in the code.

Tip: in case that the reference to your assembly keeps disappearing every time you reopen the Script Task’s code, click the Save All button – that’s the one that looks like a bunch of blue floppies – in the toolbar after you’ve added the reference.  Or hit CTRL + SHIFT + S.

Besides the scenario described above, a second reason when you may want (or better, have) to change the target framework version for your Script Task or Component is when you actually need to use functionality that doesn’t exist yet in 2.0.  New functionality in .NET 3.5, such as Linq, can only be used if the target framework is switched to 3.5.

That’s it for now, have fun!

Valentino.

References

Where is my app.config for SSIS? by Darren Green

Share

Tags: ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved