SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Metadata driven SSIS Packages
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LearningSQLKid
Yak Posting Veteran

Hong Kong
51 Posts

Posted - 04/06/2014 :  09:19:35  Show Profile  Reply with Quote
Hi Experts

I need to know what are the disadvantages of metadata driven etl framework.

I have a metadata repository of all my objects and someone suggested me that i should go with the metadata driven approach and create ssis packages based on my metadata. This means that i have to find some programming tool like bidshelper

also is it possible to create a packages which has columns stored in database with their datatypes and destination table column mappings .. does ssis allow this type of approach. pleased help me ..


Thanks





Select Knowledge from LearningProcess

gbritton
Flowing Fount of Yak Knowledge

1496 Posts

Posted - 04/07/2014 :  15:20:45  Show Profile  Reply with Quote
SSIS already maintains metadata and is metadata-driven out of the box. That's just how it works. I think you're asking if you can have another metadata source that SSIS can refer to. The short answer is not really. The long answer is that it should be possible to take in your metadata and produce SSIS-compliant XML that you can use to build SSIS packages on the fly (they're just XML). Personally I wouldn't want to do that, but it's up to you.
Go to Top of Page

LearningSQLKid
Yak Posting Veteran

Hong Kong
51 Posts

Posted - 04/08/2014 :  08:59:52  Show Profile  Reply with Quote
Thanks gbritton

Here is my problem and may be you can suggest some good points

I have a database called MetaDataDB. This database has a table called ETLLoadTables. The structure is below

TableId , TableName, Query
1 Customers Select CustomerID,CustomerName From Customers
2 Sale Select SaleID,CustomerID From Sale


Now I want to loop through this table get the Query In variable , pass this variable to source in dataflow and refresh the mappings and load it to destination .. is it possible to achieve in ssis 2008,2012 ?

I can use it EXECUTE SQL task but how can i make data flow to work in dynamic fashion. any idea


Thank you so much



Select Knowledge from LearningProcess
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1496 Posts

Posted - 04/08/2014 :  14:20:26  Show Profile  Reply with Quote
quote:
Originally posted by LearningSQLKid

Thanks gbritton

Here is my problem and may be you can suggest some good points

I have a database called MetaDataDB. This database has a table called ETLLoadTables. The structure is below

TableId , TableName, Query
1 Customers Select CustomerID,CustomerName From Customers
2 Sale Select SaleID,CustomerID From Sale


Now I want to loop through this table get the Query In variable , pass this variable to source in dataflow and refresh the mappings and load it to destination .. is it possible to achieve in ssis 2008,2012 ?

I can use it EXECUTE SQL task but how can i make data flow to work in dynamic fashion. any idea


Thank you so much



Select Knowledge from LearningProcess



Things will work up until the "refresh the mappings" part. I know of no way to dynamically tell SSIS to do that. Think about it for a minute. When you build a package in the SSIS designer, it in turn builds an XML file (the .dtsx -- x is for XML) that eventually gets run. At execution time, the SSIS engine validates the dtsx file. I think that you are basically asking the engine to modify the dtsx on the fly. I don't believe that it can do that.

Edited by - gbritton on 04/08/2014 14:21:57
Go to Top of Page

LearningSQLKid
Yak Posting Veteran

Hong Kong
51 Posts

Posted - 04/10/2014 :  07:47:23  Show Profile  Reply with Quote
quote:
Originally posted by gbritton

quote:
Originally posted by LearningSQLKid

Thanks gbritton

Here is my problem and may be you can suggest some good points

I have a database called MetaDataDB. This database has a table called ETLLoadTables. The structure is below

TableId , TableName, Query
1 Customers Select CustomerID,CustomerName From Customers
2 Sale Select SaleID,CustomerID From Sale


Now I want to loop through this table get the Query In variable , pass this variable to source in dataflow and refresh the mappings and load it to destination .. is it possible to achieve in ssis 2008,2012 ?

I can use it EXECUTE SQL task but how can i make data flow to work in dynamic fashion. any idea


Thank you so much



Select Knowledge from LearningProcess



Things will work up until the "refresh the mappings" part. I know of no way to dynamically tell SSIS to do that. Think about it for a minute. When you build a package in the SSIS designer, it in turn builds an XML file (the .dtsx -- x is for XML) that eventually gets run. At execution time, the SSIS engine validates the dtsx file. I think that you are basically asking the engine to modify the dtsx on the fly. I don't believe that it can do that.





Thanks gbritton

I too believe that it is not possible ... thank you so much for the reply and your time

Select Knowledge from LearningProcess
Go to Top of Page

LFBI
Starting Member

USA
1 Posts

Posted - 05/05/2014 :  21:24:20  Show Profile  Reply with Quote
I'm late to the conversation, but thought you may be interested in checking out LeapFrogBI.com. LeapFrogBI is a metadata driven ETL application which generates SSIS packages including dynamically updating field mapping. Drop me a line if you want to learn more. paul@leapfrogbi.com
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
806 Posts

Posted - 05/06/2014 :  14:50:59  Show Profile  Reply with Quote
Here is an option: http://sqlblog.com/blogs/allen_white/archive/2013/10/16/speaking-automate-your-etl-infrastructure-with-ssis-and-powershell.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000