July 14, 2015 at 10:10 am
scorchpc 96170 (7/14/2015)
g.britton (7/14/2015)
scorchpc 96170 (7/14/2015)
Hi,I am curious.
Are you able to automate the creation of an Excel file that preserves data types?
depends what you mean by preserves data types. Can you post some examples of what that would look like? Note that Biml can drive everything that is possible with SSIS but cannot create new transforms or enhance transforms added to the resulting packages.
Verbal example:
Extract data from SQL to Excel
Preserve data types such as Number and Dates. Make it so Numbers are treated like Numbers (for example when sorting or doing calculations), rather than Text.
[snip]
If Biml could do that, it would be a case of enhancing (fixing, IMHO) the Excel Destination transformation. I'm afraid it cannot do that.
What you *can* do, is preformat your template Excel file with the column formats you want. SSIS should respect those formats and not write the numbers as text (for example)
More info here: How to avoid 'number stored as text' error when exporting data from SQL server to excel (SSIS)
Gerald Britton, Pluralsight courses
July 15, 2015 at 5:16 am
g.britton (7/14/2015)
not sure what "use it in anger" means
Used something in a real world situation where the problem had to be solved as is and not a nicely tailored teaching problem.
g.britton (7/14/2015)
though you'd lose the out-of-the-box multithreading, buffer management, etc.
Once you know how to do it Multithreading in C# is actually very straight forward and uses just a handful of extra lines of code. The coding method does change though, i wouldn't like to have to refactor an existing solution to be multithreaded.
There are definite performance benefits when using SSIS though, especially when bulk loading tables with large amounts of data.
July 15, 2015 at 10:34 am
g.britton (7/14/2015)
scorchpc 96170 (7/14/2015)
g.britton (7/14/2015)
scorchpc 96170 (7/14/2015)
Hi,I am curious.
Are you able to automate the creation of an Excel file that preserves data types?
depends what you mean by preserves data types. Can you post some examples of what that would look like? Note that Biml can drive everything that is possible with SSIS but cannot create new transforms or enhance transforms added to the resulting packages.
Verbal example:
Extract data from SQL to Excel
Preserve data types such as Number and Dates. Make it so Numbers are treated like Numbers (for example when sorting or doing calculations), rather than Text.
[snip]
If Biml could do that, it would be a case of enhancing (fixing, IMHO) the Excel Destination transformation. I'm afraid it cannot do that.
What you *can* do, is preformat your template Excel file with the column formats you want. SSIS should respect those formats and not write the numbers as text (for example)
More info here: How to avoid 'number stored as text' error when exporting data from SQL server to excel (SSIS)
Thanks for the feedback.
I have tried pre-formatting the Excel file before, and the SSIS package still seemed to treat it all as text, even though I specified the proper data types, and even tried with valid example data.
I have tried that stackoverflow link before with no luck.
Thanks again for trying though. If I ever get a better solution I will follow up.
July 15, 2015 at 1:26 pm
1. BIML might be free but to use it in anger you will need to install Mist which is not cheap at $4000 per licence plus annual support costs.
Can you give an example of the problem that BidsHelper couldn't solve and Mist was needed? I'm curious
to know. In my experience, BidsHelper provides 100% support for SSIS development. Sure things get little un-pretty, but everything is doable. I wrote all the code in this article which I also use in production using BidsHelper, not Mist.
2. There is a very steep learning curve. Ideally you want to be expert in SSIS and SSIS design patterns before using BIML
True, there is a learning curve. No denying that. But if you put in the effort, returns are terrific! I wouldn't say expert, but yes you have to be experienced in SSIS to use Biml. You have to be experienced in SSIS to create SSIS packages manually too, wouldn't you agree?
3. Debugging is a nightmare. First you have to write a valid BIMLScript which then generates valid BIML which is then transformed into a valid SSIS Package/Project. You can then run the SSIS to check it does what it's supposed to do.
There's limited support for troubleshooting in the free version. Luckily you can workaround this. One way is to
break your script into small pieces as I've done in the article.
4. Annoyingly there is the feeling that BIML was invented to fix a problem which shouldn't exist in the first place. SSIS Should be dynamic out of the box.
I'm guessing you mean, SSIS should be able to create multiple packages dynamically. You've got a point.
In fact Scott Currie, the guy who invented Biml worked for Microsoft on a similar project before leaving.
I'd say that if you do need a dynamic metadata driven solution then it will be less painful to forget BIML and SSIS and write the solution in pure C#.
It depends on your team's skill set.
Sam Vanga
http://SamuelVanga.com
July 16, 2015 at 4:31 am
Sam Vanga (7/15/2015)
Can you give an example of the problem that BidsHelper couldn't solve and Mist was needed? I'm curiousto know. In my experience, BidsHelper provides 100% support for SSIS development. Sure things get little un-pretty, but everything is doable. I wrote all the code in this article which I also use in production using BidsHelper, not Mist.
By un-pretty I presume you mean a complete mess? Projects have died horrible deaths for less than that
Mist helps with code management, deployment and generation plus a bunch of other stuff.
July 16, 2015 at 5:43 am
By un-pretty I mean you'd have to type BIMLScript without intellisense and proper formatting in Visual Studio.
Sam Vanga
http://SamuelVanga.com
July 18, 2015 at 4:19 am
Mist gives you so much additional functionality I think the $4000 price tag is cheap.
Mist Transformers are the biggest value add for mine.
For consultants building a Framework using Mist Tranformers allows you to deliver ETL patterns to your clients faster and cheaper with 100% compliance to patterns and standards.
Offline schemas allow you build packages without having to be connected to the databases
The ability to import packages makes it easier to build your complex patterns. If you've got a complex pattern you can create the package in SSIS then import that package which creates all the BIML. Then add in your BIML script to mass produce.
Upgrading clients to later versions of SQL Server and adding some new functionality to their packages is a breeze with Mist. I can import all their existing packages and add for instance an Audit framework using event handlers to each package and re-create the upgraded packages before lunch time. This would take weeks of error prone manual work if you're looking at hundreds packages.
The above is really just scratching the surface of the awesomeness of Mist.
July 2, 2016 at 11:11 am
the average developer will flip out when they have to add a new table field into an SSIS package. But, it seems some people really like feeling "good" at all the clicking and manual searching in a non-trivial SSIS package that this entails (especially if you're a contractor (paid by the hour?)).
Me? I'd rather make a couple of modifications in a BIML file (add new field to source and possibly destination), and build the new SSIS package from the BIML file. Or boss-person says, "we need to migrate our schema to X"... I quietly quote 120 hours, knowing there's a framework for generating this kind of work on BIMLScript.com, where probably in a day or two, I can generate it all from BIML.
To each their own, I guess.
BIML seems at the surface to be complicated (because XML?), but it's really not.
July 2, 2016 at 11:17 am
To do that, use the ExcelPSLib .Net library (https://excelpslib.codeplex.com/). This library manipulates data into the OOXML format, which Excel can then read as if it was a native file.
Excel really only has 3 data types anyways - Dates, Numerics and everything else as text.
July 2, 2016 at 11:18 am
look into https://excelpslib.codeplex.com/
July 5, 2016 at 7:44 am
Thanks for the feedback sir!
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply