Not sure about this...

[dBjason]

Hello,

I have a strored procedure which creates a table dynamically on the fly. Sometimes it has 10 columns, other times it can have 100.

The stored procedure is supposed to kick off a DTS package to export the table into an Excel file. Myself, being no expert on DTS packages, am not sure how it all works but I do know the package is experiencing 1 failed step. I beleive it's because of the dynamic columns. Currently I'm creating a new DTS package via the wizard to solve this problem, however I do not know if the package will run the next time the procedure triggers it as the number of columns will be different.

Unfortunately this process is only run once per year (with the year as criteria), so trying to test under different conditions is impossible until after Jan 1, 2006. In other words, I have no idea if this package will work next year when the table will have a different number of columns.

Can anybody give me some insight or advice on how to handle this dilemma?

Thanks in advance,
Jason

 

[mrdenny]

When setting up a DTS package to export data the DTS Package needs to know the number of columns. You can change this information dynamically by using the dynamic settings task to reset this information before the export step executes.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com