|
|
You're in the
sql server dts
group:SSIS: connect to the database within a Script task (vb.net)
sql server dts:
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 [quoted text, click to view] > 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 >
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. [quoted text, click to view] "Allan Mitchell" wrote:
> 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, > > > > 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.,
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 [quoted text, click to view] > 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., >> >> 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, >>> >>> 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 >>>
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. [quoted text, click to view] "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 > > > 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., > >> > >> 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, > >>> > >>> 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.,
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 [quoted text, click to view] > 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., >> >> 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 >>> 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., >>>> >>>> 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, >>>>> >>>>> 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 >>>>>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |