Autogenerating SSIS file import packages using Biml

  • Johan Åhlén

    Ten Centuries

    Points: 1154

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

  • dave-dj

    SSChampion

    Points: 11292

    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

    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: 1154

    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: 1154

    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

    Good article, thanks.

  • Alan Burstein

    SSC Guru

    Points: 61087

    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

    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: 13689

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • Johan Åhlén

    Ten Centuries

    Points: 1154

    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: 1532

    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.

  • WAL

    Mr or Mrs. 500

    Points: 594

    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: 13689

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

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

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