Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generic SSIS Package Creation


Generic SSIS Package Creation

Author
Message
quillis131
quillis131
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 572
Hello,
I have an Idea and want to see if this can be possible or any real time Developed solution already there

Scenario :
Package should be able to pull data from Sql Server and push it to Staging tables on OrcaleDB. There are no transformation used, it should be a straight pull.

My team figured out the number of SSIS packages to be created will be some Hundreds with Different Source and destination Tables. All this packages will be copy for one another. Which is a heck of work to do.

I think off building one SSIS package and pass in all the Data ( Source, Destination, SourceDB) dynamically from a table ( Meta Data table),As the all packages logic is same. It’s just the Source and Destination table names are different.
I have no idea how mappings would be handled b/w Source and destination and Change of Connection managers.
Sam Vanga
Sam Vanga
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 502
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.

Sam Vanga
http://SamuelVanga.com
quillis131
quillis131
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 572
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.



BIML can only be written for SQL to SQL loading and not SQL to Orcale, am I right?
Sam Vanga
Sam Vanga
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 502
No. BIML can create packages to move data from SSIS to Oracle.

Sam Vanga
http://SamuelVanga.com
quillis131
quillis131
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 572
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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8291 Visits: 14368
If there are no transforms have you thought about setting up a Snapshot Replication Publication/Subscription to send data from SQL Server to Oracle?

If you must do this with SSIS then with hundreds of packages you may want to invest some effort building your own .NET application that can build and execute an SSIS package on the fly using the SSIS Object Model per metadata you store in a table, e.g. source SQL Server table and column names mapping to destination Oracle table and column name, but that sounds a lot like you'd be re-engineering what Replication can do for you.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8416 Visits: 19517
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 link.

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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search