Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2007
    Posts
    31

    Unanswered: Handling Empty Strings In DTS

    Hello,
    I have a transformation in which the column of data at the flat file source is nine characters long, and typically contains a string of six or seven zeros with a non-zero number in the last two or three characters. If none of the records in that column were an empty string, I think I could get away with this:

    DTSDestination("TTLCrd") = CInt(DTSSource("Col004"))

    The destination is a SQL Server 2000 table, and the column is of type Integer. What do I do when Col004 is an empty string? I've tried a couple of different IF statements, but they have not worked. Empty strings need to become zero values.

    Thank you for your help.

    cdun2

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Avoid using DTS to insert directly into production tables.
    Avoid putting logic or code that manipulates data in your DTS package.
    Use DTS for moving data from point A to point B. All the other features of DTS (or the new SSIS) are crap, and lead inevitably to bad application design.

    Best practice is to use DTS to pipe your data to a staging table and then kick off a stored procedure to process the data in the staging table, verifying and cleansing the data before pushing it into the production tables. The stored procedure will hold all of the data logic, including the COALESCE() function, which will easily convert your NULL values to zeros.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2007
    Posts
    31
    Thank you for your response!
    cdun2

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by blindman
    Best practice is to use DTS to pipe your data to a staging table and then kick off a stored procedure to process the data in the staging table, verifying and cleansing the data before pushing it into the production tables. The stored procedure will hold all of the data logic, including the COALESCE() function, which will easily convert your NULL values to zeros.
    So what you say is that you should have staging tables (potentially with all nvarchar field if you for instance import from text files), generate a whole lot of disk activity, before you kick off stored procs to do all the work for you? I cannot see why one would want to do it that way. I'm pretty satisfied with the way SSIS work, and would very much like to know why you discourage use of the SSIS features.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm totally with blindman on this. My question in turn would be why would you want to incorporate data\ business logic into discrete, proprietry DTS\ SSIS packages? Virtually everything else I do in T-SQL unless there is no alternative. Even if I used DTS or SSIS at all it would be to get stuff from outside the database to inside it with as little fuss as possible - nothing more. I see nothing gained throwing these ETL tools at the problem when the standard SS language is perfectly capable of everything I have come across so far.

    You probably know though that I go further even than blindman and do not use DTS or SSIS at all.

    As far as the disk usage is concerned, from my perspective I work with large batch systems with well specced servers. We are not on a big time or resource pressure when we load so for me it is not a consideration.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    KISS.
    By not creating code in my DTS package, I keep all of my logic in one place(the database) and in one language(SQL). That makes debugging much easier.
    It also compartmentalizes my process, meaning I could use DTS, or BCP, or SSIS, or ASP, or a friggin' Access macro to load data, and my sproc will process it. I can even have multiple data flows going into the staging table.
    My staging tables contain columns that record the source of each record, the time it entered the database, and a column for recording any processing errors. As my sproc cleanses, verifies, and loads the staging data, any discrepancies are noted within the ErrorStatus column. At the end of the process finding any records that failed and the reasons for their failure is a snap, and all I need to do is fix the existing staging records and reset the ErrorStatus column, and then I can rerun the sproc.
    ETL has become bread and butter to me now. I can almost write these sprocs and packages with my eyes closed.
    ROAC, how easily did your DTS packages upgrade to SSIS?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by pootle flump
    As far as the disk usage is concerned, from my perspective I work with large batch systems with well specced servers. We are not on a big time or resource pressure when we load so for me it is not a consideration.
    Well, I see. In that case I would do the same. However, there are many MANY companies around, especially in smaller countries, that cannot afford this kind of systems. The gap between for instance an EVA4000 and EVA6000 is huge for many companies, and they have to take disk performance into consideration.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by blindman
    KISS.
    ROAC, how easily did your DTS packages upgrade to SSIS?
    Agreed. Keep things simple. If you are having a lot of data sources, I find it more easy working with SSIS than having tons of procs.

    When it comes to upgrading, my DTS packages upgraded pretty well, but I know quite a few that did not, which of course is an issue. However, with almost the same arguments you come up with, you could tell to do the work in ASP.NET or Java as well, and have issues when .NET Framework or Java language is upgraded. Or, for that matter, when SQL Syntax changes.

    As I said in my last post, please keep in mind that there are smaller countries and companies in the world. What's best for an enterprise is not neccessarily best for a small or medium sized business in Scandinavia or the Baltics. Thus, I think your advices perhaps should be something like "If you can afford ..., you should ...". Got my idea?

    I have no problem seeing your points, I just cannot see it as the only solution.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by roac
    Well, I see. In that case I would do the same. However, there are many MANY companies around, especially in smaller countries, that cannot afford this kind of systems. The gap between for instance an EVA4000 and EVA6000 is huge for many companies, and they have to take disk performance into consideration.
    I might add that the organisation I worked for prior to this was certainly not large, nor specialist like my present company, but I stuck to the same philosophy then. Actually - I think I just did

    The most expensive resource of all is the bum in the seat supporting the hands keying in the code. This bum is likely to know T-SQL if he\ she is working with SQL Server. Why is it cost effective to introduce a GUI based ETL tool into the mix when bog standard T-SQL is perfectly capable? The worst short term ROI an employer gets from me is when I am wrestling with a new language\ gui\ tool etc.. I think the time aspect of my point might be a pressure for using SSIS\ DTS but not the resources - I'm afraid I have no idea what the difference is between EVA4000 and EVA6000
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ROAC, why do you think my method of storing the data in staging tables and then running a sproc against them is going to entail more disk activity/server resources than an SSIS package?
    Presumably the amount of data being imported is some fraction of the data that already exists in production, so if the server is beefy enough to handle day-to-day processing it should not choke on running sprocs against staging data. Especially since these are normally run as batch processes during maintenance hours.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    As the data is written do disk twice instead of once, yes it WILL consume more disk resources. If you are lucky enough to do all the job at night, well then you are definitely more lucky than I am. As I said previously, if you can have the extra disk load, your approach is the best. I'm not questioning that. I just want to make it clear, for you and other reders, that your scenario is not the only one around. Other people may have other needs, which will lead to other solutions. Doubling the disk activity required to import data is not always an option. You are lucky enough to have that option, but is it so hard to believe that other people not neccessarily have the same situation as you?

    I think you perhaps should open your eyes a bit and look around, because there are solutions out there, behaving quite differently from those you are working with.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Disk activity, hmmm..
    Well, as a consultant who has created production and data warehouse ETL solutions for dozens of companies in many industries I can't say I've run into a situation where that was the deciding factor in the application architecture. But I'll grant such a situation may be possible.

    Obviously I don't think my method is the only way to go. There are certainly many implementations using DTS, SSIS, or 3rd party tools (I recently had to suffer through a project where the client used a tool called DataStage).

    The problem is, too many inexperienced people jump into creating DTS/SSIS solutions simply because THEY assume that THAT is the only method. When in reality (and I'm not backing down on this), these GUI tools are SELDOM the best method and lead to fragile designs that are difficult to debug or modify.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - Roac - totally agreed that everything deserves evaluation in the context of the entire circumstance. There are no absolutes - that is why we are having this fun discussion where we are sharing our opinions
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Its all good. <\TouchGloves>
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - Roac - I've just remembered my first post addressing you was when I thought you had made a somewhat absolute statement too. What goes around comes around eh? On that one you were in agreement with blindman.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •