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?What doe sthat mean?

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

Where in Jersey?

http://members.tripod.com/~mugsy11/heyrube.html|||how does it run locally?|||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.|||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....|||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.|||When you said south...you ain't lion

How far to AC?

http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&submit=Get+Map

Flyer fan?

Come on, you can admit it...

Go Devils...|||Oh, and let me ask...

Do you or someone you know own a confederate flag?|||Originally posted by Brett Kaiser
When you said south...you ain't lion

How far to AC?

http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&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.|||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?|||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.|||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..

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...