sql server dts:
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.
[quoted text, click to view] "poppy" <poppy@discussions.microsoft.com> wrote in message
news:poppy@discussions.microsoft.com:
> "poppy" wrote:
>
> > 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
>
> 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.
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)
[quoted text, click to view] "poppy" <poppy@discussions.microsoft.com> wrote in message
news:poppy@discussions.microsoft.com:
> 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:
>
> > 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.
> >
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
[quoted text, click to view] "poppy" wrote:
> 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
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
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
[quoted text, click to view] "Allan Mitchell" wrote:
> 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.
>
Don't see what you're looking for? Try a search.
