none
getting only date from datetime using expressions (and some rant about expressions in ssis)

    Question

  •  

    Hi all,

    I am new to ssis but not new to programming and bi software.

    I am trying to set a precedence constrains between two tasks in control flow based on a success of a date comparison

    Something like getdate() > @Mydate.

    Problem is the dates are stored with time in it and even though the date part is same in both sides of the "<" sign the time gets in the way.

    Now i'v looked in alot of blogs, help files, BOL, forums, etc etc etc

    And seems the topic of getting the date out of date part is something alot of people have opinions on

    But no where I found how to do it using ssis expressions

    Is there a way?? Plz plz help as I spent over 3 hours on this one single silly problem

     

     

    And speaking of expressions in ssis

    This is really something I cannot believe,

    Here is a list of complaints I have regarding expressions and editing expressions in ssis.

    1. How can one be expected to write an expression inside the derived column task when all the space is inside a single line that you have to constantly scroll left and right when you need to write a bit complex expression? It's even worse when you want to do it inside the precedence constraint dialog box. At least the derived column gives you some help with the list of functions. Why couldn't there be a global expression edit box that you can open on the entire screen with help and easy manipulation of code???????

    2. Are the ppl at Microsoft so lazy that they give you only a few functions to work with and expect you to make other function by using existing functions?? Couldn't there be a date functions that returns only the date or time from a datetime data type? Couldn't there be a left() function instead of expecting one to use the substring()???? Why so many other software products out there have such robust expression editors (crystal reports, qlikview to name a few) and Microsoft (the big mighty Microsoft cannot???? even in excel it's much easier to write expressions.

    3. and when you make a mistake and the expression is not valid, the only way you can know about it is to either hover over the expression with your mouse and wait till the tool tip appears which list the error in a really hard to read format and disappears after few seconds, so you have to click elsewhere then hover again and wait again. Or you have to try and ok the component and then get the error msg appear and block your way out!!! I mean haven't the guys in Microsoft heard of intelisense????

     

     

    I am a big fan of expressions and I know how strong they can make the package but I mean come on

    This is elementary stuff, it appeared in software for at least 5years

    And here we have a product that is aimed at nonprogrammers that are supposed to click and code and run

    And yet all I want to do is RUN

    As far away from it as I can

     

     

    Thanks for letting me release some steam fox.

    Hope I wasn't offending anyone

    And if I am wrong I would be very happy to be informed otherwise

     

    And please answer the question from the first part of my post, even if you don't agree with the 2nd Smile

     

    Saturday, May 31, 2008 7:44 PM

Answers

  • For what it's worth, I agree with most of your rant, even if the tone and delivery wasn't particularly helpful. The lack of maturity in the SSIS tools is a real point of frustration for me as well, but when I compare SSIS to DTS (the only other ETL platform Microsoft has given us) I find it very difficult to complain too loudly. If you want to turn your frustration into something constructive, you should post suggestions to the Microsoft Connect web site (http://connect.microsoft.com) as this is the channel that Microsoft has put in place for feature requests, bug reports and the like.

     

    For your actual question, take a look here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html. The final example shows how to easily strip the time from a datetime variable so you can deal only with the date portion. Please let us know if this gives you what you need.

    Saturday, May 31, 2008 8:41 PM
    Moderator
  • No worries, Daniel. For what it's worth, I don't think that any of your points will be a surprise to the SSIS product team, and I think you'll find existing Connect items for several of the "feature gaps" that you've identified. I think that it all comes down to priorities and resources; even though I am personally disappointed in the polish and usability of the SSIS development tools in SQL 2008, I think that the performance (have you seen the benchmarks for SSIS 2008? 2 TB in less than half an hour) and functionality (once you get past the annoyances in the tools, the packages you build sure so rock) I think they probably made the right tradeoff. Bus I sure do hope they devote some serious time into tuning and refining the developer experience, because three releases with v1 dev tools is going to be two too many...

     

    So, with that out of the way - did that expression work for you?

    Saturday, May 31, 2008 11:11 PM
    Moderator

All replies

  • For what it's worth, I agree with most of your rant, even if the tone and delivery wasn't particularly helpful. The lack of maturity in the SSIS tools is a real point of frustration for me as well, but when I compare SSIS to DTS (the only other ETL platform Microsoft has given us) I find it very difficult to complain too loudly. If you want to turn your frustration into something constructive, you should post suggestions to the Microsoft Connect web site (http://connect.microsoft.com) as this is the channel that Microsoft has put in place for feature requests, bug reports and the like.

     

    For your actual question, take a look here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html. The final example shows how to easily strip the time from a datetime variable so you can deal only with the date portion. Please let us know if this gives you what you need.

    Saturday, May 31, 2008 8:41 PM
    Moderator
  • hmm i guess i deserved itSmile

    you ware ofcourse right about the tone and delivery,

    i can only excuse it by the frsutration of stumbling upon somthing as silly (in my humble opinion) as this and wasting valuable develpoment time (and money on it)

    i found the above mentioned solution marely minuts before you posted and was on my way to post an update saying i found it when i seen you found it for me, so i must say thank you.

     

    as for the essence of my rants:

    i never used DTS (tho i heard many scary stories) so maybe im not coming from where you do, but i did use tools from other companies (and other tools from microsoft)

    and what i wanted to say is that for such a robust tool as integration services that i belive is aimed at making things easier (not harder) i think the lack of simple things like those i mentioned is a fundemental shortege

    and they are only a short sample of my rants on the product

    i v been working on a rather small project for over a month now and the majority of the time isnot spend on figuring buisness logic and what to do but rather on how to get simple things (as getting a date out of a date time) done.

    i know there is a large and helpfull comunity out there but i belive it should be there for larger things and not for these silly little things.

    when i turn to the comunity i prefer to ask major things and get help and new ideas on how to do things

    not to find answers to things iv done milion times before.

     

    i will certianly take your advice and use the Microsoft Connect web site  to point out what i said

    the problem is it will only be in the next product so it wont be much help for me nowSmile

     

    thank you very much anyway for the time taken to answer and relate to my questions and rants

    and keep up the good work

    thanks

    Daniel Chotzen

    Saturday, May 31, 2008 11:01 PM
  • No worries, Daniel. For what it's worth, I don't think that any of your points will be a surprise to the SSIS product team, and I think you'll find existing Connect items for several of the "feature gaps" that you've identified. I think that it all comes down to priorities and resources; even though I am personally disappointed in the polish and usability of the SSIS development tools in SQL 2008, I think that the performance (have you seen the benchmarks for SSIS 2008? 2 TB in less than half an hour) and functionality (once you get past the annoyances in the tools, the packages you build sure so rock) I think they probably made the right tradeoff. Bus I sure do hope they devote some serious time into tuning and refining the developer experience, because three releases with v1 dev tools is going to be two too many...

     

    So, with that out of the way - did that expression work for you?

    Saturday, May 31, 2008 11:11 PM
    Moderator
  • yes the expression with casting the date into db_date and back worked for me

     

    tho i wouldnt have thought of it myslef in a milion

    kinda unintoitive aint it lol

     

    as for this:

    "SQL Server 2008 introduces DATE only data type to address this issue"

    well i never asked for that, i think that more data type  will only make more confusion and casting and converting problems. a datetime type is not wrong, what is wrong is the lack of functions to extract the dat or the time parts sepertly

     

    anyway as far as im concern this thread is closed and ill let you know if i get answers from microsoft regarding my rants Smile

    thanks guys, as allways you all ROCK

    Sunday, June 01, 2008 5:11 AM
  •  mansyno wrote:

    anyway as far as im concern this thread is closed and ill let you know if i get answers from microsoft regarding my rants

    thanks guys, as allways you all ROCK

     

    If that's the case, please take the time to mark the appropriate responses as the answer and/or as "helpful" so future visitors to the forums can more easily find the answers to their questions. And thanks for the feedback.

    Sunday, June 01, 2008 12:43 PM
    Moderator
  • i agree that there probably wont be much sympathy coming from microsoft by complaining about ssis.  ssis is a very powerful and useful tool.  having said that, it leaves a lot to be desired.

     

    as mentioned above, ssis 2005 is a version 1.0 product.  i imagine that it would be quite some time before it matures to include many of the features that some of us have suggested.  nonetheless, i suspect that microsoft is doing everything it can to enhance the product in future releases.

    Monday, June 02, 2008 6:17 AM
    Moderator
  •  

    i did try to do that but when i pressed the button i was redirected to a page saying iwas successefully loged of

    the same thing hapnd when i pressed reply to answer this post ( i had to relogin)

    strange

    Monday, June 02, 2008 9:37 AM
  •  

     

    I agree 100% that SSIS is a cutesy, but unrealistic tool for development.  It is mainly for people who have a hard time learning SQL, and for small tasks.  I really shouldn't have to create a package that looks like pipe dream, just to load a simple table.  Yes, there are some neat features, but should it really replace SQL dev work?  You can do the same things with SQL, just without the overhead of whatever Microsoft has tacked onto their SSIS compilation and execution.  That being said, any "performance benefit" you are looking to gain with SSIS, will be completely diluted by the workarounds you have to do to extend their controls, or use simple functions.

     

    I am also having issues with responding to posts without getting logged out and having to log back in.  Every single time!!  I don't know what is wrong with this Website, but it is very annoying. Smile

    Wednesday, June 04, 2008 12:33 AM
  •  SalJ wrote:

     

     

    I agree 100% that SSIS is a cutesy, but unrealistic tool for development.  It is mainly for people who have a hard time learning SQL, and for small tasks.  I really shouldn't have to create a package that looks like pipe dream, just to load a simple table.  Yes, there are some neat features, but should it really replace SQL dev work?  You can do the same things with SQL, just without the overhead of whatever Microsoft has tacked onto their SSIS compilation and execution.  That being said, any "performance benefit" you are looking to gain with SSIS, will be completely diluted by the workarounds you have to do to extend their controls, or use simple functions.

     

    I am also having issues with responding to posts without getting logged out and having to log back in.  Every single time!!  I don't know what is wrong with this Website, but it is very annoying.

     

    well, i suppose it depends on what type of "development" work you're speaking of.

     

    i think that most ssis users will agree that ssis is a great development tool for ETL solutions, but probably overkill for most other types of database development work.

     

    i don't think that a "performance benefit" is one of the main reasons why ssis was created.  where ssis shines is in its ability to easily create sophisticated ETL solutions with very little programming needed.

     

    because ssis is relatively new, i think that a lot of sql server developers are still trying to figure out when it is needed and when it isn't.  after all, ssis is a version 1.0 tool, and version 2.0 is still in beta.

    Wednesday, June 04, 2008 5:59 AM
    Moderator
  • Since working on an enormous data warehouse using both stored procedures, and a completely different one using SSIS, I don't see a necessity for using SSIS for a majority ETL.  For the beginning stages I could see using it.  But, the only argument I have heard was that there are performance benefits when loading the data. That hasn't been proven to me, but I haven't totally dismissed it.

     

    I just don't see how dev could be faster, especially if you are also using TFS.  And, the UI hangs a lot, even developing on a beefy box.  I did see a Microsoft demo where they showed a pretty neat way of creating as little steps as possible for ETL, but I just don't think that the product is quite there yet, since they forgot to add some very important functionality to some of the tasks.  And, it would take more time developing to try to clone the missing functionality.Maybe SSIS 2008 is a little better.

     

    If a developer is sharp with SQL, I don't think it would take them very long to develop a solution at all.  And if they had equal skills of SSIS and SQL, I still think SQL would be faster for dev. 

    Wednesday, June 04, 2008 5:00 PM
  • it's very hard for me to imaging that it would be faster to create a robust ETL solution using SQL as the sole development tool as opposed to using an ETL tool.

     

    informatica is the market leader in ETL tools, and has been so for quite some time.  i believe that the main reason for this is that it vastly reduces the development life cycle of many ETL solutions.  i can't speak on informatica beyond that because i've never used their products.  however, i believe that ssis can create ETL solutions just as easily (if not easier) than informatica.  as a matter of fact, creating ETL solutions is much easier with ssis than with both .net and sql.

     

    my main gripe with ssis is the steep learning curve.  a novice application developer would find it to be rather daunting.  however, ssis development becomes much easier once that initial learning hurdle is surmounted.

     

    honestly, i can't imagine attempting to create a robust ETL solution without an ETL tool like ssis. sticking pins in my eyes would be more enjoyable.

     

    Friday, June 06, 2008 5:31 AM
    Moderator
  • I would not necessarily use just SQL for an entire ETL.  However, I would only use SSIS for bulk copies and simple tasks.

    There are too many exceptions to what you need in a REAL life ETL solution that SSIS just cannot encompass.  Workarounds = more dev time.  If you are just pushing data from object to object with very little transformation or logic, then yes, it is a great tool. 

    If you need more flexibility, and you actually know SQL, go ahead and use it.  A lot of people that are not comfortable with SQL love this toy. 

    Yes, at first there is a learning curve, but not after more than a few days of learning, you should have everything mastered. Especially if you are comfortable with the VS environment.  Once you figure out how transactions flow, it is quite easy. 

    However, it is also buggy in parts and can be slow loading metadata.  I know how to write a join and catch exceptions, I don't need to spend time dragging things around to do so.  And the debugging is awful!  Sure, logging is good, but some of the stuff is just plain atrocious.

    Friday, June 06, 2008 8:12 AM
  •  

    Well I am glad to see that my rants stirred some reactionsSmile hopefully Microsoft is looking at these posts and taking notes

    on the same spirit though I'm not sure this is the right place to put it, but since this tread is more about the rants now I guess ill just tuck it here.

     

     

    Here is a little description of me trying to do simple stuff in ssis.

     

    I have a main pkg that run several other pkgs.

    In each child pkg I have some excel and flat file connection managers.

    since they all require me to specify where they are in the file system (why other connection managers allow me to dynamically set setting via the property dialog while excel only lets me state the file location? and for everything else I must use the F4) I figured I could use variables to specify the exact location of the files (both input and output files)

    And that works great, but........ On my development machine

    Now comes deployment time.

    So I am thinking....

    I can configure the same subfolders hierarchy in the production machine as in my dev machine but I have no control over the root path.

    But hey I am covered right? I have variable holding the paths.

    all I need to do is create configuration files (or tables or whatever) for all my pkgs and then I could happily go into editing it on my production machine and go thru all 100 variables and change the root path to the one on the production machine (cool, right? hmm but what if I need to deploy to 100 production machines? ok not 100 but 10) do I need to do this each time? 1000 times????? Wow! (You guys can feel the sarcasm wind blowing right?)

    Hmm but then I said: wait I am a programmer. I can set a global path variable on my parent pkg and pass it to the child pakges and use this variable inside the variable in my child pkgs. surely it can be done on such a robust tool like ssis.

    ok so I go and study the articles discussing how to do it (pass variables to child pkgs) and qualla the good ppl at Microsoft thought of us poor developers and created the way, its fast, easy and just a few mouse clicks away . Just pass it via configuration (so cool).

    Alas in order to achieve my goal I need to configure a separate configuration (in the children pkgs) for each variable (remember I have many variables in each child pkg and configuration of type "parent package variable" can handle only one variable) in each of my child pkgs.

    Well I figured, I already spent 5 hours playing with this stuff (including searching and reading and learning) and since I only have to do it once I should as well do it and get it over with. So I did.

    I spent another 2 hours creating all the configurations and setting all the connection managers to except the new values from the variable that is now dynamically using the parent "Global_path" variable. And setting all the variable to use it and I should be home free

    I supposedly now can deploy my project on every machine I want. I just need to create the folder hierarchy, and set the global_path variable in my configuration file and it should run with out problems.

    Well ok, I took a coffee break and then went to test it on a test production machine sitting next to me.

    Hmmm what do you know it does work Smile

    Well that's a happy end.

    But.. And here are my two cents

     

    This process of investing so much  time on doing tasks that should be easy, well isn't that the opposite of what ssis is all about? Shouldn't it be that I spend LESS time on doing tasks that otherwise should take longer to develop?

    And that's  what I want to say. SSIS is not an ETL tool. It is an ETL DEVELOPMENT TOOL.

    It is meant for developers. Now you say it is suppose to be used by non programmers? But how exactly? If I look in the various forums vikis and articles and blogs related to ssis, every second line says: Script task, variables, t-sql, and workarounds.

    If I take the packages I've developed in the last month, I use only about as 6 or 7 of the stock components. Everything else is done either via scripts (ok it's a component but only a wrapper component for code so I won't count it as a drag and drop solution). So if I need to see someone without deep know-how in database programming trying to use ssis then I see disasters.

    I mean even for programmers this is way way way to low for a development tool. And please don't give me the :it's only the first version"

    Imagine you bought a truck to move containers across states. Would you accept if it didn't have all its wheels? Or no trunk? Or the gear gets stuck every 10 minutes and you have to find workarounds for it?  Think of the truck manufacturer  representative telling you it's only the first version of the truck and the main thing is that it drives fast (or even drive at all)  and in version two you will have all the things that are wrong fixed and whets missing completed. What do you think will happen to sales of the truck in this country? Smile.

     

    things like proper debugging, like proper and consistent dialog boxes, like proper expression editors, like proper variables exploring (for example an ability to search a pkg for all the places a variable is used) like proper ways to organize your pkgs in the project (yes I mean subfolders and sorting in the project explorer) .

    Even the idea of the separation of control flow and data flow (which is great) lacks the ability to move data easily between control components. Yes there is the rowfile, but why do I need to manually configure a row file each time  I want to pass data between two dataflow tasks in the same pkg? I mean the data is already in memory, why not just let it pass to the next data flow? That way a developer can encapsulate the flow and work more easily.

     

    These are just some of the things that a developer cannot do without ( and it doesn't matter if he/she uses ready made components or lines of code)

    And these are the things that are lacking in ssis (there are more).

    And I don't even want to start with the BUGs.

     

    So to conclude, this is what we have.

    I don't know about others but for me working with ssis is a must (since my boss says so)

    So I don't have a choice.

    I'm not familiar with other ETL/integration tools out there

    So I cannot tell if there are better solutions

    But I sure would try and search for them in my next project and use ssis only as a last option.

    And I think it's sad.

     

     

     

    I'm sorry for all the spelling mistakes

    I'm sorry that it turned out longer then I thought, maybe even too longSmile

    I'm sorry if I used sarcasm and cynicism but I am frustrated. (Not an excuse I know, but at least a reason)

     

    Daniel Chotzen

     

    Saturday, June 07, 2008 1:26 AM
  •  SalJ wrote:

     

     

    I agree 100% that SSIS is a cutesy, but unrealistic tool for development.  It is mainly for people who have a hard time learning SQL, and for small tasks.  I really shouldn't have to create a package that looks like pipe dream, just to load a simple table.  Yes, there are some neat features, but should it really replace SQL dev work?  You can do the same things with SQL, just without the overhead of whatever Microsoft has tacked onto their SSIS compilation and execution.  That being said, any "performance benefit" you are looking to gain with SSIS, will be completely diluted by the workarounds you have to do to extend their controls, or use simple functions.

     

    I am also having issues with responding to posts without getting logged out and having to log back in.  Every single time!!  I don't know what is wrong with this Website, but it is very annoying.

     

    Just who are you agreeing with 100% here? I don't see that opinion expressed anywhere else in this thread?

     

    For what it's worth, I have been using SQL server since version 6.0 and don't personally think that I have a hard time learning SQL, and regularly use SSIS for tasks that are anything but small.

     

    I hate to disagree in a public forum, but no one has suggested that SSIS should "replace SQL dev work." SSIS and SQL are both tools, and each is valuable for its own purpose. SSIS does things that would be much more difficult to accomplish and would both take longer to build and take longer to run if they were implemented in SQL alone. As an example, I have data flows that I have built in less than an hour that move millions of records per minute (on commodity hardware, not a massive box) from a source database into a data warehouse, performing over a dozen lookups to translate business keys into surrogate keys, as well as performing some basic data cleansing. Performing the same work in SQL alone would take much longer to build, and would require multiple transactions (and all of the transactional IO overhead that this implies) to the same thing.

     

    I would instead suggest that (like with any development tool) if someone were to use SSIS for tasks to which it is not well suited, or attempt to use it to solve complex problems without first taking the time to learn how it works and how best to use its functionality, then that person's experience with SSIS would be less than pleasant. But speaking for myself and dozens of SSIS developers with whom I have worked, I could not disagree more.

     

    Saturday, June 07, 2008 1:57 AM
    Moderator
  • if several path variables in a package all need the same root path, then all of these variables can use the exact same configuration value in order to acheive this.  the value of a root path stored in a parent package configuration can be shared by all of the child package variables via a single parent package configuration for each child package.

     

    a parent package can retrieve the value of a root path from a configuration.  this value can then be stored in one of the parent's package variables.  each child package can then retreive this value via a parent package configuration and store it in one of its own variables.  thus, each variable in a child package can use a root path stored in one of it's own variables.  it isn't necessary to have a separate parent package configuration for each child variable that uses the same root path.  in short, all of the above can be achieved using ssis expressions.

     

    expressions are one of the most powerful of all the ssis features.  expressions are used to dynamically set values.  every user defined variable can use an expression.  many (if not all) of the control flow tasks have properties that can be set using expressions.  many (if not all) of the data flow transformations can also use expressions.  all of the connection managers can use expessions as well.  in short, expressions are an easy and powerful way to dynamically calculate many values used in ssis.

     

    many ssis solutions can be achieved without any programming. however, this is the exception, rather than the norm.  it would be very daunting for anyone attempting to create a robust ETL solution in ssis without a programming background.  programming knowledge is required to create expressions, use the script task, or use the script components.  nonetheless, it is possible to create a very robust ETL solution with ssis using very little programming.

     

    creating a robust ETL solution with microsoft tools "should" be faster using ssis than without it.   however, i believe that significantly more learning time than several hours is required for anyone to become proficient enough with ssis for this to be possible.

     

    perhaps i'm mistaken, but i don't think that ssis was intended for use by novice developers.  nonetheless, as an experienced developer, i find ssis to be quite useful.

    Saturday, June 07, 2008 7:56 AM
    Moderator
  •  Duane Douglas wrote:

    if several path variables in a package all need the same root path, then all of these variables can use the exact same configuration value in order to acheive this.  the value of a root path stored in a parent package configuration can be shared by all of the child package variables via a single parent package configuration for each child package.

     

    a parent package can retrieve the value of a root path from a configuration.  this value can then be stored in one of the parent's package variables.  each child package can then retreive this value via a parent package configuration and store it in one of its own variables.  thus, each variable in a child package can use a root path stored in one of it's own variables.  it isn't necessary to have a separate parent package configuration for each child variable that uses the same root path.  in short, all of the above can be achieved using ssis expressions.

     

    i am assuming you refer to my previous post, well i didnt say i need a difrent parent config file for each child

    pkg. i mean i need to ad configuration into each of the CHILD pkgs if i want to retrieve values from the parent. and i need a diffrent config for each usch value. (why not be able to state several parent variables to effect several child variables in one child config?

    in ssis i need to use the configuration techniuqe  not as a mean to change values from outside the pkg (whice is mainly what configuration files are used for) but as a mean to pass values from parent into child.

     

    so for each variable in each child pkg it is  necessary  to go thru the process of creating a SEPERATE configuration of type "Parent Package Variable".

    is it not the case???

     

     Duane Douglas wrote:

    expressions are one of the most powerful of all the ssis features.  expressions are used to dynamically set values.  every user defined variable can use an expression.  many (if not all) of the control flow tasks have properties that can be set using expressions.  many (if not all) of the data flow transformations can also use expressions.  all of the connection managers can use expessions as well.  in short, expressions are an easy and powerful way to dynamically calculate many values used in ssis.

    not anyware in my posts did i say that expressions arent powerfull. or not useable.

    al i was saying was that in most components the way to DEVELOPE and write expressions, or the place in which you get to write them in. isnt good enoug for a tool like this. take the precedence constraint expression for example. you only get a small edit box which you cannot even expand , without any way to see the variable and functions from which you can build the expression. and even places like the dervied column that do have this option, dont have a suitable place to write the expression in.

     

     Duane Douglas wrote:

    many ssis solutions can be achieved without any programming. however, this is the exception, rather than the norm

     

     

    Hmm. this is intersting. since this is not the impression one gets when hearing or reading official publications about SSIS. but even so.

    i am an Experinaced proggramer. and maybe thats why i have so many complaints. becouse i beeen there-done that.

    and if write code (which is what  a programer do mostly) is the norm then by all means let me do that in a decent way in ssis. i dont mind writing code, or customising objects/commponents. but i do ask for the right tools and editors to work like that.

     

     

     

     Duane Douglas wrote:

    creating a robust ETL solution with microsoft tools "should" be faster using ssis than without it.   however, i believe that significantly more learning time than several hours is required for anyone to become proficient enough with ssis for this to be possible

     

    well i totaly agree.

    i spent 3 weeks learning ssis before i even started developing for production.

    iv read dozens of blogs, forums, articls, and documentations.

    and followed every tutorial about ssis i could find.

     

    but only when i started working for production and with real life scenarios  did i encountner most of my problems with ssis.

    i found myself getting stuck every few minutes.

    forced to go ask question is forums like a novice, for things i thought should work out of the box. i found myself spending valuable development time on creating workarounds and tricks and bugs in the tool.

    and generaly feeling that with one hand ssis is telling me: "run go where you want fast, i will help" and with the other hand i felt ssis pulling my leg and trying to stop me from moving.

    and all of the above is coming from getting frustrated for not getting what i thought i was getting.

     

    OK yes ssis is usefull, it has many built in tasks and components that let you do many things fast.

    but that is  the exception, rather than the norm.

    for everyting else you need to go SLOW, somtimes very slow.

     

    maybe there isnt anything better out there. but in that case is that the excuse of not giving me the basics?

    becouse i am forced to work with this?

     

     

     

     

     

    Saturday, June 07, 2008 9:22 AM
  •  mansyno wrote:
     Duane Douglas wrote:

    if several path variables in a package all need the same root path, then all of these variables can use the exact same configuration value in order to acheive this.  the value of a root path stored in a parent package configuration can be shared by all of the child package variables via a single parent package configuration for each child package.

     

    a parent package can retrieve the value of a root path from a configuration.  this value can then be stored in one of the parent's package variables.  each child package can then retreive this value via a parent package configuration and store it in one of its own variables.  thus, each variable in a child package can use a root path stored in one of it's own variables.  it isn't necessary to have a separate parent package configuration for each child variable that uses the same root path.  in short, all of the above can be achieved using ssis expressions.

     

    i am assuming you refer to my previous post, well i didnt say i need a difrent parent config file for each child

    pkg. i mean i need to ad configuration into each of the CHILD pkgs if i want to retrieve values from the parent. and i need a diffrent config for each usch value. (why not be able to state several parent variables to effect several child variables in one child config?

    in ssis i need to use the configuration techniuqe  not as a mean to change values from outside the pkg (whice is mainly what configuration files are used for) but as a mean to pass values from parent into child.

     

    so for each variable in each child pkg it is  necessary  to go thru the process of creating a SEPERATE configuration of type "Parent Package Variable".

    is it not the case???

     

     Duane Douglas wrote:

    expressions are one of the most powerful of all the ssis features.  expressions are used to dynamically set values.  every user defined variable can use an expression.  many (if not all) of the control flow tasks have properties that can be set using expressions.  many (if not all) of the data flow transformations can also use expressions.  all of the connection managers can use expessions as well.  in short, expressions are an easy and powerful way to dynamically calculate many values used in ssis.

    not anyware in my posts did i say that expressions arent powerfull. or not useable.

    al i was saying was that in most components the way to DEVELOPE and write expressions, or the place in which you get to write them in. isnt good enoug for a tool like this. take the precedence constraint expression for example. you only get a small edit box which you cannot even expand , without any way to see the variable and functions from which you can build the expression. and even places like the dervied column that do have this option, dont have a suitable place to write the expression in.

     

     Duane Douglas wrote:

    many ssis solutions can be achieved without any programming. however, this is the exception, rather than the norm

     

     

    Hmm. this is intersting. since this is not the impression one gets when hearing or reading official publications about SSIS. but even so.

    i am an Experinaced proggramer. and maybe thats why i have so many complaints. becouse i beeen there-done that.

    and if write code (which is what  a programer do mostly) is the norm then by all means let me do that in a decent way in ssis. i dont mind writing code, or customising objects/commponents. but i do ask for the right tools and editors to work like that.

     

     

     

     Duane Douglas wrote:

    creating a robust ETL solution with microsoft tools "should" be faster using ssis than without it.   however, i believe that significantly more learning time than several hours is required for anyone to become proficient enough with ssis for this to be possible

     

    well i totaly agree.

    i spent 3 weeks learning ssis before i even started developing for production.

    iv read dozens of blogs, forums, articls, and documentations.

    and followed every tutorial about ssis i could find.

     

    but only when i started working for production and with real life scenarios  did i encountner most of my problems with ssis.

    i found myself getting stuck every few minutes.

    forced to go ask question is forums like a novice, for things i thought should work out of the box. i found myself spending valuable development time on creating workarounds and tricks and bugs in the tool.

    and generaly feeling that with one hand ssis is telling me: "run go where you want fast, i will help" and with the other hand i felt ssis pulling my leg and trying to stop me from moving.

    and all of the above is coming from getting frustrated for not getting what i thought i was getting.

     

    OK yes ssis is usefull, it has many built in tasks and components that let you do many things fast.

    but that is  the exception, rather than the norm.

    for everyting else you need to go SLOW, somtimes very slow.

     

    maybe there isnt anything better out there. but in that case is that the excuse of not giving me the basics?

    becouse i am forced to work with this?

     

     

     

     

     

     

    perhaps i misapprehended your orginal post.  my understanding was that paths the only variable values in your solution that could change during deployment.  is that correct?

    Monday, June 09, 2008 4:43 AM
    Moderator
  • MatthewRoche, I was agreeing 100% with the original poster of this thread. 

    And, yes, for your small, one time solution of just needing to move some data, and doing some transforms, or what not, you could use SSIS.

    I'm talking about using it for a dynamic, robust software solution that needs more processing and functionality that SSIS can provide without having to constantly write my own code.  I'm talking about limitations and workarounds for the necessities of what my ETL would have to accomplish, and which have been posted by several other users.



    I love the truck comparison from the SSIS + Excel rant.

    I was also looking at SSIS to do simple, fast tasks with Excel (it looked cool and fast at first), and found that there were some serious limitations.  Things that Microsoft could have easily added were not there.  So, now I have to write a script to do it?  That's great if I am a VB programmer, and thankfully I am. 

    To workaround a lot of missing functionality within SSIS, you better have some programming background, or you are in trouble.  And, if I have to add all this missing functionality with writing VB code, it begs the question, "Why don't I just write my own utility to do the same work outside of SSIS? " 

    It does irk me that some of these CIOs and bigwigs tout SSIS, even though they have never used it.  They should let the developer choose the right tools to get the project done, instead of believing MS sales peeps.

    I appreciate you creating this thread, mansyno, and feel good knowing I'm not the only one who feels this way.


    Take care,

    Sal
    Monday, June 09, 2008 6:51 AM
  • i have other values that needs changing during deployment,

    i gave the paths as an example.

    but tho there is only one global path for all values, there are MANY variables and properties in the children pkgs that need to get thier values from the parent variables.

    its not about how to get the value from outside the pakeg in to the parent and from there to the child (that i can do via configuration files in the parent)

    its about getting the value sink to the child pkgs without needing to do it on a once per variable basis. so if i have 100 variables in the children pgks that all of them need to get the single value from the parent i could do it all in just one configuration of type "parent variable" and not make one for each variable

    as i understand it there are to distincts uses for configurations.

    1. is to to supply a way to get values from outside the pakcge so that certain values could be set with out going inside development studio. (for example to have a DBA of the production machine be able to change them)

    2. is to supply a way for child pkgs to retrieve values that are stored inside parant pkgs.

     

    its that  2nd use that i have problems with.

     

     

    the way i belive it should work is that inside the child configuration. when you select the type of "Parent packege variable".

    that instead of just option to selct just one variable form the parent and asign it to a child property, there should be an option to select more then one and map it to a child property. similiar to the way we use column mappping in other data flow tasks.

     

    hope i made myself clearerSmile

     

    after thinking again......

    i figured i can set one variable in the child to get the value from the parent and then inside the child pakg use it for other variable inside the child pkg that needs it.

    but what if i have many pkgs?  or like i said above, what if i have more values in the parent i wish to use in the children?

     

    Monday, June 09, 2008 7:21 AM
  • mansyno,

     

    let's assume that i have a parent package which has 100 child packages.  if each child package needs the same value from the parent, then i could have each package retrieve that value from outside of ssis.  for example: a parent package can write a value to a file, and each child package can retreive this value from the same file.  as a matter of fact, i could put this logic in an assembly and have each child package use the same assembly via the script task.  futhermore, i could have a ready made child package template to use when creating the child packages.

     

    as i mentioned above, one of the features that makes ssis so appealing is its flexiblilty.  i'm not restricted to using any particular built-in feature of ssis -- the only limitation of ssis is my imagination.

     

    Monday, June 09, 2008 7:57 AM
    Moderator