VSTSC's Community Server

Community Server: The platform that enables you to build rich, interactive communities.
VSTeamSystemCentral: Your Team System community!

Welcome to VSTSC's Community Server Sign in | Join | Help
in Search

Steve Fibich

  • Data Dictionary Part II

    Ok, after putting up my first post on the Data Dictionary I had posters remorse.  There is a simple change that makes the script shown in the previous post a little more user friendly.  First the view metadata.columnsDataDictionary need to be changed from a SQL Select using a pivot to just a self join from the metadata.columnsExtendedProperties to allow for columns that shared the same domain to easily display different MS_Description values.  The second change was to allow the procedure metadata.columnsExtendedPropertiesRepopulate to allow for an overall overwrite or just to add new values and retain old MS_Description extended propertie values.  I don't think I mentioned this earlier the extended property of 'MS_Description' will be picked up by a number of development applications so it is useful to stick your data dictionary value in there but not to overwrite one if it came supplied with say a third part application installation or if you are allowing a third party applicaiton manage your descriptioins in the database.

    Well thats it here is the code with a simple example:

    USE [steves]

    GO

    IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedProperties]'))

    DROP VIEW [metadata].[columnsExtendedProperties]

    GO

    IF EXISTS (SELECT * FROM sys.views

    WHERE object_id = OBJECT_ID(N'[metadata].[columnsDataDictionary]'))

    DROP VIEW [metadata].[columnsDataDictionary]

    GO

    IF EXISTS (SELECT * FROM sys.objects

    WHERE object_id = OBJECT_ID(N'[metadata].[dataDictionary]') AND type in (N'U'))

    DROP TABLE [metadata].[dataDictionary]

    GO

    IF EXISTS (SELECT * FROM sys.objects

    WHERE object_id = OBJECT_ID(N'[dbo].[testExtendedProperties]') AND type in (N'U'))

    DROP TABLE [dbo].[testExtendedProperties]

    GO

    IF EXISTS (SELECT * FROM sys.objects

    WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedPropertiesRepopulate]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [metadata].[columnsExtendedPropertiesRepopulate]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [metadata].[dataDictionary](

    [domainName] [varchar](50) NOT NULL,

    [domainDesc] [varchar](1000) NOT NULL,

    PRIMARY KEY CLUSTERED

    (

    [domainName] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF

    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    go

    exec sp_addextendedproperty 'domain', 'domainName'

    , 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainName'

    exec sp_addextendedproperty 'domain', 'domainDesc'

    , 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainDesc'

    insert into metadata.dataDictionary

    (domainName,domainDesc)

    values('domainName','Short text name for the classification of a businessdata type or attribute')

    insert into metadata.dataDictionary

    (domainName,domainDesc)

    values('domainDesc','Description of the a business data type or attribute')

    GO

    create view [metadata].[columnsExtendedProperties]

    as

    ----------------------------------------------

    --Steve Fibich

    --1.11.2007

    --This view is used to match columns to their extended

    --properties. This is an extension of sys.extended_properties

    --system view.

    --

    ----------------------------------------------

    select

    top 100 percent

    s.name as schemaName

    ,t.name as TableName

    ,c.name as columnName

    ,e.name as EName

    ,e.value as EDesc

    ,c.column_id as columnID

    from

    sys.extended_properties e

    inner join

    sys.tables t

    on

    t.object_id=e.major_id

    inner join

    sys.columns c

    on

    e.minor_id=c.column_id

    and

    e.major_id=c.object_id

    inner join

    sys.schemas s

    on

    t.schema_id=s.schema_id

    where

    class=1

    order by

    s.name

    ,t.name

    ,c.column_id

    ,e.name

    GO

    create view [metadata].[columnsDataDictionary]

    as

    ----------------------------------------------

    --Steve Fibich

    --1.11.2007

    --This view is used to match columns to their extended

    --properties. It does this for particular extended properties

    --"domain" and "MS_Description. It then pivots these results for a

    --more useful layout.

    --

    ----------------------------------------------

    select

    top 100 percent

    cep1.schemaName

    ,cep1.tableName

    ,cep1.columnName

    ,cep1.eDesc as domain

    ,cep2.eDesc as MS_Description

    from

    (

    select

    schemaName

    ,tableName

    ,columnName

    ,eDesc

    ,eName

    ,columnId

    from [metadata].[columnsExtendedProperties]

    where

    EName='domain'

    ) cep1

    full outer join

    (

    select

    schemaName

    ,tableName

    ,columnName

    ,eDesc

    ,eName

    ,columnId

    from

    [metadata].[columnsExtendedProperties]

    where

    EName='MS_Description'

    ) cep2

    on

    cep1.schemaName=cep2.schemaName

    and

    cep1.tableName=cep2.tableName

    and

    cep1.columnID=cep2.columnID

    order by

    cep1.schemaName

    ,cep1.tableName

    ,cep1.columnID

    GO

    go

    create proc metadata.columnsExtendedPropertiesRepopulate

    ----------------------------------------------

    --Steve Fibich

    --1.11.2007

    --This proc removes any 'MS_Description' extended properties

    --from any columns of any tables that have matching entires in the

    --metadata.datadictionary table and that have an extended propertie of 'domain'

    --This is to try to make keeping 'MS_Description' descripions upto date eaiser

    --

    --@overwrite=0 turns off overwriting so existing MS_Descriptions will be left alone

    --@overwrite=1 existing MS_Description values will be deleted and re-created

    --@debug =0 sets additional logging off

    --@debug=1 sets additional looging on

    --

    ----------------------------------------------

    @overwrite bit=0

    ,@debug bit=0

    as

    --if @debug=0

    -- BEGIN

    -- select @debug=debug from metadata.logging where schemaName='metadata'

    -- END

    declare @schemaName sysname

    declare @tableName sysname

    declare @columnName sysname

    declare @domainDesc varchar(1000)

    declare extProp_cursor cursor for

    select

    e.schemaName

    ,e.tableName

    ,e.columnName

    ,dd.domainDesc

    from

    metadata.dataDictionary dd

    inner join

    [metadata].[columnsExtendedProperties] e

    on

    dd.domainName=edesc

    where

    ename='domain'

    open extProp_cursor

    Fetch next from extProp_cursor into

    @schemaName,@tableName,@columnName,@domainDesc

    while @@fetch_status=0

    BEGIN

    if @debug=1

    BEGIN

    select @schemaName,@tableName,@columnName,@domainDesc

    END

    if (exists (select * from ::fn_listextendedproperty('MS_Description', 'schema'

    , @schemaName, 'table', @tableName, 'column', @columnName)) and (@overwrite=0))

    BEGIN

    execute sp_dropextendedproperty 'MS_Description','schema'

    ,@schemaName,'table',@tableName,'column',@columnName

    END

    if not exists (select * from ::fn_listextendedproperty('MS_Description', 'schema'

    , @schemaName, 'table', @tableName, 'column', @columnName))

    BEGIN

    execute sp_addextendedproperty 'MS_Description',@domainDesc,'schema'

    ,@schemaName,'table',@tableName,'column',@columnName

    END

    Fetch next from extProp_cursor into

    @schemaName,@tableName,@columnName,@domainDesc

    END

    close extProp_cursor

    deallocate extProp_cursor

    -----------------------------------------

    --Example Table and Extended Properties--

    -----------------------------------------

    go

    create table dbo.testExtendedProperties

    (

    column1 int

    ,column2 int

    ,column3 int

    ,column4 int

    ,column5 int

    ,column6 int

    )

    exec sp_addextendedproperty 'domain', 'domainName'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column1'

    exec sp_addextendedproperty 'domain', 'domainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column2'

    exec sp_addextendedproperty 'domain', '3rdDomainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column3'

    exec sp_addextendedproperty 'domain', '4thDomainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column4'

    exec sp_addextendedproperty 'domain', '3rdDomainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column5'

    exec sp_addextendedproperty 'domain', '4thDomainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column6'

    insert into metadata.dataDictionary

    (domainName,domainDesc)

    values('3rdDomainDesc','This is the 3rd domain description that we have it should be on column3 and column5')

    insert into metadata.dataDictionary

    (domainName,domainDesc)

    values('4thDomainDesc','This is the 4th domain description that we have it should be on column4 and column6')

    execute metadata.columnsExtendedPropertiesRepopulate

    select * from [metadata].[columnsDataDictionary]

    select * from [metadata].[columnsExtendedProperties]

     

  • Data Dictionary part I

    Having a data dictionary is an invaluable resource for not only for the DBA but for anyone, report writers or developers who need to work on a database that you have to manage or have developed yourself.  A good amount of time can be saved from answering the same questions over and over again if you can point to documentation to answer any initial questions.   Microsoft provides a way to keep a this information in each database without having to change the structure of any of the objects that already exist.  Extended Properties are a valuable resource in keeping metadata close to the heart of the system where it is needed.  In this series I will show you my data dictionary frame work.  I do create one table data dictionary that contains information that will work in conjunction with MS extended properties system functions, procedures, and views.  I have a concept of "data domains" which is a way to relate different columns in the same table or different tables that contain the same logical information that may not support a foreign key relationship.  An example of this is state in a data warehouse or master data management system, some tables may use the USPS two letter code, other tables from a different system may use an ISO 3 digit numeric code, but in each of these cases they represent the same information.  Connecting these two different data elements in the database can be incredibly useful to a developer especially if they are new to your environment.  To facilitate this I create an extended property every column in the database that is "domain" and populate it with the name of the data domain, in the example above the extended properties "domain" would get a value of "state" in each of the two different tables.  I then populate my dataDictionary table with the domain "state" and a description for this domain.  I have simple stored procedure that will read my dataDictionary table and place all of the domain descriptions in a extended propriety of "MS_Description" for all columns that have a matching value in the "domain" extended property.  I then have two simple views that extended the sys.extended_properites system view (SQL 2005 object).  Below I have listed the code that will generate the aforementioned objects and a quick sample to show what it can do.   (This code requires a metadata schema to exist)

    IF EXISTS (SELECT * FROM sys.views

    WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedProperties]'))

    DROP VIEW [metadata].[columnsExtendedProperties]

    GO

    IF EXISTS (SELECT * FROM sys.views

    WHERE object_id = OBJECT_ID(N'[metadata].[columnsDataDictionary]'))

    DROP VIEW [metadata].[columnsDataDictionary]

    GO

    IF EXISTS (SELECT * FROM sys.objects

    WHERE object_id = OBJECT_ID(N'[metadata].[dataDictionary]') AND type in (N'U'))

    DROP TABLE [metadata].[dataDictionary]

    GO

    IF EXISTS (SELECT * FROM sys.objects

    WHERE object_id = OBJECT_ID(N'[dbo].[testExtendedProperties]') AND type in (N'U'))

    DROP TABLE [dbo].[testExtendedProperties]

    GO

    IF EXISTS (SELECT * FROM sys.objects

    WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedPropertiesRepopulate]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [metadata].[columnsExtendedPropertiesRepopulate]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [metadata].[dataDictionary](

    [domainName] [varchar](50) NOT NULL,

    [domainDesc] [varchar](1000) NOT NULL,

    PRIMARY KEY CLUSTERED

    (

    [domainName] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF

    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    go

    exec sp_addextendedproperty 'domain', 'domainName'

    , 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainName'

    exec sp_addextendedproperty 'domain', 'domainDesc'

    , 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainDesc'

    insert into metadata.dataDictionary

    (domainName,domainDesc)

    values('domainName','Short text name for the classification of a businessdata type or attribute')

    insert into metadata.dataDictionary

    (domainName,domainDesc)

    values('domainDesc','Description of the a business data type or attribute')

    GO

    create view [metadata].[columnsExtendedProperties]

    as

    ----------------------------------------------

    --Steve Fibich

    --1.11.2007

    --This view is used to match columns to their extended

    --properties. This is an extension of sys.extended_properties

    --system view.

    --

    ----------------------------------------------

    select

    top 100 percent

    s.name as schemaName

    ,t.name as TableName

    ,c.name as columnName

    ,e.name as EName

    ,e.value as EDesc

    ,c.column_id as columnID

    from

    sys.extended_properties e

    inner join

    sys.tables t

    on

    t.object_id=e.major_id

    inner join

    sys.columns c

    on

    e.minor_id=c.column_id

    and

    e.major_id=c.object_id

    inner join

    sys.schemas s

    on

    t.schema_id=s.schema_id

    where

    class=1

    order by

    s.name

    ,t.name

    ,c.column_id

    ,e.name

    GO

    create view [metadata].[columnsDataDictionary]

    as

    ----------------------------------------------

    --Steve Fibich

    --1.11.2007

    --This view is used to match columns to their extended

    --properties. It does this for particular extended properties

    --"domain" and "MS_Description. It then pivots these results for a

    --more useful layout.

    --

    ----------------------------------------------

    select

    top 100 percent

    schemaName

    ,tableName

    ,columnName

    ,[domain]

    ,[MS_Description]

    from

    (

    select

    schemaName

    ,tableName

    ,columnName

    ,columnID

    ,EName

    ,EDesc

    from

    [metadata].[columnsExtendedProperties]

    )p

    PIVOT

    (

    min(EDesc)

    for EName IN

    ([domain],[MS_Description])

    )as pvt

    order by

    tableName

    ,columnID

    GO

    go

    create proc metadata.columnsExtendedPropertiesRepopulate

    ----------------------------------------------

    --Steve Fibich

    --1.11.2007

    --This proc removes any 'MS_Description' extended properties

    --from any columns of any tables that have matching entires in the

    --metadata.datadictionary table and that have an extended propertie of 'domain'

    --This is to try to make keeping 'MS_Description' descripions upto date eaiser

    --

    ----------------------------------------------

    @debug bit=0

    as

    --if @debug=0

    -- BEGIN

    -- select @debug=debug from metadata.logging where schemaName='metadata'

    -- END

    declare @schemaName sysname

    declare @tableName sysname

    declare @columnName sysname

    declare @domainDesc varchar(1000)

    declare extProp_cursor cursor for

    select

    e.schemaName

    ,e.tableName

    ,e.columnName

    ,dd.domainDesc

    from

    metadata.dataDictionary dd

    inner join

    [metadata].[columnsExtendedProperties] e

    on

    dd.domainName=edesc

    where

    ename='domain'

    open extProp_cursor

    Fetch next from extProp_cursor into

    @schemaName,@tableName,@columnName,@domainDesc

    while @@fetch_status=0

    BEGIN

    if @debug=1

    BEGIN

    select @schemaName,@tableName,@columnName,@domainDesc

    END

    if exists (select * from ::fn_listextendedproperty('MS_Description'

    , 'schema', @schemaName, 'table', @tableName, 'column', @columnName))

    BEGIN

    execute sp_dropextendedproperty 'MS_Description','schema'

    ,@schemaName,'table',@tableName,'column',@columnName

    END

    execute sp_addextendedproperty 'MS_Description',@domainDesc,'schema'

    ,@schemaName,'table',@tableName,'column',@columnName

    Fetch next from extProp_cursor into

    @schemaName,@tableName,@columnName,@domainDesc

    END

    close extProp_cursor

    deallocate extProp_cursor

    -----------------------------------------

    --Example Table and Extended Properties--

    -----------------------------------------

    go

    create table dbo.testExtendedProperties

    (

    column1 int

    ,column2 int

    ,column3 int

    ,column4 int

    ,column5 int

    ,column6 int

    )

    exec sp_addextendedproperty 'domain', 'domainName'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column1'

    exec sp_addextendedproperty 'domain', 'domainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column2'

    exec sp_addextendedproperty 'domain', '3rdDomainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column3'

    exec sp_addextendedproperty 'domain', '4thDomainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column4'

    exec sp_addextendedproperty 'domain', '3rdDomainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column5'

    exec sp_addextendedproperty 'domain', '4thDomainDesc'

    , 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column6'

    insert into metadata.dataDictionary

    (domainName,domainDesc)

    values('3rdDomainDesc','This is the 3rd domain description that we have it should be on column3 and column5')

    insert into metadata.dataDictionary

    (domainName,domainDesc)

    values('4thDomainDesc','This is the 4th domain description that we have it should be on column4 and column6')

    execute metadata.columnsExtendedPropertiesRepopulate

    select * from [metadata].[columnsDataDictionary]

     

    I will be revising this data dictionary system in future posts, I already have an issue with the way the  columnsDataDictionary view functions.  As you may have noticed the columnsExtendedPropertiesRepopulate overwrites any already existing "MS_Description" values already in place.  This will be enhanced in future versions.

  • Output Clause

    Alright have to give credit for this post to Andy because he is the one who pointed out how useful the Output Clause on an insert statement could be. Starting in SQL Server 2005 you could add an output clause to an insert, delete, or update statement to return information about the DML you just executed.  Below is a simple example that will return the identity inserted into a table in an insert statement without having to issue and @@identity, SCOPE_IDENTITY, or IDENT_CURRENT select

    Example:

     --Create the table--

    create table dbo.testOutput

    (

    ident int identity(1,1) not null primary key

    ,otherValue varchar(10) not null

    )

    -- Run the insert and get back the identity just inserted.-

     insert into testOutput

    (otherValue)

    output inserted.ident

    values('Test 1')

    The output clause can select from the DELETED and INSERTED tables similar to how you can access the INSERTED and DELETED tables from inside a trigger.  BOL has a whole host of information on this command but you might not know to look for it if you didn't know it existed.

     Thats it...have a good weekend.

  • The Third Project - Obscure T-SQL commands

    Ok so maybe this isn't a project so much as a topic.  This will be the first in a series of post dealing with obscure t-sql commands from SQL Server 2000 thru 2008 that I find, that may be useful to other people.  Hopefully this will be a weekly series and allow me the opportunity to find new commands or find out more about commands I thought I knew. This series is not meant to tell you everything about a command BOL will do this in a much more in-depth manner than these post, but I will bring to light commands you might not even now exist.  This week I have two commands one common one not so common but both very useful.

    GO, yes thats it, we've all used it to break up statements but did you know you can add parameters to the GO command (Go is not a T-SQL Command it is a command recognized by the sqlcmd or osql utility), say GO 5 this will cause the statement block above to execute five times.  I saw this on a QotD over on SQLServerCenteral.com (October 8th 2007 - What does this do?)

    Example:

    select rand() random

    select count(*) from sysobjects

    GO 5

    The next obscure T-SQL command is WAITFOR this command allows you to put a delay into your sql code.  This command allows you to delay for a certain amount of time or until a certain time occurs.  The command has two key words associated with it DELAY for an amount of time specific duration and TIME for a specific time to run the command following it.

    The example below will for 5 seconds in between the first select and the second.

    Example:

    select getdate() priorWAITFORDate

    WAITFOR DELAY '00:05'

    select getdate() postWAITFORDate

    As always BOL can provide additional and more in-depth command documentation.

  • Two Projects

    I have decided that the best way to have a more interactive community blog is to have posts that follow a project from start to finish.   To accomplish this goal and because I know I get board if I only have one project I decided to start posting about two personal projects on this site.  “theSystem” and another project “SOAK”.  Each of these projects has entirely different goals and each will allow me to explore and learn about different areas of SQL Server (and its components….SSIS, Service Broker, Reporting Services, ect…)

    The first is called “theSystem” and is my attempt to create a game engine based wholly SQL Server.  I know this sound like a daunting task and may make someone to ask “why would you want to do this? “  The answer is simple, the challenge, I have been kicking around the idea o building a game engine since about 5th grade when I first came across nethack/rogue.  I think SQL Server is up to the challenge, and when I say game engine I mean the game logic, functions, and data required making a game like this progress.  If I ever get around to building a front end for it, it will be in something else…don’t know what.    The current plan is to have all user functions available via stored procedures (either T-SQL or .NET) and make them available to users through and ODBC/OLE/JDBC connection and through the web via an XML interface.

    ·         SQL Server 2005 based gaming engine

    ·         All “business/game” logic to be encapsulated in SQL Server objects (T-SQL or .NET)

    ·         All game data to be stored in SQL Server 2005

    ·         All user functions to be available through ODBC/OLE/JDBC connections (XML Web Service when time allows)

    ·         All users to get individual SQL Server accounts (we will see how this works out)

    The second is “SOAK”, a Data Warehousing and Master Data Management project dedicated to collecting, consolidating, cleansing all of the publicly available datasets found on the web for free.  (No sending off for CD’s or any other costs).  The examples attached to this project will not only give overall view of how to do something in SSIS for instance but provide actual code for a particular data set that is usable by anyone who needs to gather this data.  This system will also provide the ability for users to enter their own statistical information for given datasets to allow the system to grown on its own.  The current plan is to have all user functions available via stored procedures (either T-SQL or .NET) and make them available to users through and ODBC/OLE/JDBC connection and through the web via an XML interface. Finally statistical information in the system should be made available through Reporting Services reports.

    ·         Datasets to be pulled from the web for free

    ·         Allow users to enter updated statistics/information on pre-defined datasets

    ·         All user Functions to be available through ODBC/OLE/JDBC Connections(XML Web Service when time allows)

    ·         All users to get individual SQL Server accounts (we will see how this works out)

    ·         Provide reporting thru MS Reporting Services

    Hopefully this will be a good learning experience for everyone including myself.  If you see me going down the wrong path some let me know.  I plan on making the code base of each project available on line.  As each of these projects is flushed out I will utilize this post to document ground rules for each.  Finally if you know of any other projects like these let me know… I did a quick Google search and couldn’t find anything resembling these two ideas so I figured I would try to take them on.

  • Using the SSIS OLE-DB command on non friendly destinations (iSeries DB2)

    At the risk of becoming pigeonholed as the guy who only posts about SSIS and iSeries interaction I have decided to post yet again on this subject area.   The information provided below may lend itself to other non SQL server OLE DB destinations but I have not tried it with anything other than the MS OLE-DB driver for DB2. 

     The Issue:

    How do you get OLE-DB Command object to work with MS OLE-DB provider.  When you go to map parameters nothing is provided in the column mapping window.

    The Fix:

    You must manually add the columns to the External Columns Output in the Input and Output Properties tab under the OLE DB Command Input section.  Then you define these parameters to have correct data type and scale, precision, or length as the DB2 object you are operating on.

     

    The Walk Through:

    Setting up the source in this case a SQL Server table:

    go

    create table testSource

    (testChar char(1) not null primary key

    ,testInte int not null

    )

    go

    insert into testSource

    values('T',1)

    insert into testSource

    values('S',2)

    Setting up the destination table on the iSeries:

    create table testDest (

    testChar char(1) not null

    ,testInt int not null

    )

     

    See Mom no Parameters:

    Adding columns:

     

    Running:

    Technorati Profile
  • iSeries TimeStamps to SQLServer using SSIS

    Here is a simple enough problem, the IBM iSeries (AS400) allows for a date range that starts at a lower value then SQL Server can handle, but one that SSIS can handle.  This can cause a slight issue when trying to import data from an iSeries into a SQL Server.  This can be handled by a derived column transformation checking the source column for a TimeStamp value less than 1753-01-01, then replacing it with 1753-01-01, or null or whatever other value you desire.  Another simple solution is to place this value into a char or varchar  on SQL Server buy you may have this problem when you go to query this field later.  The data for this example does not contain valid date of a value early then 1753 so I know if I get a date of 0002-01-01 that it is bad source data.  I may still want to retain this information in a char/varchar field so I can refer to but it is safe to be replaced in my SQL Server because it is bad data, but the related data may still have value.  (Also reporting on bad data from the source system has value in itself)

    1st Execute SQL task: Delete the records from my test table on the iSeries

    2nd Execute SQL task: Filling the test table on the iSeries with a date time stamp value that SQL Server cannot hold.

    Data Flow task: The record set is multicast into two pipelines that end up inserting records into the same table on the same SQL Server.  (I wanted to show the complete and failure separately otherwise I would used a UNION and put the results back together)  The pipeline on the right hand side goes through a transformation to replace the invalid SQL Server datetime value.

    Derived Column: Transformation of the date time SQL server can not handle via a if then statement.  (TESTTIMESTAMP < (DT_DBTIMESTAMP)"1753-01-01" ? (DT_DBTIMESTAMP)"1753-01-01" : TESTTIMESTAMP)

    The package below runs successfully for the pipeline on the right, while the left fails, notice the values in the TESTTIMESTAMP data viewers, and how it changes.

    Pretty straight forward but it took a few minutes for me to think about what I should do when I encountered this error so I figured I would share it with everyone.  What is nice is that SSIS can handle a timestamp value outside the range of what SQL Server can.

  • Looping through iSeries DB2 Data in SSIS (DB2 for the AS400)

    This should be a pretty short post.  I came across a pretty interesting behavior using the Native OLE DB\Microsoft OLE DB Provider for DB2 when its configured to connect to a iSeries DB instance.  (This is the driver provided by MS in an add on pack to SQL Server 2005 sometime earlier this year)  When you try to loop through a record set of data in a for each loop the package will failure with an error of wrong data type for data types that should match.  Numeric to Double, Decimal to Double, Char to Char, Varchar to Char.  I did notice that you can loop through any of the data types if you assign the variable its being dumped into to type Object and that variable can still be used to pull out the value of that field.  I have found the tested with the following data types here are my results:

    iSeries DB2 Type    SSIS Type 

    Char--------------------->String

    VarChar---------------->String

    Integer------------------>Int32

    TimeStamp----------->DateTime

    Numeric--------------->Object

    Decimal--------------->Object

    Char to Char Failure

     

    Char to Object Success

    TimeStamp to DateTime Success

    TimeStamp to Object Success

    Thats it, if you come across any others send me an email and I'll update the list above

  • Passing a values back from a child package to a parent package in SSIS

    This is an example of something similar to what I saw Allan Mitchell execute in an SSIS package about a year ago.  I didn't think much of it until a few days ago when I needed to pass a bunch of data from a child package back to the calling parent package.  This example passes a record set from a child package to the parent package where the parent package loops through object.  This example uses the Adventure Works database to push data from sys.tables table into a record set in the child package then push this information into a Object in the Parent Package.  There are a few things that I will mention up front that have to be in place to make this work.

    1. The name of the Object in the parent package has to be different from the name of the Object in the child package
    2. You have to place the name of the parent Object in the script task's ReadWriteVariables line in the child package.
    3. The child package cannot contain a local variable with the same name as that of the Object variable from the parent package
    4. This looks to pass the variables by reference, and I'm not exactly sure why this is possible since I never assigned this variable from the parent package using package configurations.  If anyone one has any information I would like to know how and why the parent packages variables are aviable to the child package?  It does work which is the main reason I'm making this post.

    Here we go:

    This is a screen shot showing the layout and variables of the parent package, you can see the first TestObject Object and the first thing I do is call the child package.

     

    This is a screen shot of the child package showing the layout and the variables.  Please note the TestedObject Object and the lack of a variable called TestObject

    Execute SQL task selects the top 10 name values from the sys.tables table in the adventure work database.  This data is placed into the Child Package variable TestedObject

    Screen Shot 3

     

    Execute SQL task assigning the record set to the TestedObject.

     

    The Script task Script page.  Notice the readOnlyVariables has the TestedObject and the ReadWriteVariables line has the TestObject variable from the parent package.  (This variable was not declared in this package)

     

     The script task script itself does a simple assignment from the TestedObject to the TestObject variables.

     

    This last screen shot shows the variable Test which was assigned to the result set in the for loop to display each row of the record set TestObject.

     

     

    This is a screen shot of the package executing creating message boxes for each row in the sys.tables record set.

     

     

    I found this process very useful to pass metadata about versions of data imported into a master data management system from a child package back to a parent package.  You can pass any data types from a child to a parent using this method.  Normally I put the script that passes the value from the child to parent package in the post execute event handler.  The only reason I put the task in the event handler is for style, as I feel it’s better to put this variable handling code separate from the specific package logic itself.

     Well that’s it let me know what you think.

     

  • Exporting Multi-Record Formated Flat File with SSIS

    I recently had to come up with an easy way to create a multi record formatted file as a destination from and SSIS package.  While I found a number of helpful tips on how to read from such a file I did not find anything on how to create such a file.  I decided to see if I could do this utilizing standard SSIS objects with as little code as possible.  I will walk you through the process utilizing the Adventure Works database as a source ( The sample database can be found at codeplex: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004)

    The first thing I want to cover is what I a mean by a multi-record formatted file.  This is a flat file that is delimited by some character in this case we will use a comma and the number of columns changes from one record type to the next.  In this example I will have a file that has 2 distinct record formats.  The first record format is the header record which has 4 columns from the salesOrderHeader table from the Adventure Works database. SalesOrderID, RecordType, OrderDate, and CustomerID.  The second record type in this file is from the salesOrderDetail table and it contains 6 data elements; SalesOrderID, RecordType, ProductID, OrderQty, UnitPrice,and LineTotal.  The two record type values are A_SH for the sales header record format, and B_SD for the sales detail record format.  Below I will step through the process to creating this type of file as easily and quickly as I know how.

    Step 1 is to get the first data set required; I choose to get the Header data first.  This data is sorted by the SQL server for the merger join later.

    Step 2 is to get the second data set, this is also sorted by the SQL Server for use in the merge later.

    Step 3 is to add the derived column "DataColumn".  This is where all the real work happens.  What you do is put all of the non sort key data into a single row that is exported as a string (This can be any type of string). There is no reason to put the key data into this column as it will be used in the merge to give you the header/detail/detail/detail output you are looking for.  All of the data you place in the derived column needs to be seperated by the column delimiter you choose for your export file, in this case a ",".  The header and detail data sources are required to have a key row in common in this case SalesOrderID, this column is sorted first then the RecordType column will be sorted, where A_SH will come out first then A_SD(More on this later)

    Step 4 is very similar to step 3 except you are working on the second data set.

     

    Step 5 is the merge step, join the two data streams on both the SalesID and the RecordType id, notice the sort order.  This will give you all of the SalesID from both the header and detail data streams together ordered by the RecordType, giving you the Header/Detail/Detail/Detail layout required by this type of file layout.

    That's really it, the last step is just to stuff this data into a flat file that has the same column delimiter as you chose in the derived column tasks early in the pipeline.

     

    Execution Results:

    More can be added onto this package to give a header record or end record with row counts, execution date, or any other meta data required by the system reading this file.

    Thats it, short and sweet.  I'll try to the example package up on the site sometime soon.

More Posts « Previous page
Powered by Community Server (Personal Edition), by Telligent Systems