Data Consumer Issues

This topic provides information to troubleshoot data consumer issues.

SQL Server Integration Services

Enterprise Single Sign-On

When using Enterprise Single Sign-On with SQL Server Integration Services, you may need to enter a placeholder value of “MS$SAME” for user name and password. Using Data Links, you can configure ESSO for use with SSIS.

1. In the Connection dialog, click Single sign-on for the Security method.

2. Select an Affiliate application from the drop-down list box.

3. In the All dialog, click Password and then click Edit Value. In the Edit Property Value dialog, enter MS$SAME for the Property Value, and click OK.

4. In the All dialog, click User ID and then click Edit Value. In the Edit Property Value dialog, enter MS$SAME for the Property Value, and click OK.

5. In the Connection dialog, click Test. You can view the results in the Microsoft Data Links dialog.

6. Click OK to save the configuration information.

Data Type Mapping

When using the SQL Server Integration Services Import and Export Wizards, from the Microsoft SQL Server Management Studio or Business Intelligence Design Studio, you can customize the default data conversions by editing XML mapping files. The Import and Export Wizard XML mapping files are located in the following folder.

C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles

To correctly map IBM DB2 for i5/OS character and decimal data types to SQL Server data types, the data mapping files should be extended to include the DB2 data type long form synonym. For example, add the following data type mapping between DB2 INTEGER source and SQL Server. This mapping is compatible with SQLOLEDB, SQLNCL, SQLNCLI10, and System.Data.SqlClient.SqlConnection. It replaces the short form SourceDataType value INT with the long form INTEGER.

The following mapping for DB2 INT is compatible with a DB2 for z/OS V9 source.

<!-- INT -->
<dtm:DataTypeMapping>
<dtm:SourceDataType>
<dtm:DataTypeName>INT</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
<dtm:SimpleType>
<dtm:DataTypeName>INT</dtm:DataTypeName>
</dtm:SimpleType>
</dtm:DestinationDataType>
</dtm:DataTypeMapping>

The following data type mapping for DB2 INTEGER is compatible with a DB2 for i5/OS V6R1 source.

<!-- INTEGER -->
<dtm:DataTypeMapping>
<dtm:SourceDataType>
<dtm:DataTypeName>INTEGER</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
<dtm:SimpleType>
<dtm:DataTypeName>INT</dtm:DataTypeName>
</dtm:SimpleType>
</dtm:DestinationDataType>
</dtm:DataTypeMapping>

Data Type Mapping Files

The following table describes the three mapping files that you can edit when you use the Data Provider.

After you edit a mapping file, you must close and reopen the SQL Server Import and Export Wizard or the Business Intelligence Development Studio, depending on the environment in which you are working.

