« June 2007 | Main | October 2007 »
August 15, 2007
SSIS - Package Configurations - Best Practices - Part #2 (Set-Up)
by Ray Barley, Principal Architect, RDA CorporationClick the Control Flow tab in the SSIS designer, right click the design surface, then select Package Configurations from the context menu. You will see the Package Configurations Organizer dialog.
Click Enable package configurations then Add. The Welcome to the package Configuration Wizard dialog appears; click Next.
The Select Configuration Type dialog appears. Select SQL Server as the Configuration type. Click Specify configuration settings directly, then fill in the Connection, Configuration table and Configuration filter. The Configuration table value shown below is the default; you can specify a different table name. If the table does not exist it will be created.
Click Next; you will now see the Select Properties to Export dialog. Check the properties that you want to specify in the configuration and retrieve at run time. Ideally you want to use this configuration information in multiple packages. Note that all properties that you select for export must exist in any package that uses this configuration; see Problem: Editing a package Configuration below for the trap to be aware of. It looks like the best practice here is to specify the properties for just a single object in a configuration. A package can use as many configurations as necessary.
Click the localhost.AdventureWorks ConnectionString property. The current value of the property will be written to the SQL Server database table. At runtime the value in the SQL Server database will be retrieved and used in the package. Click Next.
Enter the Configuration name then click Finish.
View the AdventureWorksDatabase configuration from SQL Server management Studio:
The above schema is straight forward. Note the PackagePath column. This must match a property in your package if you reuse the package configuration in another package.
Share:
Posted by Steve Mann at 8:44 PM | Comments (0)
August 11, 2007
SSIS - Package Configurations - Best Practices - Part #1 (Introduction)
INTRODUCTION
· Environment Variable
· Registry Entry
· Parent Package variable
· SQL Server
While any of the above configuration options can work, based on a number of successful BI projects the combination of environment variable and SQL Server is a good choice.
Let’s start with a discussion of the steps to setup your package configuration using the environment variable and SQL Server options. Here are the steps:
- Create a SQL Server database (i.e. the config database) and add a table where package configuration info will be stored. SSIS specifies the schema for this table.
- Add an environment variable where you will specify the connection string to the config database.
- Create an SSIS package template. This is an SSIS package that you stub out and use as your starting point in creating other SSIS packages.
- Add connection managers to the template package; e.g. the config database, a source database, and a destination database (assume a standard ETL-type package). The OLE DB Source works well as it can be used to connect to just about any database.
- Add package configurations to the template package. This is where you wire up the connection strings in each of your connection managers to the package configuration.
Here is a sample environment variable setting:
SSISConfigConnectionString= Data Source=localhost;Initial Catalog=SSISConfiguration;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
The schema of the table that holds the package configuration is as follows (defined by SSIS); you can name the table anything you want and you can put it in any database:
USE [SSISConfiguration]
GO
/****** Object: Table [dbo].[SSISConfiguration] Script Date: 04/09/2007 12:24:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSISConfiguration](
[ConfigurationFilter] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
This is a screen shot of a completed package configuration in an SSIS package:
Here are the main points of the package configuration:
· The first entry assigns the ConnectionString of the SSISConfiguration database to the value defined in an environment variable; this needs to be first; the package configurations that follow use this connection string to access their configuration values.
· The second entry assigns the ConnectionString of a connection manager to an entry in the SSISConfiguration table
· The third entry also assigns the ConnectionString of a connection manager to an entry in the SSISConfiguration table
The ConfiguredValue is the actual connection string; the PackagePath is the mechanism for assigning the ConfiguredValue to a particular object property in the SSIS package; e.g. the connection string property of a connection manager.
It’s a good practice to open the table in SQL Server Management Studio then copy/paste the data to an Excel spreadsheet that you can archive in source control.
Share:
Posted by Steve Mann at 5:49 PM | Comments (0)
Package Configurations - Best Practices - Part #1 (Introduction)
INTRODUCTION
· Environment Variable
· Registry Entry
· Parent Package variable
· SQL Server
While any of the above configuration options can work, based on a number of successful BI projects the combination of environment variable and SQL Server is a good choice.
Let’s start with a discussion of the steps to setup your package configuration using the environment variable and SQL Server options. Here are the steps:
- Create a SQL Server database (i.e. the config database) and add a table where package configuration info will be stored. SSIS specifies the schema for this table.
- Add an environment variable where you will specify the connection string to the config database.
- Create an SSIS package template. This is an SSIS package that you stub out and use as your starting point in creating other SSIS packages.
- Add connection managers to the template package; e.g. the config database, a source database, and a destination database (assume a standard ETL-type package). The OLE DB Source works well as it can be used to connect to just about any database.
- Add package configurations to the template package. This is where you wire up the connection strings in each of your connection managers to the package configuration.
Here is a sample environment variable setting:
SSISConfigConnectionString= Data Source=localhost;Initial Catalog=SSISConfiguration;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
The schema of the table that holds the package configuration is as follows (defined by SSIS); you can name the table anything you want and you can put it in any database:
USE [SSISConfiguration]
GO
/****** Object: Table [dbo].[SSISConfiguration] Script Date: 04/09/2007 12:24:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSISConfiguration](
[ConfigurationFilter] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
This is a screen shot of a completed package configuration in an SSIS package:
Here are the main points of the package configuration:
· The first entry assigns the ConnectionString of the SSISConfiguration database to the value defined in an environment variable; this needs to be first; the package configurations that follow use this connection string to access their configuration values.
· The second entry assigns the ConnectionString of a connection manager to an entry in the SSISConfiguration table
· The third entry also assigns the ConnectionString of a connection manager to an entry in the SSISConfiguration table
The ConfiguredValue is the actual connection string; the PackagePath is the mechanism for assigning the ConfiguredValue to a particular object property in the SSIS package; e.g. the connection string property of a connection manager.
It’s a good practice to open the table in SQL Server Management Studio then copy/paste the data to an Excel spreadsheet that you can archive in source control.
Share:
Posted by Steve Mann at 5:49 PM | Comments (0)
August 6, 2007
SQL Server 2008 - MERGE Statement
by Brian McCullough, Senior Software EngineerMERGE Overview
The MERGE syntax allows you to join a data source with a target table or view, and then perform multiple actions based on the results of that join. Therefore, you can perform INSERT, UPDATE, and DELETE operations within a single statement. The MERGE syntax consists of four primary clauses:
- The MERGE clause specifies the table or view that is the target of the INSERT, UPDATE, or DELETE operations.
- The USING clause specifies the data source being joined with the target.
- The ON clause specifies the join conditions to determine where the target and source match.
- The WHEN clauses specify the actions to take based on the results of the ON clause.
Basic MERGE Syntax
MERGE DestinationTable AS Dest
USING SourceTable AS Src
ON Dest.ID = Src.ID
WHEN MATCHED THEN
UPDATE SET Dest.Field1 = Src.Field1
, Dest.Field2 = Src.Field2
WHEN NOT MATCHED THEN
INSERT (Field1, Field2)
VALUES (Src.Field1, Src.Field2)
;
MERGE Usage Scenarios
- Populating a data warehouse
--- Fact Tables
--- Dimension Tables - Consolidate and/or condense your data manipulation logic in stored procedures (i.e. INSERT/UPDATE operations)
MERGE Tips
- Don’t forget your semi-colon (;) at the end of the statement!
- When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted.
- As with other T-SQL statements, appropriate use of indexes on fields in ON clause could affect performance.
- If possible, apply forced parameterization to the database when MERGE statements are executed frequently because compilation times are typically longer than those for individual INSERT, UPDATE, and DELETE statements (see BOL for information on forced parameterization).
Sample Code:
--In this example, we use our dimension table, DimEmployee,
--as our destination. The source table in this case references the Employee table.
--Matches are identified by a match of the Employee.EmployeeNumber and
--DimEmployee.EmployeeNumberAlternateKey fields.
MERGE DimEmployee AS DimEmp
USING Employee AS Emp
ON (DimEmp.EmployeeNumberAlternateKey = Emp.EmployeeNumber)
WHEN MATCHED THEN
UPDATE SET DimEmp.FirstName = Emp.FirstName
, DimEmp.LastName = Emp.LastName
, DimEmp.HireDate = Emp.HireDate
WHEN NOT MATCHED THEN
INSERT (EmployeeNumberAlternateKey, FirstName, LastName, HireDate, YtdPtoHours)
VALUES (Emp.EmployeeNumber, Emp.FirstName, Emp.LastName, Emp.HireDate, 0)
;
Share:
Posted by Steve Mann at 7:24 AM | Comments (0)
SQL Server 2008 - July CTP
Microsoft released the second CTP for SQL Server 2008 on July 31, 2007. Get the details and download here: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395
Share:
Posted by Steve Mann at 7:22 AM | Comments (0)
SQL Server 2008 Highlights
SQL Server 2008 is built around the following technology themes:
· Mission Critical Platform for All †SQL Server 2008 will provide a more secure, reliable and manageable enterprise data platform, including an innovative, policy-based declarative management framework allowing administrators to define policies to be enforced across all database servers.
· Redefining Pervasive Insight †SQL Server 2008 will provide a more scalable infrastructure for business intelligence and empower users to easily consume data via increased integration with Microsoft Office.
· Beyond Relational †SQL Server 2008 will enable developers and administrators to store and consume any type of data from XML to documents, including a new spatial data type for map-based applications and a new integrated transactional file system to allow storing large database objects directly in the file system.
· Dynamic Development for Data Management Solutions †SQL Server 2008 will provide an integrated development environment with Visual Studio and the Microsoft .NET Framework version 3.0, accelerating development with a higher level of data abstraction with a new entity-based framework.
The highlights of the initial CTP release include the following features:
· SQL Server Integration Services (SSIS) †the script task and script component now support development in C# and full debugging capabilities.
· SQL Server Analysis Services (SSAS) â€the user interface for creating and editing dimensions is enhanced to guide users toward designs that follow best practices.
· Star Join Query Optimization †improved query performance by recognizing common data warehousing join patterns.
· MERGE SQL Statement †added support for the MERGE SQL statement which is especially useful in data warehousing where data is inserted if it does not exist or updated if it does exist. MERGE is a single statement that handles both conditions.
· Change Data Capture †a generic component that tracks database changes asynchronously and exposes the changes through a relational interface. This is another big enhancement in the data warehousing area where data changes need to be captured and used to update dimensions.
· Table Value Parameters †provides an easier way to define a table type as well as allow applications to create, populate and pass table structured parameters to stored procedures and functions.
· Declarative Management Framework †a policy-based mechanism to ensure compliance with policies for system configuration and to prevent or monitor changes.
These new product features are all welcome additions to SQL Server. The Declarative Management Framework may well be the most compelling of the new features, reducing the total cost of ownership as it relates to enforcing consistent configuration and policies across all SQL Server database servers in the enterprise. The Declarative Management Framework is made up of three distinct elements:
· A Facet is a high-level category within SQL Server; it contains a list of properties.
· A Condition is a property expression that evaluates to True or False; it is associated with a particular Facet.
· A Policy is a list of Conditions that are evaluated.
The policy may operate in one of the following ways:
· Rollback a transaction that violates the policy; i.e. prevent a SQL statement from violating the policy
· Check on change - monitor transactions in real time and note any policy violations; i.e. allow a SQL statement to violate a policy but log the fact that the policy is violated
· Check on schedule †check for policy violations on a periodic schedule
In the next section, we will look at an example of how it works.
Declarative Management Framework †Example
In order to walk through an example of the Declarative Management Framework, assume that an organization wants to enforce a policy whereby all databases will have the AutoShrink property set to False. If the AutoShrink database property is set to True, then the SQL Server database engine will reduce the size of any data files that have excess space in them. Many organizations do not want this function to be performed automatically; rather they will analyze the database file sizes and shrink the files manually.
The screen shot below shows where the Declarative Management policies are defined within the SQL Server Management Studio (SSMS). Expand the Management node; expand the Policy Management node. At this point you will see the Policies, Conditions, and Facets group nodes.
Notes on specifying the Condition:
· You can specify multiple And/Or Expressions in a Condition.
· The Field column displays a drop down list of the available fields based on the Facet.
· The Operator column displays a drop down list of choices: =, !=, LIKE, IN, NOT LIKE, NOT IN
· You enter the Value column.
Right click Policies, select New Policy from the context menu, then fill in the policy which references the Condition:
By default the policy will apply to every database on the server. You can click Edit Filter … to specify And/Or Expression filter(s) to limit the policy to particular databases on the server. You can Export the policy and Import it to other servers.
To test this policy, right click on the policy then select Test Policy from the context menu. The following dialog is displayed showing the databases on the server and whether they are in compliance with the policy:
Reference
For the latest information on SQL Server 2008 including download instructions, white papers, webcasts and sample code, visit the following URL:
https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395
Share:
Posted by Steve Mann at 7:13 AM | Comments (0)
