EXECUTING PACKAGES from vb.net

Hello:
I have a web application from where I need to execute a DTS,
my platform is SQL 2000 and .net 2003. From a Vb.net module I want to
execute the package, my question is: does the package need to be STRUCTURED
STORAGE FILE or could it be SQL SERVER package (local to the server) in
order to be executed from .NET.
- If I can execute the local SQL package , how can I do it?
- If I can only execute STRUCTURED STORAGE FILE, how can I get ride off of
the multiple versions that are created everytime the package is opened and I
make any update on it.
ThanksGina Hernandez wrote:
> Hello:
> I have a web application from where I need to execute a DTS,
> my platform is SQL 2000 and .net 2003. From a Vb.net module I want
to
> execute the package, my question is: does the package need to be STRUCTUR
ED
> STORAGE FILE or could it be SQL SERVER package (local to the server) in
> order to be executed from .NET.
> - If I can execute the local SQL package , how can I do it?
> - If I can only execute STRUCTURED STORAGE FILE, how can I get ride off o
f
> the multiple versions that are created everytime the package is opened and
I
> make any update on it.
> Thanks
>
I assume you could go both ways, or you could use the dts com class in
.NET, without using a storage file or an sql package.
Steven|||> execute the package, my question is: does the package need to be
> STRUCTURED STORAGE FILE or could it be SQL SERVER package (local to the
> server) in order to be executed from .NET.
To expand on Steven's response, you can use the DtsPackage LoadFromSqlServer
or LoadFromStorageFile methods depending on the package location.

> how can I get ride off of the multiple versions that are created everytime
> the package is opened and I make any update on it.
If you delete the file before saving (or save to a new name and rename),
only the current version is saved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gina Hernandez" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uIHXLnlBGHA.3156@.TK2MSFTNGP12.phx.gbl...
> Hello:
> I have a web application from where I need to execute a DTS,
> my platform is SQL 2000 and .net 2003. From a Vb.net module I want
> to execute the package, my question is: does the package need to be
> STRUCTURED STORAGE FILE or could it be SQL SERVER package (local to the
> server) in order to be executed from .NET.
> - If I can execute the local SQL package , how can I do it?
> - If I can only execute STRUCTURED STORAGE FILE, how can I get ride off
> of the multiple versions that are created everytime the package is opened
> and I make any update on it.
> Thanks
>

Executing DTS package from VB.NET

Hello,

I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:

Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)\n

I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?

Any ideas?

BrianHey,

It seems the error is because the steps aren't being executed in the right order. The steps are being sorted by name so to speak, as the execute SQL's are being fired first, then the dynamic task, then the pump tasks, etc. Is there anyway around that, either in the package or through code?

Brian|||The tasks in a DTS package will attempt to execute all at once unless you've established a workflow. With a workflow, the next task won't execute until the previous task has completed. Try this MSDN article and see if you can locate what you're missing:DTS Package Workflow.

Terri

Executing a stored procedure that uses linked server from vb.NET

I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET
MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"
MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()
The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)
I can run the stored proc on in QA on my MS SQL with no prob.
Must i open connections to the 2 linked servers from .NET as well?
Glad for any opinions so i dont have to rewrite my sp,
Mikaelbermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?|||> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?
> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>> How long does it take to run in QA, and how long is your timeout set for
>> in .NET?
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M|||bermik wrote:
> > I'm no .NET programmer, but with typical web apps you have a CONNECTION
> > timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> > So, in a nutshell, if your query takes longer than 30 seconds, the web
> > connection is going to give up and report a timeout.
> Not a web app, but still makes sense, will check the times. My test
> procedure worked out fine so it should have something to do with the
> load and not linked server, thanks!
> /M
...and timeout it was thanks for your help!
(MyBase.cmdTimeout = 15000)

Executing a stored procedure that uses linked server from vb.NET

bermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?
> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?

> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||
> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M|||bermik wrote:
> Not a web app, but still makes sense, will check the times. My test
> procedure worked out fine so it should have something to do with the
> load and not linked server, thanks!
> /M
...and timeout it was thanks for your help!
(MyBase.cmdTimeout = 15000)|||I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET
MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"
MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()
The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)
I can run the stored proc on in QA on my MS SQL with no prob.
Must i open connections to the 2 linked servers from .NET as well?
Glad for any opinions so i dont have to rewrite my sp,
Mikael|||bermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?|||
> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?

> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||
> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M

Executing .sql file from vb.net

Hi,

I have a .sql file that contains sql statement to create tables. Is there a way where I can execute the codes in this file creating the tables?

Thanks

EDIT

The code is in C# so use the code converter in the second link.

http://www.c-sharpcorner.com/UploadFile/mahesh/CreatingDBProgrammaticallyMCB11282005064852AM/CreatingDBProgrammaticallyMCB.aspx

http://www.carlosag.net/Tools/CodeTranslator/Default.aspx

|||

Hi,

Thanks for the link. Will try it out and post the outcome.

|||If it does not work it will be related to C# to VB conversion because I have helped a user clone a database and all the objects with ExecuteNonQuery.|||

Hi,

Yup the 'ExecuteSQL' did not seem to work, so I just removed that entire function and directly called an 'ExecuteNonQuery' and it worked fine.

Thanks!

Executing .sql file from vb.net

Hi,

I have a .sql file that contains sql statement to create tables. Is there a way where I can execute the codes in this file creating the tables?

Thanks

EDIT

The code is in C# so use the code converter in the second link.

http://www.c-sharpcorner.com/UploadFile/mahesh/CreatingDBProgrammaticallyMCB11282005064852AM/CreatingDBProgrammaticallyMCB.aspx

http://www.carlosag.net/Tools/CodeTranslator/Default.aspx

|||

Hi,

Thanks for the link. Will try it out and post the outcome.

|||If it does not work it will be related to C# to VB conversion because I have helped a user clone a database and all the objects with ExecuteNonQuery.|||

Hi,

Yup the 'ExecuteSQL' did not seem to work, so I just removed that entire function and directly called an 'ExecuteNonQuery' and it worked fine.

Thanks!

sql

Execute SSIS package on vb.net

Hi All,

I would like to write a small application for execute a SSIS package manually, but i got a error message as following:

Coding:
Dim oPkg As DTS.Package2
oPkg = New DTS.Package2

'Error Message Here
oPkg.LoadFromSQLServer("SQL2005", "sa", "abc", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, Nothing, Nothing, Nothing, "AccessImport")

oPkg.Execute()
oPkg.UnInitialize()

oPkg = Nothing

Error Message:
The specified DTS Package ('Name='AccessImport';ID.VersionID = {[not specified]}.{[not specified]}') does not exist.

So what is the problem here?
Thank a lot.

Laputa

You're using the DTS2000 object model.

You need the Microsoft.SqlServer.Dts.Runtime .Net namespace!

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9mref/html/N_Microsoft_SqlServer_Dts_Runtime.htm

-Jamie

|||Thx you help a lot of me !!|||I can't found the namespace Microsoft.SqlServer.Dts.Runtime