Hi Experts

I have written a dts exporting data from SQL to Excel and the data is
exported fine, however when I save the dts as VB and run it from VB, it just
exports the headings of the table into Excel, but does not export the data in
the table. What am I doing wrong?

I know I have data in the table because after running the dts from the
wizard, I open the Excel file and the data is all there. However when I open
and run it in vb there is only the headings.

Thanx for all your help in advance

Kind Regards

"poppy" wrote:

Hi again

I just stumbled on the fact that I forgot to include the password to the db
in the code and that is why I was not recieving the data from the table.

I have another question. It seems from the code that data can only be
manipulates as a group.

e.g. Set oColumn = oTransformation.SourceColumns.New("Discount", 13)
oColumn.Name = "Discount"
oColumn.Ordinal = 13
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 131
oColumn.Precision = 10
oColumn.NumericScale = 2
oColumn.Nullable = True

I wonder if it is possible to manipulate data in every record? e.g.

Why are you running the package like this? All you are doing is
rebuilding a package each and every invocation and then firing the
package. This will hurt for performance. If I is because you want to
manipulate properties of the package at runtime then you could probably
do this using a prebuilt package and calling that through VB.

Once you have that then you can change properties of the package in your
code. You could also do this using the Dynamic Properties task or by
passing in variable values on the cmdline through the /A switch.



"poppy" <poppy (AT) discussions (DOT) microsoft.com> wrote

Hi Allan

This is cause I don't know any better. In fact I started working with dts
yesterday. I'm actually trying to help a collegue of mine. He says he has a
number of dtss in sql and he wants to be able to store and fire them all in
vb with out the object model? (not quite sure about this) He also wants to be
able to manipulate each record when and if he needs to.

How would I go about doing this: "prebuilt package and calling that through
VB.
Once you have that then you can change properties of the package in your
code. You could also do this using the Dynamic Properties task or by
passing in variable values on the cmdline through the /A switch."

Thanx for all your help

Kind Regards

"Allan Mitchell" wrote:

If they do not want to execute it using the Object Model then rebuilding
the package every time is not fulfilling that wish then.

Use DTS Designer and build the package. Anything you need to set
dynamically then you can have the package read from external locations
using the Dynamic Properties task. You could also set properties in the
package using Global Variables and set those Global Variables using the
/A switch to DTSRUN.

You can find all kinds of examples of doing things in DTS on this site.
Try this article for a start

Building a Package in the DTS Designer
(http://www.sqldts.com/default.aspx?278)


"poppy" <poppy (AT) discussions (DOT) microsoft.com> wrote