locked
Importing Data From Flat file source into MDS RRS feed

  • Question

  • It would be great if I could get some inputs on how to design ETL/SSIS package to load staging tables in MDS. I mean any sample etl packages or any examples would really be help full. I have gone through several articles over the net, but felt I havent got enough hands on what I am doing,,,

     

    Any help would be highly appreciated

     

    Thanks

    Friday, August 13, 2010 5:34 AM

Answers

  • Hello

    I cannot send you the whole SSIS package because it is a part of a private project for a company.

    But, maybe you could send me your package, and I would be glad to help you to fix it. (I've  already sent you a mail with my personal gmail address)

    By the way, you told me that you have populated the staging tables.

    To process the unbatched rows, you can do it with the MDS UI:

    You can find this option in

    click on the icon below on the MDS UI after selecting a model/version

     

    Or, you can do it automatically in your SSIS Package using  an SQL Task with SQL type : Variable

     

     

    "DECLARE @ModelName NVARCHAR(50) ='"+ @[User::ModelName]+"' 
    
    DECLARE @UserName NVARCHAR(50)='"+ @[User::UserName]+"'
    
     DECLARE @UserId INT
    
    DECLARE @VersionId INT
    
    
    
    SET @UserId = (SELECT ID
    
       FROM mdm.tblUser u
    
       WHERE u.UserName = @UserName)
    
    
    
    SET @VersionId = (SELECT MAX(ID)
    
       FROM mdm.viw_SYSTEM_SCHEMA_VERSION
    
       WHERE Model_Name = @ModelName)
    
    
    
    EXEC mdm.udpStagingSweep @UserId=@UserId, @VersionId=@VersionId, @Process=1
    
    " 
    

     

    Regards

    Xavier Averbouch

     


    Xavier Averbouch
    Monday, August 23, 2010 4:28 PM

All replies

  • Have a look at http://www.sqlservercentral.com/articles/SSIS/69372/

    You can start mds staging process and model validation via the stored proc calls (mdm.udpStagingSweep, mdm.udpValidateModel).

     

    Regards,

    Andreas

     

     

    Friday, August 13, 2010 10:46 AM
  • Hello

    check theses links for staging table import

     http://sqlblog.com/blogs/mds_team/archive/2010/02/10/Importing-Data-by-Using-the-Staging-Process.aspx

    http://sqlblog.com/blogs/mds_team/archive/2010/02/10/staging-examples.aspx

    here is my SSIS Import Package :

     

     

    but, you can also import your data this way (no c# script)

    http://www.sqlservercentral.com/articles/SSIS/69372/

    Regards

    Xavier A.



    Xavier Averbouch
    Tuesday, August 17, 2010 8:55 AM
  • Thanks for the reply stanhe anf  xavave,

     

    Xavave,

    I have populated the staging tables but I am not able to load them into MDS . I am using the MDS UI to process the unbartched rows. I would request you to send me the sample ssis package that you have mentioned above to make sure I am not missing anything. My email is (sqldev.ashok@gmail.com if u need)

     

    Thanks again.

     

    Monday, August 23, 2010 3:06 PM
  • Hello

    I cannot send you the whole SSIS package because it is a part of a private project for a company.

    But, maybe you could send me your package, and I would be glad to help you to fix it. (I've  already sent you a mail with my personal gmail address)

    By the way, you told me that you have populated the staging tables.

    To process the unbatched rows, you can do it with the MDS UI:

    You can find this option in

    click on the icon below on the MDS UI after selecting a model/version

     

    Or, you can do it automatically in your SSIS Package using  an SQL Task with SQL type : Variable

     

     

    "DECLARE @ModelName NVARCHAR(50) ='"+ @[User::ModelName]+"' 
    
    DECLARE @UserName NVARCHAR(50)='"+ @[User::UserName]+"'
    
     DECLARE @UserId INT
    
    DECLARE @VersionId INT
    
    
    
    SET @UserId = (SELECT ID
    
       FROM mdm.tblUser u
    
       WHERE u.UserName = @UserName)
    
    
    
    SET @VersionId = (SELECT MAX(ID)
    
       FROM mdm.viw_SYSTEM_SCHEMA_VERSION
    
       WHERE Model_Name = @ModelName)
    
    
    
    EXEC mdm.udpStagingSweep @UserId=@UserId, @VersionId=@VersionId, @Process=1
    
    " 
    

     

    Regards

    Xavier Averbouch

     


    Xavier Averbouch
    Monday, August 23, 2010 4:28 PM