Autogenerating SSIS file import packages using Biml

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

  • 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)

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

  • 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

  • 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

  • Good article, thanks.

  • 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

  • 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!

  • 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

  • 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

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

  • 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!

  • 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

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

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