Darren Gosbell has published his presentation and a sample project with demos he presented at the SQL Down Under Code Camp.

This really is worth downloading and reading!

And Darren had a very well-fitting picture about the feelings while building the first SSIS package while coming from DTS for example:

I recently had to download some flatfiles from different FTP Resources to load these into a SQL database.

Being a fan of package configurations and minimized maintenance I decided to build a package which would loop through a recordset containing the necessary informations like FTP server, port, user and password instead of creating different packages or steps for each FTP server.

excursion:
I know that storing passwords in a database table is not really best practice for security, but the guys being responsible for the SQL Server are also responsible for the FTP server...

So, extracting the informations with a SQL Task, including a loop and using expressions to set the correct properties would not be a great challenge I thought. Up to the moment I tried to select the property for the password from the Property Expressions Editor in BIDS:

There is no property which could be used to set the password via expression - this must be a security feature to enable FTP tasks to be "secure by default"...

So what do to do?
Searching the properties for the password prop I recognized that there is a property for the connection - though I should use this for the loop approach instead of building several tasks? But what would be the correct usage of the connection?

Once again like every day in the life of an IT guy I used the "tool which must not be named" [@Microsoft: why don't you find search results even in your own resources???] and et voilà I got the perfect hint in the SSIS forum of MSDN.

The correct syntax for the connection would be Server IP:Port.loginAccount.loginPWD, therefor you could use an expression like this:

@[user::FtpServer] + ":" + @[user::FtpServerPort] +"."+ @[user::FtpAcct] +"."+ @[user::FtpPwd]

to build this connection string. And if you use another variable to build the string - do not forget to set the "evaluate as expression" to TRUE...

Phil Brammer has posted a very nice and simple quick tutorial on using a script component as a source on his blog:

http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/#more-42

Really worth reading!

Today at the BI Conference in Seattle Jeff Raikes from Microsoft has given a first official impact - just slides - on the next SQL Server version.

I have seen a new feature called Change Data Capture (also mentioned in the datasheet) in the session "PL208 Data Warehousing with Microsoft SQL Server 2005" presented by Gopal Ashok, Program Manager with the SQL Server Product team, which was quite impressive.

Ever dealt with the problem of having mass data in a table and no "real" marker to identify the specific rows for incremental data operations like inserts, updates and deletes?
The Change Data Capture should ease your pain by creating a kind of audit table to watch data modification without the need of programming stored procedures, triggers and so on.
I am extremely curious how this feature proves in real life scenarios. And I am also a little bit surprised if the SSIS team will not bring up some new tasks to enable the use of the audit table of the Change Data Capture - or do we still need to develop the handling concerning load cycles, deleting of successfully loaded records, etc by our own? A kind of automatic delta queue mechanism and handling like in SAP systems would be great!

Tomorrow Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft will have a keynote at the morning - I hope that he will show some more live impressions.

You will find the official press release here.

And there also is a information website online.

Regarding to the press release, "SQL Server “Katmai” is scheduled to be delivered in 2008" - but there is no information about CTP´s available on the website yet.

Vera Noest provides a really great link collection of ressources which will be very usefull while doing troubleshoting.

Vera also provides troubleshooting tips for Terminal Server and Citrix.

Worth reading - check it out!

Interested in all the new things in SP2? Here is a complete list.

And this is the complete download site with links for the SP2 itself, a new BOL, new Samples, new Feature Pack and especially the links to SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies and SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007.

To avoid a restart after the installation of the SP2 you should stop the neccessary services which could be done with the following commands on the command line - if you have used the standard instances:

net stop MSSQLSERVER
net stop MSSQLServerOLAPService
net stop msftesql
net stop MsDtsServer
net stop ReportServer

After the installation the "SQL Server 2005 User Provisioning Tool is started automatically if you use Windows Vista. As you probably know, Windows Admins are not automatically also SQL or AS Admins  - this tool enables to set the right privileges to the administartor accounts.

But do not forget to start the services before you try to set the privileges...

If you want to add a new account you will have to start the Provisioning Tool again, but unluckily it is not added to the Start Menu, you will find it at:
C:\Program Files\Microsoft SQL Server\90\Shared\SqlProv.exe - assumed that you have a standard installation and an english version of MS Windows Vista.

Interested in online ressources for SQL Server Training?
Please do not forget that these ressources can not replace a complete Training and therefor only should be considered as a additional point of information...

SQL Server 2005

After offering free delta courses Microsoft now offers a whole bunch of free E-Learning courses, which will enable you to pass the Exam 70-445 for the Microsoft Certified Technology Specialist (MCTS) SQL Server 2005 Business Intelligence.

You will find the complete Course Catalog at www.microsoftelearning.com

There are also some other resources for free at the moment at a subsite from Microsoft SQL Server 2005 – Learning Portal they called Dig Deep. Especially the free e-book Microsoft SQL Server 2005 Administrator's Pocket Consultant is a very useful resource and worth reading.

Sharepoint Technology

There is another bunch of free E-Learning courses for the Sharepoint Technology, which can be found here.

Visual Studio 2005 and .Net 3.0

And as expected developers are not left alone and there are also free E-Learning courses and a Dig Deep available for Visual Studio 2005 and .Net 3.0.

You need to have to do some manual prerequisites to convince you Reporting Services installation to accept SSIS packages as a source as described in "Defining Report Datasets for Package Data from SQL Server Integration Services".

Russell Christopher brings up some additional thoughts in his article "Using a parameterized SSIS package as a data source for SQL Reporting Services" and therefor lifts this datasource to a higher level with more value.

Thanks Russell!

The "Microsoft Architect Insight Conference" was held on 22 - 23 March 2006 and the slides are now available for download.

I would highly recommend that you read the PPT about Service-oriented Business Intelligence (SoBI) by Sean Gordon, Robert Grigg, Michael Horne, and Simon Thurman, which brings up some really good new approaches.
The authors also have published an article, which contains a more detailed descriptions and is available online.

And here are two additional links extracted from the article page:

• Data on the Outside vs. Data on the Inside
• Information as a Service: Service-Oriented Information Integration

You also should have an eye for the slides of Eric Nelson, who tries to explain that there are seven new servers in the SQL world...

You surely know "Project REAL", the SQL Server 2005 reference implementation at Barnes & Noble. It contains information on all upcoming topics in a BI project.

And now...
your are not only able to read about it - you are able to download a whole subset of the project REAL data and the various parts of the implementation

Extract from the website:
"The kit contains:

1. A set of instructions for setting up the environment
2. Guidance on how to explore the implementation
3. A sample relational data warehouse database (a subset of the Project REAL data warehouse)
4. A sample source database (from which we pull incremental updates)
5. SSIS packages that implement the ETL operations
6. An SSAS cube definition and scripts for processing the cube from the sample warehouse
7. Sample SSRS reports
8. Sample data mining models for predicting out-of-stock conditions in stores
9. Sample client views in briefing books for the Proclarity and Panorama BI front-end tools

This kit will guide you through the key points to observe in the Project REAL implementation. Use it to learn and to get ideas for your own implementation. (While we believe it represents a very good design and generally follows best practices, it should not be regarded as the solution for every BI situation.) It will be helpful to see the overview presentations about the BI tools in SQL Server, or read documentation, before exploring the kit. One good source of information is to go through the tutorials that ship with SQL Server (the tutorials are installed when you select “Workstation components, Books Online and development tools” at installation time)."

Chris Webb has blogged about the "Business Intelligence Architecture and Design Guide" downloadable at gotdotnet.

I agree with Chris, this is not really new stuff, but I personally think it is good to have informations like this in a kind of "complete package".

Extract from the website:
"The patterns & practices team is please to announce the availability of Architecture and Design Guidelines for Business Intelligence Applications. This exploratory community project provides prescriptive guidance on how to overcome architectural challenges and design issues when building Business Intelligence solutions using Microsoft platform. This guide is intended for software architects and developers who are developing Business Intelligence applications on the Microsoft .NET Framework using SQL Server 2005 - Integration Services and Analysis Services. Being an exploratory community project - anticipate gaps and scope for improvement. For more information see: http://codegallery.gotdotnet.com/biguide."

PASS Germany is doing a BI BootCamp again!
[Even if it is an event in german language I am writting in english - please spread this news around in your blogs though interested community members have the chance to participate]

Better, bigger, more content, more networking, more speakers than 2005 - if even possible!
And also a suprising night session again - stay tuned!

If you want to register, please use this link.

Here is the eight-slide-presentation for download:
PASS BI-BootCamp2006_8-Slides.pdf (190,66 KB)

The SQL Server 2005 Service Pack 1 is available now, which updates your SQL Version to Build 9.00.2047.

• download english version
• download german version
• List of bugfixes
• List of new features

Microsoft also now has published the "SQL Server 2005 Express Edition with Advanced Services", which contains Reporting Services now, and the "Microsoft SQL Server 2005 Express Edition Toolkit", which contains the Business Intelligence Development Studio and a Software Development Kit!
SQL Server 2005 Express now really is worth a look and it is for free, you will be able to download it here:
http://msdn.microsoft.com/vstudio/express/sql/download/

This video highlights two mission-critical applications within Microsoft that take advantage of SQL Server 2005: MS Sales, a worldwide revenue reporting system, and SAP R/3, an enterprise resource planning system.

http://www.microsoft.com/downloads/details.aspx?FamilyID=2ef438a4-4ae3-4d7b-bfe5-037c954f72c0&DisplayLang=en

Discussion on how Microsoft IT used SQL Server 2005 to deliver Microsoft Report Analytics, a robust, ad hoc reporting application and MS Sales Business Intelligence, a comprehensive business intelligence solution.

http://www.microsoft.com/downloads/details.aspx?FamilyID=92fbac74-0ca3-4b47-98ca-85014ec5df26&DisplayLang=en

Project REAL is a cooperative effort between Microsoft and a number of technology partners in the business intelligence (BI) industry to build on actual customer scenarios to discover best practices for creating BI applications based on SQL Server 2005. The term REAL in Project REAL is an acronym for Reference implementation, End-to-end, At scale, and Lots of users.

This page gives tons of informations and really is worth reading carefully and staying for hours!

Microsoft offers free e-learning courses for SSAS, SSIS and SSRS as a kind of delta-courses to enable an update of your knowledge to SQL 2005.

Course 2942: New Features of Microsoft® SQL Server™ 2005 Analysis Services

Extract from the website:
"Description:
This course will enable business intelligence developers experienced in SQL Server 2000/7.0 and Analysis Services to upgrade their skills to SQL Server 2005. The course is broken into five modules.

• Exploring SQL Server™ 2005 Analysis Services Core Concepts and Architecture
• Designing and Deploying a Unified Dimensional Model
• Administering SQL Server™ 2005 Analysis Services
• Programming in SQL Server™ Analysis Services 2005
• Working with Data Mining Solutions in SQL Server™ 2005 Analysis Services

Objectives:
At the end of the course, students will be able to:

• Build a Cube in SQL Server 2005 Analysis Services
• Administer SQL Server 2005 Analysis Services
• Program in SQL Server Analysis Services 2005
• Work with Data Mining Solutions"

Course 2943: Updating Your Data ETL Skills to Microsoft® SQL Server™ 2005 Integration Services

Extract from the website:
"Description:
This course will enable business intelligence developers experienced in SQL Server 2000/7.0 and Analysis Services to upgrade their skills to SQL Server 2005. The course is broken into four modules.

• Exploring SQL Server™ 2005 Integration Services Architecture and Core Concepts
• Designing and Debugging SQL Server™ 2005 Integration Services Packages
• Rich Functionality in SQL Server™ 2005 Integration Services
• Extensibility

Objectives:
At the end of the course, students will be able to:

• Design and debug SSIS Packages
• Configure and deploy SSIS Packages "

Course 2944: Updating Your Reporting Skills to Microsoft® SQL Server™ 2005 Reporting Services

Extract from the website:
"Description:
This course will enable business intelligence developers experienced in SQL Server 2000/7.0 and Analysis Services to upgrade their skills to SQL Server 2005. The course is broken into three modules.

• Exploring the Features and Core Concepts of SQL Server™ 2005 Reporting Services
• Designing Reports Using Report Designer
• Deploying and Managing Reports
• Working with Ad Hoc Reports

Objectives:
At the end of the course, students will be able to:

• Design Reports Using Report Designer
• Deploy and Manage Reports
• Work with Ad Hoc Reports"

Sorry, German only...

Aus der Reihe "Steffen (unser deutscher SQL Evangelist) erklärt die Welt":

Ist BizTalk ein Workflow-System?

Und wer mehr darüber erfahren will, wann er welches MS Produkt für Daten- und Systemintegration einsetzen sollte, dem sei Steffens TechNet Webcast "Welches Microsoft-Integrationsprodukt wofür? - Teil 1 (Level 100)" ans Herz gelegt.

Zusätzlich dazu gibt es ein englisches Whitepaper unter diesem Link Understanding Microsoft Integration Technologies - A Guide to Choosing a Solution.

There is a report pack available for download which provides log reports for SSIS - very interesting!

According to the website these reports are included:

• ErrorRows.rdl
  This report is based on SSIS Dataflow components which where configured to redirect error rows to a central DB It allows you to see a count and the details error rows by ExecutionGUID or Package GUID
• EventLogAnalysis.rdl
  This report has several sub reports including a graph of executions per package.
• EventLogSummary.rdl
  This is a sum by server of executions on that server and per package with result count (failed, success) and average time..
• OnPipelineRowsSent.rdl
  This report is based in the specific SSIS Dataflow task events of OnPipelineRowsSent which show rows processed on a per SSIS buffer basis. The sum of the numbers for any particular data path will equal the number you see processed in the Business Intelligence Development Studio. The assumption is users have configured their packages to log the OnPipelineRowsSent log entry
• PreandPostExecute.rdl
  This Report will show a summary report of pre and post execute events for packages. Can be used as a running status. .e.g refresh to see pre/post pairs increment. The assumption is users have configured their packages to log the pre and post execute log entries.

There are some new SQL Server SSIS Sample Components available for download at the Microsoft Download Center:

CalendarTransform
"is an SSIS dataflow transform component that generates standard calendar attributes based on a DT_DBTIMESTAMP or DT_DATE input column and a user-defined set of output columns.
- Multiple calendar types (Roman, Fiscal, Reporting, Manufacturing, ISO8601)
- Numerous attributes per calendar
- Custom-formatted output values"

CodePageConvert
"is an SSIS dataflow component that translates from and to any code page or unicode character representations."

ConfigureUnDouble
"is an SSIS dataflow component that:
- Allows user to configure the component with a chosen quote character.
- Removes any such quotes that bracket input strings if present.
- Replaces doubled quote characters with single instances inside the string."

NullDetector
"was built to provide an introduction to the use of the "virtual buffer". Also included are:
- ProvideComponentProperties and the second output.
- Using DirectRow in ProcessInput.
- PostWarning."

Regex
"is an SSIS dataflow component that applies a configured regular expression against an incoming column.
- Rows with no match can be routed to the error output, fail the data flow, or be ignored.
- The matched string, as well as any matching substrings, are placed in new dataflow columns."

RTrimPlus
"takes a string or unicode column, and removes trailing spaces, whether ASCII, or Japanese."

SeeBuffer
"is useless for business. It does offer some pedagogical features. I use it to introduce the ProcessInput method, the runtime interface, and the buffer."
---^^^^^... useless... ok...

UnDouble
"is an SSIS dataflow component that:
- Removes quotes that bracket input strings if present.
- Replaces doubled quotes with single quotes inside the string."

UnDoubleOut
"is an SSIS dataflow component that:
- Removes quotes that bracket input strings if present.
- Replaces doubled quotes with single quotes inside the string."

UnpackDecimal
"takes a bytes column, and converts to decimal, using a user provided scale."

There is a "Hands On Training for SSIS" available for download on the MS sites.

Extract form the website:
"Contains a Microsoft Word lab Document, sample files to process, and a sample DB, all of which support a series of exercises which guide a reader/user through step by step creating SSIS (SQL Server Integration Services) packages. Lab document includes commentary and by completion of the exercises a user will have a good understanding of what can be accomplished with SSIS as well as how to create some simple SSIS packages."

Oldies but Goldies - I promised to share the whole knowledge stuff from my mail folder though I came across these articles today:

Updating SQL Server Connection Properties from the DTS Package INI File

SQL Server Data Transformation Services (DTS) Best Practices 

I think it is worth reading for beginners who face migration from DTS to SSIS or a mixed landscape with SQL 2000 and SQL 2005 and want to understand the "old concepts" too...

I came across a MySQL database during a project last days and had to migrate it to a MS SQL Server database.

I could have printed the ER-diagram and rebuild the tables, but... I have discovered a real cool and cheap tool to do this!
Take a look at the MySQL-to-MSSQL migration tool from intelligent converters.

My tip is to convert just the metadata and do the data transformation using DTS or SSRS, this is much faster.

They also provide other converters worth a look.

I just came across this post from Bayer White who is a co-blogger of Brian Knight - they are sharing this Community Server.

There is a website available called msbicentral.com, regarding to the post, this should be a "pilot community and the "the "GOTO", place for Business Intelligence knowledge".

Extraxt from the website:
"Wayne, Brian and I spoke about what the future holds for MSBICentral and came to the conclusion that there were not enough BI Communities. We decided we should do more and make sure that are content is up to date and give examples of BI techniques for 2005. Please hold me accountable to this!"

I think we should do this...

Extract from msbicentral.com:
"MSBICentral.com is a community site that helps users like yourself learn to develop, use and administer Business Intelligence (BI) solutions using Microsoft products like SQL Server. On this site, you'll find hundreds of articles, scripts and tutorials to help you learn or perfect your BI skills. If you have a question or if you're a business intelligence newbie, look on the left bar for some quick links to get you started.
Please note: You'll need to be logged in with your free account to see articles and use much of the site."

And be ashured, registering is really worth investing the two minutes to fill out the form!

As Chris Webb posted here, there is a "SQL Server 2005 Business Intelligence Metadata Samples Toolkit", which brings up "Tools and Sample reports for Metadata Dependency Analysis of SSIS, SSAS", available for download for free.

Extract from the website:
"Microsoft SQL Server 2005 BI Metadata Samples provides tools and samples for analysing dependencies within and across SSIS and SSAS. The source code for the tools is provided.
The following are the components of this pack:
DependencyAnalyzer: a utility that scans SSIS packages and SSAS databases for enumerating metadata (objects, properties and dependencies).
DependencyViewer: a utility to view metadata organization & inter-dependencies (lineage/impact analysis).
Analysis Services Samples : sample SSAS packages that can be analyzed
Integration Services Samples: sample SSIS packages that can be analyzed
Report Model Samples (Report Builder): report builder model
Reports: sample reports built from metadata store
SQL: SQL script for creating metadata store"

Extract from the website:
"This white paper covers several interesting and unique methods for managing metadata in SQL Server Integration Services, Analysis Services and Reporting Services using built-in features including data lineage, business and technical metadata and impact analysis."

Download an updated version of the documentation and tutorials for Microsoft SQL Server 2005.

Extract from the website:
"Download an updated version of Books Online for Microsoft SQL Server 2005. Books Online is the primary documentation for SQL Server 2005. The December 2005 update to Books Online contains new material and fixes to documentation problems reported by customers after SQL Server 2005 was released. Refer to "New and Updated Books Online Topics" for a list of topics that are new or updated in this version. Topics with significant updates have a Change History table at the bottom of the topic that summarizes the changes.

Books Online includes the following types of information:

• Setup and upgrade instructions.
• Information about new features and backward compatibility.
• Conceptual descriptions of the technologies and features in SQL Server 2005.
• Procedural topics describing how to use the various features in SQL Server 2005.
• Tutorials that guide you through common tasks.
• Reference documentation for the graphical tools, command prompt utilities, programming languages, and application programming interfaces (APIs) that are supported by SQL Server 2005.
• Descriptions of the sample databases and applications included with SQL Server 2005."

Download over 100 samples and three sample databases for SQL Server 2005.

Extract from the website:
"The samples download provides over 100 samples for SQL Server 2005, demonstrating the following components:

• Database Engine, including administration, data access, Full-Text Search, Common Language Runtime (CLR) integration, Server Management Objects (SMO), Service Broker, and XML
• Analysis Services
• Integration Services
• Notification Services
• Reporting Services
• Replication

The samples databases downloads include the AdventureWorks sample online transaction processing (OLTP) database, the AdventureWorksDW sample data warehouse, and the AdventureWorksAS sample Analysis Services database. These databases are used in the samples and in the code examples in the SQL Server 2005 Books Online."

Microsoft offers a document named "SSIS Creating a Custom Transformation Component.doc" which provides  "Step by Step Instructions for Creating a sample Custom Component" based on a hands on training lab.

SQL Server Integration Services (SSIS) Hands on Training - Creating Custom Components:
http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en

And this is another ressource from Project Real:
SQL Server 2005 Integration Services, Part 2: Developing Custom Components for Project REAL
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/sqlpjtreal2.asp

http://www.ftponline.com/archives/premier/mgznarch/vbpj/2001/09sep01/jf0109/jf0109-1.asp

http://vyaskn.tripod.com/code.htm#inserts

http://www.insidesql.de/content/view/200/

http://www.sqlscripter.com/

http://www.databasejournal.com/features/mssql/article.php/3404001

http://www.sqldts.com/default.aspx?t=6&s=105&i=204&p=1&a=0
http://www.sqldts.com/redir.aspx?1209

http://www.sqldts.com/default.aspx?t=6&s=105&i=242&p=1&a=0

http://www.sqldts.com/default.aspx?202