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:

This weblog is sponsored by FITS.
We support PASS Germany!

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...

This weblog is sponsored by FITS.
We support PASS Germany!

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!

This weblog is sponsored by FITS.
We support PASS Germany!

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.

This weblog is sponsored by FITS.
We support PASS Germany!

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!

This weblog is sponsored by FITS.
We support PASS Germany!

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.

This weblog is sponsored by FITS.
We support PASS Germany!

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...

This weblog is sponsored by FITS.
We support PASS Germany!

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)."

This weblog is sponsored by FITS.
We support PASS Germany!

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."

This weblog is sponsored by FITS.
We support PASS Germany!

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)

This weblog is sponsored by FITS.
We support PASS Germany!

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 weblog is sponsored by FITS.
We support PASS Germany!

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!

This weblog is sponsored by FITS.
We support PASS Germany!

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"

This weblog is sponsored by FITS.
We support PASS Germany!

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."

This weblog is sponsored by FITS.
We support PASS Germany!

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."

This weblog is sponsored by FITS.
We support PASS Germany!

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

This weblog is sponsored by FITS.
We support PASS Germany!