Data Consumer Issues

 

This topic provides information to troubleshoot data consumer issues.

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.

FastLoad to insert TIMESTAMP value

When using SQL Server Integration Services OLE DB Destination with FastLoad to insert SQL Server datetime or datetime2 values into a DB2 TIMESTAMP column, you may encounter this error.

THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE

To work-around this problem, you should set the Data Provider data source property Use Early Metadata to true.

Use Early Metadata=True  

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.

DB2 Data Type NameDB2ToMSSqlDB2ToMSSql10DB2ToSSIS10
TIMEDATETIMEtimeDT_DBTIME
TIMESTAMPdatetimedatetime2DT_DBTIMESTAMP2
DATEDATETIMEDATEDT_DBDATE
CHARCHARCHARDT_STR
CHAR () FOR BIT DATABINARYBINARYDT_BYTES
CHAR () FOR MIXED DATANCHARNCHARDT_WSTR
CHAR () FOR SBCS DATACHARCHARDT_STR
CHARACTERCHARCHARDT_STR
CHARACTER () FOR BIT DATABINARYBINARYDT_BYTES
CHARACTER () FOR MIXED DATANCHARNCHARDT_WSTR
CHARACTER () FOR SBCS DATACHARCHARDT_STR
NATIONAL CHARACTERNCHARNCHARDT_WSTR
VARCHARVARCHARVARCHARDT_STR
VARCHAR () FOR BIT DATAVARBINARYVARBINARYDT_BYTES
VARCHAR () FOR MIXED DATANVARCHARNVARCHARDT_WSTR
VARCHAR () FOR SBCS DATAVARCHARVARCHARDT_STR
CHARACTER VARYINGVARCHARVARCHARDT_STR
CHARACTER VARYING () FOR BIT DATAVARBINARYVARBINARYDT_BYTES
CHARACTER VARYING () FOR MIXED DATANVARCHARNVARCHARDT_WSTR
CHARACTER VARYING () FOR SBCS DATAVARCHARVARCHARDT_STR
NATIONAL CHARACTER VARYINGNVARCHARNVARCHARDT_WSTR
LONG VARCHAR FOR BIT DATAimageimageDT_IMAGE
LONG VARCHARtexttextDT_TEXT
GRAPHICNCHARNCHARDT_WSTR
VARGRAPHICNVARCHARNVARCHARDT_WSTR
GRAPHIC VARYINGNVARCHARNVARCHARDT_WSTR
SMALLINTSMALLINTSMALLINTDT_I2
INTINTINTDT_14
INTEGERINTINTDT_14
BIGINTBIGINTBIGINTDT_18
DECIMALNUMERICNUMERICDT_NUMERIC
NUMERICNUMERICNUMERICDT_NUMERIC
REALREALREALDT_R4
FLOATFLOATFLOATDT_R8
DOUBLEFLOATFLOATDT_R8
DOUBLE PRECISIONFLOATFLOATDT_R8
BLOBimageimageDT_BYTES
BINARY LARGE OBJECTimageimageDT_BYTES
CLOBtexttextDT_TEXT
CLOB () FOR MIXED DATAntextntextDT_NTEXT
CLOB () FOR SBCS DATAtexttextDT_TEXT
CHAR LARGE OBJECTtexttextDT_TEXT
CHAR LARGE OBJECT () FOR MIXED DATAntextntextDT_NTEXT
CHAR LARGE OBJECT () FOR SBCS DATAtexttextDT_TEXT
CHARACTER LARGE OBJECTtexttextDT_TEXT
CHARACTER LARGE OBJECT () FOR MIXED DATAntextntextDT_NTEXT
CHARACTER LARGE OBJECT () FOR SBCS DATAtexttextDT_TEXT
130ntextntextDT_NTEXT

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 (http://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 (http://go.microsoft.com/fwlink/?LinkId=241523).

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 (http://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  
    )  
    
    
  2. 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_idsource_dbmssource_typedestination_dbmsdestination_typedestination_length
    189MSSQLSERVERdatetime2DB2VARCHAR27
  3. Drop the data type mapping.

    exec sp_dropdatatypemapping 189  
    
    
  4. 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  
    
    
  5. Run the query again to verify the new data type mapping.

    select * from

    select * from sys.fn_helpdatatypemap  
    (  
       'MSSQLSERVER',   
        '%',   
        '%',   
        '%',   
        '%',   
        '%',   
        0  
    )  
    
    
  6. 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_idsource_dbmssource_typedestination_dbmsdestination_typedestination_length
    494MSSQLSERVERdatetime2DB2TIMESTAMPNULL
  7. 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  
    
    
  8. 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.

    namestatus
    DB2TS0125
  9. 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.

  10. 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 (http://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.

Show: