quillis131 (1/23/2013) samvanga (1/22/2013)
You may want to look at BIML (Business Intelligence Markup Language). It comes with BIDS Helper
It's metadata driven and allows you to automate SSIS package generation.
I've implemented this scenario, but moving tables from SQL Server source to staging tables in SQL Server (not Oracle). Source and staging tables should have the same table and column names. Then, columns will be automatically mapped for each table.This script
is an example to get started. It creates SSIS package to export each table in the database to a flatfile. You'll see it is dynamic and you don't have to manually create them.
I've a framework I about to publish that might also give you ideas. I'll let you know when it's published.
I have difficult in understanding this BIML concept….. I end up editing BIML for every package that I want to create (Source, source Query, Destination, data types). I could have done it in the same way by copying an SSIS package and editing it or standard template can be used to generate a SSIS package with CM and Task and DFs and edit source and Destinations, isn’t this same as BIML?
I am trying to understand what more flexibility BIML adds up when compared to customized SSIS Template.
Good question. BIML attempts to be a simple text-based representation of a package, which can be 'built' (can't remember the correct term) into a package.
It's possible to create a routine - eg a stored proc - which will accept parameters & return an XML string in BIML format which can be built straight into a package.
So you can create packages which do not require any BIDS development. And recreate them at will.
This is particularly useful when you want to use the same simple package structure for numerous sources and destinations (eg, a data warehouse load).
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.