For more information about configuring SQL Server 2008 Integration Services, see the Importing and Exporting Data by Using the SQL Server Import and Export Wizard) topic in SQL Server Books Online (https://go.microsoft.com/fwlink/?LinkId=193204).

Customizing Data Flow Components

You can use SQL Server Integration Services Data Flow Components to perform default and customized transformations. The customized transformations are based on developer-provided custom code.

The SQL Server Integration Services mapping files in XML format are for use with the Import and Export Wizard. These files are not for use with the Data Flow. SQL Server Integration Services offers a Pipeline Buffer class to allow enterprise developers to customize data mapping within the Data Flow. For more information about customizing data flow components using SQL Server 2008 Integration Services, see the Working with Data Types in the Data Flow topic in SQL Server Books Online (https://go.microsoft.com/fwlink/?LinkId=241523).

SQL Server Replication

Derive Parameters

SQL Server Replication requires that Derive Parameter is set to FALSE.

Data Type Mapping

SQL Server Replication may convert data incorrectly, based on the default mappings from SQL Server to DB2 data types. We recommend that you review and revise the Replication data type mappings using the following SQL Server system stored procedures.

• sp_helpdatatypemap
  

• sp_getdefaultdatatypemapping
  

• sp_setdefaultdatatypemapping 
  

For more information, see the System Stored Procedures (Transact-SQL) topic in SQL Server Books Online (https://go.microsoft.com/fwlink/?LinkId=241524).

Problem with mapping SQL Server DATETIME2 to DBTYPE_TIMESTAMP

SQL Server 2008 Replication to DB2 for z/OS may fail with SQLCODE -188 (the string representation of a datetime value is not a valid datetime value). This occurs when Replication is configured to map DATETIME2 to DB2 VARCHAR(27) and uses subscription article commands with string literal data values.

Solution to problem with Step-by-Step Instructions

Re-configure SQL Server 2008 Replication to map DATETIME2 to DB2 TIMESTAMP and subscription article commands with parameters. This enables the Data Provider to format the DATETIME2 as a DB2 TIMESTAMP structure that is supported by the IBM DB2 database server.

1. Identify the data type mapping to modify. Use MASTER for all steps.

   select * from

   sys.fn_helpdatatypemap( 'MSSQLSERVER', '%', '%', '%', '%', '%', 0)

   Where destination_dbms = 'DB2' and source_type = 'datetime2'The results should indicate the mapping_id to modify. The following table shows the results pane for this example where the mapping_id is 189.

   mapping_id	source_dbms	source_type	destination_dbms	destination_type	destination_length
   189	MSSQLSERVER	datetime2	DB2	VARCHAR	27

2. Drop the data type mapping.

   exec sp_dropdatatypemapping 189

3. Add the data type mapping.

   exec  sp_adddatatypemapping
        @source_dbms = 'MSSQLSERVER', 
        @source_type = 'datetime2', 
       @destination_dbms = 'DB2', 
       @destination_type = 'TIMESTAMP', 
       @destination_nullable = 1, 
       @destination_createparams = 0, 
       @dataloss = 0, 
      @is_default = 1
   

4. Run the query again to verify the new data type mapping.

   select * from

   select * from sys.fn_helpdatatypemap
      (
      'MSSQLSERVER', 
       '%', 
       '%', 
       '%', 
       '%', 
       '%', 
       0)
   

   Where destination_dbms = 'DB2' and source_type = 'datetime2'

   The results should indicate the mapping_id to modify. The following table shows the results pane for this example where the mapping_id is 189.

   mapping_id	source_dbms	source_type	destination_dbms	destination_type	destination_length
   494	MSSQLSERVER	datetime2	DB2	TIMESTAMP	NULL

5. Identify the replication subscription article to re-configure. Use the Transact-SQL USE statement to switch from the master database to the database from which you are replicating.

   USE [Test]

   select name, status from sysarticles
   

6. The results should display the name of the article to modify. In this example, the following table shows the results where the name is DB2TS01.

   name	status
   DB2TS01	25

   If the status value is 1 or 9, then the article is configured for string literal formatting.

   If the status value is 17 or 25, then the article is configured for parameterized formatting.

7. Configure the replication subscription article for parameterized commands.

   USE [Test]

   DECLARE @publication AS sysname; 
   DECLARE @article AS sysname; 
   SET @publication = N'DB2TS_PUB01'; 
   SET @article = N'DB2TS01'; 
   EXEC sp_changearticle @publication, @article, 'status', 'parameters', 0, 0;
   

For more information, see Replication System Stored Procedures Concepts (https://go.microsoft.com/fwlink/?LinkId=241525) in SQL Server Books Online.

SQL Server Analysis Services

When you design cubes for use with SQL Server Analysis Services, the tools generate SQL commands that contain long alias names that may exceed the maximum length supported by the DB2 server. Depending on the DB2 platform and version that you use, you may not be able to use queries with alias names exceeding 18 characters. For example, many objects deployed in DB2 for z/OS use names of 18 characters. Refer to the DB2 SQL Reference for your DB2 platform and version and check with your DB2 database administrator. We recommend that the administrator or developer update the two SQL Server Analysis Service configuration cartridge files that contain the data type mapping support for DB2 by changing the identifier-length (limit-table-identifier-length) from 29 to 18. The following are the names and location of the two cartridge files that must be updated.

• C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\db2v0801.xs

• C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\db2v0801.xs

SQL Server Analysis Services uses the updated configuration files to correctly name objects in SQL commands.