SQL Server Q&A

Symptoms
You can create a Microsoft SQL Server 2005 Integration Services (SSIS) packageby using SQL Server Business Intelligence Development Studio. When you create this package, you can create an expression for a property of the SSIS packageto update or to populate the property at runtime. For example, if the SSIS package contains a Send Mail task, you can create an expression for the Subject property and for the MessageSource property. You can use the Subject property expression to dynamically update the subject of an e-mail message. You can use the MessageSource property expression to dynamically update variables in the e-mail message, such as variables that are populated by a Row Count transformation.
This article discusses how to create a dynamic subject or message for the Send Mail task.
Resolution
The following is a sample property expression for the Subject property in a Send Mail task.

“Package>>> ” + @[System::PackageName] +” was executed at>>> ” + (DT_WSTR, 40) @[System::StartTime] + ” by user>>> ” + @[System::UserName] + ” on Machine>>> ” + @[System::MachineName]If you use this sample property expression, the subject of an e-mail message is updated dynamically. The subject will include the following information: Text information
In this example, the e-mail messagesubject includesthe “Package>>> ” text information.System variables
The e-mail message includes the following system variables:PackageName
The package name.StartTime
The time that the package was executed. UserName
The user who executed the package.MachineName
The name of the computer on whichthe package was executed. You can also include more information in the expression, such as a user-defined variable. For example,a Data Flow task can include a Row Count transformation before the Send Mail task. (The Row Count transformation is used to count rows.) The Row Count transformation populates a user-defined variable that is named @myrowcount. This variable stores the count information in the data flow.
To specify that an e-mail message be sent only if the row count is smaller than a certain value, modify the control flow by using precedence constraints. To do this, follow these steps: In SQL Server Business Intelligence Development Studio, right-clickData Flow Task,and then click Add Precedence Constraint.Double-click the precedence constraint that you just created. In the Precedence Constraint Editor dialog box, click Expression and Constraint in Evaluation operation.In the Expression box, type the following expression:
@myrowcount < 2 In the Precedence Constraint Editor dialog box, click OK.If less than two rows are processed in the data flow, an e-mail message is sent.
Additionally, you can use the Send Mail task as part of an error handler. For example, you may want to send an e-mail message to administrators when an SSIS package does not execute. To do this, create an OnError event handler for the package, and then add a Send Mail task to the event handler. Create a subject property expression that captures the time that the package is executed, the start time of the container, or the start time of the event handler from the relevant system variables. For example, create an expression that is similar to the following.

“Error in the task: ” +@[System::SourceName] + “with the ID: ” +@[System::SourceID] + ” has failed at: ” + (DT_WSTR, 20) @[System::ContainerStartTime] + “.”This sample expression uses the following system variables: StartTime
The time when the package was executed.ContainerStartTime
The time that the container started.EventHandlerStartTime
The time that the event handler started.

Symptoms
Consider the following scenario. In Microsoft SQL Server 2005 Integration Services (SSIS), you create an SSIS package. The SSIS package contains some Script tasks or Script components. When the file size of the SSIS package increases to more than 7 megabytes (MB), you receive the following error message when you try to save the SSIS package in SQL Server Business Intelligence Development Studio:

TITLE: Microsoft Visual Studio
Failure saving package.
ADDITIONAL INFORMATION:
Exception of type ‘System.OutOfMemoryException’ was thrown. (Microsoft.SqlServer.ManagedDTS)
BUTTONS:
OK
Resolution
This problem occurs because heap memory is fragmented in the SQL Server Business Intelligence Development Studio process.

Symptoms
This article describes how to create a shared package configuration file so that you can make configuration changes easily in Microsoft SQL Server 2008 Integration Services (SSIS).
Resolution
Description of a shared package configuration fileWhen you deploy sets of related SSIS packages, it is more efficient to reconfigure these packages by including shared settings. For example, a connection string might be shared across multiple packages to point to a frequently used database server. The connection string may have to be changed when the system is deployed from a test environment to a production environment or in other scenarios.
You can apply new settings to a package at runtime by using package configurations. Package configurations are created by using the Package Configurations tool in Business Intelligence Development Studio (BI Development Studio). By using the Package Configuration Organizer dialog box and the Package Configuration Wizard under Package Configurations, you can configure how XML and database configurations are applied to a package before it runs. Additionally, you can edit these configurations when the system is migrated. This mechanism is designed for use with individual packages. However, you can share a single package configuration file across multiple packages. This enables you to change package configurations in one place.
How to create a shared package configuration fileBefore you create a shared package configuration file, you must first uniformly design the set of packages. You must name every object consistently in the set of related packages that have to be configured together. For example, if you want the shared package configuration file to configure the ConnectionString property of a connection manager that is named “ReportsDB” in one package, every other package that shares this configuration file must also contain a ‘ReportsDB’ connection manager. If packages do not contain objects that are specified in the shared package configuration file, the packages cannot use the shared package configuration file. After you have verified that the packages contain consistent object names, you can create the shared package configuration file. To do this, follow these steps:In BI Development Studio, open the first package in Solution Explorer.In SSIS Designer, click the Package Explorer tab, and then on the SSIS menu, click Package Configurations.In the Package Configuration Organizer dialog box, click Enable package configurations, and then click Add.On the Welcome page of the Package Configuration Wizard, click Next.On the Select Configuration Type page, specify the configuration type, and then set the properties that are relevant to the configuration type.On the Select Properties to Export page, select only the properties that you want to configure across multiple packages.
Note If the configuration type supports only one property, the title of this wizard page is Select Target Property.On the Completing the Wizard page, type the name of the shared package configuration file, and then click Finish.Test this first package to make sure that it works correctly.Open each additional package in BI Development Studio, and then in the Package Configuration Organizer dialog box, enable package configurations.Add the file that you created in step 7. When you are prompted to reuse or overwrite the existing file, click Reuse Existing.

Symptoms
Consider the following scenario:On a computer that is running Windows 7, you design a Reporting Services report in Microsoft SQL Server 2008 Business Intelligence Development Studio (BIDS).The report contains a Chart control.In BIDS, you try to resize the Chart control in the Report Designer.In this scenario, you may receive the following error message:

Chart Exception: Parameter is not valid.
Resolution
The fix for this issue was first released in Cumulative Update4 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
973602?(http://support.microsoft.com/kb/973602/LN/) Cumulative update package 4 for SQL Server 2008 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
970365?(http://support.microsoft.com/kb/970365/LN/) The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was releasedMicrosoft SQL Server 2008hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Symptoms
You create or edit a Microsoft SQL Server 2008 Integration Services (SSIS) package in Business Intelligence Development Studio (BIDS). You edit an OLE DB source in a Data Flow task. When you click the Columns tab, you receive the following error message:

TITLE: Microsoft Visual Studio
——————————
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR.An OLE DB error has occurred. Error code: 0×80040E21.
——————————
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)Note The DTS_E_OLEDBERROR error code has many causes. This article describes only one rare problem that has a specific cause. To make sure that you are encountering this problem, see the “More Information” section for a troubleshooting method.
Note This problem does not occur in SQL Server 2005.
Resolution
In SQL Server 2008 SSIS, a specific check determines whether the identifiers that are used by an OLE DB provider are case-sensitive. This check allows for SSIS to follow the case-sensitivity that is defined by an OLE DB provider configuration when SSIS lists object names, table names, and column names. This check is performed by implementing an OLE DB property that is named DBPROP_IDENTIFIERCASE.However, not all OLE DB providers support and implement this property. If the property is not implemented, an error may occur in the BIDS.