Results 1 to 12 of 12

Thread: DTS issues...

  1. #1
    Join Date
    Apr 2004
    Location
    Jersey
    Posts
    9

    Unanswered: DTS issues...

    When executing a DTS Package from an ASP page, the steps of the package are executed in the wrong order, but it's the same wrong order every time. Any ideas?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What doe sthat mean?

    They could thread out, depending on how you coded it?

    Where in Jersey?

    http://members.tripod.com/~mugsy11/heyrube.html
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    how does it run locally?

  4. #4
    Join Date
    Apr 2004
    Location
    Jersey
    Posts
    9
    Meaning that the steps in the package are coded correctly. I create a table, then populate the new table, then export to an excel file. Running it from Enterprise Manager works like a champ every time, but when running it from an ASP page, it always tries to populate before creating.

    Now after trying to tweek a few things, my code is screwy and I'm get server errors. I'll get it sorted out and post the exact errors I was getting.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does it have to be excel?

    Can it be csv?

    How about a sproc...

    Even if it needed to be a xls, you could use a com object I guess...

    I've seen DTS do some weird stuff...especially if this is a versioned package...

    Can you recreate a brand new package and try it?

    I know this is a stupid question, but you do have workflow steps in there...right?

    I'd still go with a sproc.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2004
    Location
    Jersey
    Posts
    9
    Originally posted by Brett Kaiser
    Does it have to be excel?

    Can it be csv?

    How about a sproc...

    Even if it needed to be a xls, you could use a com object I guess...

    I've seen DTS do some weird stuff...especially if this is a versioned package...

    Can you recreate a brand new package and try it?

    I know this is a stupid question, but you do have workflow steps in there...right?

    I'd still go with a sproc.....
    Yeah, the workflow steps are in there, and you just answered my next question. I was thinking about calling a sproc to execute it, so now that you said that, I'm going to give that a try.

    And I'm in the south Jersey area by the way. Erial to be exact.....about 5 miles south of Deptford.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    When you said south...you ain't lion

    How far to AC?

    http://www.mapquest.com/maps/map.adp...submit=Get+Map

    Flyer fan?

    Come on, you can admit it....

    Go Devils....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, and let me ask...

    Do you or someone you know own a confederate flag?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2004
    Location
    Jersey
    Posts
    9

    Wink

    Originally posted by Brett Kaiser
    When you said south...you ain't lion

    How far to AC?

    http://www.mapquest.com/maps/map.adp...submit=Get+Map

    Flyer fan?

    Come on, you can admit it....

    Go Devils....
    I'm 20 minutes or so from AC depending on how heavy my foot is that day. And I'm not a fan for ANY Philly team. I'm from the midwest originally, so that's where my loyalties are. And as far as hockey goes, I'm more of a Redwings fan than anything I guess, but the Rangers are pseudo-cool.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    A transplant TO NJ?

    There's a switch....



    btw, in your sproc I'd use xp_cmdshell and ftp to get the data out....

    How/ why are you creating this table?

    What the data source?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2004
    Location
    Jersey
    Posts
    9
    Yeah. Was in the Navy for 9 years and got dropped off on the east coast and just stayed. It's a long story. And every long story involves a woman, so I'll leave it at that for now.

    Every month, our affiliates require a report(xls) that contains referrals, items purchased, costs and prices, etc. It's a different query for every affiliate and until recently, the person in charge of this was running each query in QA and then saving the results to an xls file. When this duty was passed to me, I quickly decided that the was NO way that I was going to do it all manually like that. So I put together the DTS package that works just fine for me because I have all the permission and rights that a person could have, so I would just run the package once a month.

    Soon after, the powers that be, who also have large plush offices and are technical idiots, decided that they wanted to be able to check this data when and where ever they want to, so I added a link to our intranet, and that's where my problem started.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, I set that up for 1 project...

    Each report has it's own sproc...

    Each report is in a report table which contains report names, sproc names, if it's batch or online (batch save it to a file location, online is saved to their local through a browswer)

    The browser executes a job when they select a report that runs the sproc.

    That's important becuase we found if the sproc took a whil, the browser would hang...basically started another independant thread....

    The report sprocs would create csv files with a header...like..

    Code:
    Select		  '"'+LTrim(RTrim(IsNull(SignOffEntity,'')))+'"'
    		+',"'+LTrim(RTrim(IsNull(LedgerSumAmount,'')))+'"'
    		+',"'+LTrim(RTrim(IsNull(LedgerNumOfRows,'')))+'"'
    From 
    (       Select	  0 As SQLGroup, 'ATS Sign-Off Entity' As SignOffEntity
    		, 'Ledger Balance' As LedgerSumAmount, 'Ledger Count' As LedgerNumOfRows
         Union All
            Select	  1 As SQLGroup, a.SignOffEntity
    		, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
    		, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
    	  From	 ATS_SignOff_Entity a (NoLock)
         Left Join   (Select Entity, IsNull(Amount,0) As Amount 
    		    From Ledger_Detail c (NoLock)
                       Where Account_Type In ('4', '5', '6', '7')
    		   Union All
    	          Select Entity, IsNull(Convert(money,BOY),0) As Amount 
    		    From tblAcct_LedgerBalance d (NoLock))  b
    	    On	 a.Entity = b.Entity
          Group by	 a.SignOffEntity
         Union All
            Select	  2 As SQLGroup, 'TOTAL'
    		, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
    		, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
    	  From	 ATS_SignOff_Entity  a (NoLock)
         Left Join   (Select Entity, IsNull(Amount,0) As Amount 
    		    From Ledger_Detail c (NoLock)
                       Where Account_Type In ('4', '5', '6', '7')
    		   Union All
    	          Select Entity, IsNull(Convert(money,BOY),0) As Amount 
    		    From tblAcct_LedgerBalance d )  b
    	    On	 a.Entity = b.Entity
    ) As A


    Notice that it's just 1 column, because all of the columns are concatenated...notice too the column conversions to char where needed....

    Also notice the trailer or total row...they're kept in the correct order by the SQLGroup Column....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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