Any better tools to develop packages (3rd pty)?

  • eddie

    SSC Enthusiast

    Points: 156

    Hi - I am continuing to hang my EM sessions when trying to define a transformation on a very large DB (a SAP DB with 15,000 tables). All I want to do is copy 15 tables routinely into another db for analysis services, but when trying to define this DTS wants to read all table information and hangs. I have 1.5gig of RAM, and a strong dual xeon 700 machine, but it just doesn't want to work (even after 20 minutes).

    Does anyone have any tips or links to a tool that will let me explicity define the tables rather than provide me a list from the entire DB?

    Any help is greatly appreciated.

    eddie


    eddie

  • Andy Warren

    SSC Guru

    Points: 119686

    I havent seen anything quite like that, but how about this:

    1) Script the tables you want to copy into a new db.

    2) Use the designer to build your package.

    3) Edit to point to the real db, or just save out as VB code and change there.

    EM is NOT great with a lot of objects, or a lot of DB's either! Query Analyzer ends up being a better choice for a lot of stuff because of it.

    Andy

  • eddie

    SSC Enthusiast

    Points: 156

    Thanks for the answer! I have the scripts already (thankfully) but ran into a codepage difference (the SAP 7.0 is stuck on CP 850 from the 6.5 upgrade to 7.0 and the new install is at CP 1252).

    Seems a shame to have to install again...

    eddie


    eddie

  • Andy Warren

    SSC Guru

    Points: 119686

    If you're using SQL2K you could just change the collation to match the old one.

    Andy

  • eddie

    SSC Enthusiast

    Points: 156

    I'm glad I caught your reply. I'm having a tough time with this one. SAP's SQL install chose some rather non-default settings that are hard to deal with.

    Are all of these codepage, locale ID, sorting differences transparent to DTS, or is that going to create any issues in actually running the packages across these servers? (I can't find much documentation on this even at SQLDTS.COM - maybe I am asking the wrong way AND I haven't been successful at completing the design so far).

    Thanks for your help on this by the way.

    eddie


    eddie

  • Andy Warren

    SSC Guru

    Points: 119686

    Honestly I dont know:-) I think the package will run ok, the question is how will the data look. You might want to pull some data out of the db in question and run it through a test package.

    Brian - any experience with this?

    Andy

  • Brian Knight

    One Orange Chip

    Points: 27249

    quote:


    Are all of these codepage, locale ID, sorting differences transparent to DTS, or is that going to create any issues in actually running the packages across these servers?


    DTS does not care about collation since it uses OLE DB. Even the copy SQL Objects task doesn't care about collation.

    BTW: are you using the Import/Export wizard to accomplish this task? There are some interesting stubborn bugs with transfering data through this.

    Brian Knight

    bknight@sqlservercentral.com

  • eddie

    SSC Enthusiast

    Points: 156

    I'm trying to setup some data copy routines in DTS Local and then scrub for OLAP. Most of the data I need is currency and integers, so I don't forsee any issues (but I'm learning here as I go!). I used Ex/Imp Wizard for the initial data load, but ran smack into the codepage and other issues when trying to restore or attach the db to the other server (ran from main db to a temp db on the same box - then moved to second box). It ran OK but that will not be practical. Of course replication did not like the CP differences!

    Thanks for the answer on the DTS! That will save some painful testing time.

    eddie


    eddie

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

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