executing dts package through asp.net creates tables but no data!

hey out there,

just started playing with this so bare with me...

i've used the data export wizard to create a dts package in sql server 2000. the package takes selected tables, creates an access database and then dumps all the data into the tables.

it works fine when i run the package in enterprise manager, but when i call it from asp.net (vb.net) the access database gets created, the tables are created but it fails to dump the data!

this is my code:

1Public Sub executeDts()23Dim oPkgAs DTS.Package24 oPkg =New DTS.Package25Dim oStepAs DTS.Step6 Dim sMessageAs New StringBuilder789 oPkg.LoadFromSQLServer("serverNameHere","userNameHere","passwordHere", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , ,"packageNameHere")1011For Each oStepIn oPkg.Steps1213 sMessage.Append("<p> Step [" & oStep.Name &"] ")1415If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_FailureThen1617 sMessage.Append(" failed<br>")1819Else2021 sMessage.Append(" succeeded<br>")2223End If2425 sMessage.Append("Task """ & oPkg.Tasks.Item(oStep.TaskName).Description &"""</p>")2627Next2829 Response.Write("sMessage = " & sMessage.ToString &"<br/>")3031 oPkg.Execute()3233 oPkg.UnInitialize()3435 oPkg =Nothing3637 End Sub38

am i missing a step?! it seems very odd that the tables are created but the insert fails...

any advice anyone can offer would be great!

cheers,

jake

hi jake the code is perfect but there is one tiny little error

just put the DTS.Execute() statement before theFor loop block

this should solve your problem

take care brother

happy coding

|||

hi jake

the code is perfect but there is one tiny little error

just put the DTS.Execute() statement before theFor loop block

this should solve your problem

take care brother

happy coding

sql

Execute Integration Package by SP

Hi,
I'm writing a ASP.NET application, and I would like to export data from SQL
Server 2005 to MS-Excel. I know that the Integration Service in SQL 2005
has replaced the DTS in SQL2K. I'm wondering how I can execute the
Integration package from either ASP.NET or by means of Stored Procedures.
Thank you.
Regards,
JanetWell, I have not pkayed with this issue in SQL Server 2005 ,however , can
you create a linked server to EXCEL or using OPENDATSOURCE
"Janet >" <<unknown> wrote in message
news:OoNzgaYfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm writing a ASP.NET application, and I would like to export data from
> SQL Server 2005 to MS-Excel. I know that the Integration Service in SQL
> 2005 has replaced the DTS in SQL2K. I'm wondering how I can execute the
> Integration package from either ASP.NET or by means of Stored Procedures.
> Thank you.
> Regards,
> Janet
>

Execute installation script.

Hello. I will export the database generated script for all objects, I want to make an installer that executes that script on the remote server, I think the installer must ask for sa password; anyway thats not the problem, How can I make with SMO execute an script file?

Thanks

SMO expects you to either use the SMO object model to modify the database interactivly or else script the changes and run them externally later. If you are just executing SQL script, you can use sqlcmd.exe for that.

For something fancier that gets passwords and other user input and then runs the script, you would need to write a wrapper script/application to gather the user input and then send it off to the server.|||If you see DotNetNuke when you navigate to the first page it takes a lot of time, that page creates all the objects on the database taking as input some .sql file that are already on the package. However its in .net 1.1 and not 2.0 and I want to use SMO.

It seems to be very difficult because I havent find good info about this.

Tks|||Use Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery Method with your script in parameter
(at first you must create a database)|||How can I Pass this method a .sql file?|||You can do something like:

using System.IO;

~~~~~~~~~~~~~~~
Server srv = new Server("MyServer");
string filePath = "c:\\create.sql";

FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

StreamReader sr = new StreamReader(file);

string s = sr.ReadToEnd();

sr.Close();

srv.Databases["tempdb"].ExecuteNonQuery(s);

|||I use this:

string script = System.IO.File.ReadAllText(file);

Execute installation script.

Hello. I will export the database generated script for all objects, I want to make an installer that executes that script on the remote server, I think the installer must ask for sa password; anyway thats not the problem, How can I make with SMO execute an script file?

Thanks

SMO expects you to either use the SMO object model to modify the database interactivly or else script the changes and run them externally later. If you are just executing SQL script, you can use sqlcmd.exe for that.

For something fancier that gets passwords and other user input and then runs the script, you would need to write a wrapper script/application to gather the user input and then send it off to the server.|||If you see DotNetNuke when you navigate to the first page it takes a lot of time, that page creates all the objects on the database taking as input some .sql file that are already on the package. However its in .net 1.1 and not 2.0 and I want to use SMO.

It seems to be very difficult because I havent find good info about this.

Tks|||Use Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery Method with your script in parameter
(at first you must create a database)|||How can I Pass this method a .sql file?|||You can do something like:

using System.IO;

~~~~~~~~~~~~~~~
Server srv = new Server("MyServer");
string filePath = "c:\\create.sql";

FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

StreamReader sr = new StreamReader(file);

string s = sr.ReadToEnd();

sr.Close();

srv.Databases["tempdb"].ExecuteNonQuery(s);

|||I use this:

string script = System.IO.File.ReadAllText(file);

Execute Excel macro when exporting in SSRS

We have a report that users typically export to Excel. I've created
a
macro in Excel that I run against the the exported report to do some
follow up work. Unfortunately, this requires that I export the
report
myself, run the macro and forward on to those interested.
I'm looking for a way to execute the macro automatically every time
someone exports to Excel (or sets up a subscription that sends Excel
output). Any ideas?
If there is a way to do this with a custom DLL, etc. we would be
interested in hiring a consultant to complete the task.
Thanks for you help
IANOn Oct 2, 8:48 pm, IAN <iani4pro...@.gmail.com> wrote:
> We have a report that users typically export to Excel. I've created
> a
> macro in Excel that I run against the the exported report to do some
> follow up work. Unfortunately, this requires that I export the
> report
> myself, run the macro and forward on to those interested.
> I'm looking for a way to execute the macro automatically every time
> someone exports to Excel (or sets up a subscription that sends Excel
> output). Any ideas?
> If there is a way to do this with a custom DLL, etc. we would be
> interested in hiring a consultant to complete the task.
> Thanks for you help
> IAN
In terms of exporting the report to excel via subscription, if you are
not aware, this is built into the Report Mgr. Also, a report can be
exported to Excel via URL. Here's an example:
http://ServerName/reportserver?/DirectoryNameWhereApplicable/ReportName&rs:Command=Render&Param1=ParamValue&rs:Format=Excel
This opens an export dialog that defaults to the Excel format. Another
option that can most likely be used in conjunction w/Excel
manipulations (known as Automation) is utilizing the web service
available w/SSRS and the Report Server. An example link is found here:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||If you're talking about using the standard interface, I'm pretty sure the
answer is no. It generates a new copy of the file and that's it. Of course,
you could always build your own aspx page that generates the xls file and
then uses the excel object model to do whatever you want. An option for
subscriptions would be to send the xls file to a share, and have a .net app
that is looking at that folder (filesystemwatcher) and runs whatever code
you need when a new xls file shows up in the directory.
Mike G.
"IAN" <iani4profit@.gmail.com> wrote in message
news:1191376082.251031.190910@.n39g2000hsh.googlegroups.com...
> We have a report that users typically export to Excel. I've created
> a
> macro in Excel that I run against the the exported report to do some
> follow up work. Unfortunately, this requires that I export the
> report
> myself, run the macro and forward on to those interested.
> I'm looking for a way to execute the macro automatically every time
> someone exports to Excel (or sets up a subscription that sends Excel
> output). Any ideas?
>
> If there is a way to do this with a custom DLL, etc. we would be
> interested in hiring a consultant to complete the task.
>
> Thanks for you help
>
> IAN
>

Execute Excel macro after export

We have a report that users typically export to Excel. I've created a
macro in Excel that I run against the the exported report to do some
follow up work. Unfortunately, this requires that I export the report
myself, run the macro and forward on to those interested.
I'm looking for a way to execute the macro automatically every time
someone exports to Excel (or sets up a subscription that sends Excel
output). Any ideas?
If there is a way to do this with a custom DLL, etc. we would be
interested in hiring a consultant to complete the task.
Thanks for you help
IANI'm currently working on a DLL to do exactly this. We execute a macro to
strip out hyperlinks because the excel renderer doesn't have an
OmitHyperlinks option.
Contact me if you are still interested.
Raul
"iani4profit@.gmail.com" wrote:
> We have a report that users typically export to Excel. I've created a
> macro in Excel that I run against the the exported report to do some
> follow up work. Unfortunately, this requires that I export the report
> myself, run the macro and forward on to those interested.
> I'm looking for a way to execute the macro automatically every time
> someone exports to Excel (or sets up a subscription that sends Excel
> output). Any ideas?
> If there is a way to do this with a custom DLL, etc. we would be
> interested in hiring a consultant to complete the task.
> Thanks for you help
> IAN
>