Autogenerating SSIS file import packages using Biml

Question

Autogenerating SSIS file import packages using Biml

Johan Åhlén

Ten Centuries

Points: 1196
More actions
September 9, 2015 at 11:52 pm

#399017

Comments posted to this topic are about the item Autogenerating SSIS file import packages using Biml

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

dave-dj SSChampion Points: 11297 More actions · Answer 1

Could BIml be used to auto generate SQL to oracle interface with data type conversions easily or would it require the need to build some form of table column and data type mapping before I could do that?

Also, in terms of executing the packages, is there a simple way adding all the packages to a master 'control' package that calls each one, or would that need to be a manual step?

I can see a use for it where we have 60, (some very wide) tables we need to extract data for.

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

David Allan SSC Journeyman Points: 93 More actions · Answer 2

Wer are using a combination of XML/XSL to auto generate our SSIS packages from templates which we build using Maven to produce the SQL and BIML and then Hadron (part of MIST) to auto build the packages which then get deployed directly to SSIS servers. This has the advantage of being able to make a change in the XSL template and have this deployed to multiple packages in one build.

Johan Åhlén Ten Centuries Points: 1196 More actions · Answer 3

dave-dj (9/10/2015)
Could BIml be used to auto generate SQL to oracle interface with data type conversions easily or would it require the need to build some form of table column and data type mapping before I could do that?
Also, in terms of executing the packages, is there a simple way adding all the packages to a master 'control' package that calls each one, or would that need to be a manual step?
I can see a use for it where we have 60, (some very wide) tables we need to extract data for.

Hi Dave,

You can absolutely automate reading from Oracle databases. You can replace the Flat File Source with whatever source you want and you can autogenerate the SQL for that source, including data type manipulations, through BimlScript.

Autogenerating a master 'control' package can also be done. In fact it is pretty simple (only a few lines of BimlScript).

/Johan

Johan Åhlén Ten Centuries Points: 1196 More actions · Answer 4

David Allan (9/10/2015)
Wer are using a combination of XML/XSL to auto generate our SSIS packages from templates which we build using Maven to produce the SQL and BIML and then Hadron (part of MIST) to auto build the packages which then get deployed directly to SSIS servers. This has the advantage of being able to make a change in the XSL template and have this deployed to multiple packages in one build.

Yes, good example. There are ways to automate things even further by combining the best of different technologies... 😀

/Johan

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

September 10, 2015 at 7:07 am

#1825650

Good article, thanks.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 6

Great article Johan. I work with people who swear by BIML and I see why. This is the kind of thing I have tried to do before without success. I'm still getting up-to-speed with BIML and plan to do something like this in the near future. Very interesting and useful stuff sir!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

gerardo.arevalo Valued Member Points: 55 More actions · Answer 7

Hi! greate post.

Kind of confused about whether the .BIML file can be executed directly on the SQL Server without being imported in VisualStudio first. Make sense?

Thanks!

g.britton SSChampion Points: 13863 More actions · Answer 8

I just used BIML for the first time in a new project. Pretty pleased with the results except for one thing:

It's really hard to get help! I got stuck on one point and posted on the Biml forum at Varigence. I saw that others had posted on the same topic. however no answers or hints were ever posted. Had to give up on that one thing.

Pity.

Gerald Britton, Pluralsight courses

Johan Åhlén Ten Centuries Points: 1196 More actions · Answer 9

gerardo.arevalo (9/10/2015)
Hi! greate post.
Kind of confused about whether the .BIML file can be executed directly on the SQL Server without being imported in VisualStudio first. Make sense?
Thanks!

Hi Gerardo,

You'll need to open the Biml file either from MIST or from BIDS Helper (which is an add-on for Visual Studio).

/Johan

Sam Vanga SSCommitted Points: 1742 More actions · Answer 10

g.britton (9/10/2015)
I just used BIML for the first time in a new project. Pretty pleased with the results except for one thing:
It's really hard to get help! I got stuck on one point and posted on the Biml forum at Varigence. I saw that others had posted on the same topic. however no answers or hints were ever posted. Had to give up on that one thing.
Pity.

Sorry you didn't get an answer. I noticed posting the question with the link on Twitter (#Biml) to be helpful.

Sam Vanga
http://SamuelVanga.com

WAL SSChasing Mays Points: 615 More actions · Answer 11

I would consider BULK INSERT command to use in such trivial cases.

It could be automated to populate many tables in parallel instead of running sequentially SSIS packages.

Thanks! Nice article though!

g.britton SSChampion Points: 13863 More actions · Answer 12

Val Urban (9/11/2015)
I would consider BULK INSERT command to use in such trivial cases.
It could be automated to populate many tables in parallel instead of running sequentially SSIS packages.
Thanks! Nice article though!

Pretty easy to run ssis packages in parallel.

Gerald Britton, Pluralsight courses