Hi,

I need to be able to query the database within a Script task. I basically
need to get the current connection and then execute some sql or stored proc
and find out what's in the database and based on this info make a decision in
the script. Does anybody know how to do this?

I've tried few things but no luck, fairly new with .net. This is all sql
server 2005 ssis package.

Thanks,

Panos

Hello Panos Stavroulis.,


Why do you need to do this in a Script task? SSIS is far more flexible than
DTS and you can probably do what you want without doing it through a Script
task.




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hi Allan,

Thanks for this. Well what I want to do is query the database, find out if a
file exists there then if not then copy the file to a dir for processing. I
know I can do this in a sql task but then I'll have to store the results,
possibly hundreds into a variable. Then I can retrieve the results of the
variable one by one?.

So do you think it's better doing that and storing the result sets into a
variable? would you suggest Object as the best type to use for my container
variable?

Thanks,

Panos.

"Allan Mitchell" wrote:

Hello Panos Stavroulis.,

When you say a file do you mean a table?

A variable can hold a rowset so you can hold more than one value in there.
With your method how would you not have to query the DB multiple times.
If your method would not require this then neither does the ExecuteSQL task
way either


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

No I mean a file on the drive. So have to ckeck if the file name exists in
the database then returnn back the result. It will have to be done with
200-300 calls to an sp can't be done with just one sp call. That's why I
wanted to call the sp within a script and not within a sp task.

Then if a file needs further processing has to be stored in a global
variable for use by other tasks. So what I need is a variable which will
contain file1.txt, file2.txt etc. Hope it makes more sense now. Thanks.

"Allan Mitchell" wrote:

Hello Panos Stavroulis.,

There will be no difference then.

You could loop over the files and then do with that info what you will.
I really see no difference in either solution.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com