Executing DTS from Stored Procedure in Sql Server 2000

Hai .....,
I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error message:(I Executed the procedure thru Query Analyzer window and got the following message)

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established
Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established

Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500

Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: A connection with the server could not be established
Error source: msxml3.dll
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL
Can anyone help me solve this problem please

I am with the same occurrence, but in a server windows 2003 server. Does anybody can me to help?

Guilherme

guilhermer@.correios.com.br

|||

Can you post the code you are running?

This works:

xp_cmdshell'dtsrun /SmyServer /NmyPackage /E'

|||DTSRun /S "smg0062" /N "DBTNT - BAIXA SRO" /W "0" /Esql

Executing DTS from Stored Procedure in Sql Server 2000

Hai .....,
I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error message:(I Executed the procedure thru Query Analyzer window and got the following message)

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established
Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established

Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500

Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: A connection with the server could not be established
Error source: msxml3.dll
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL
Can anyone help me solve this problem please

I am with the same occurrence, but in a server windows 2003 server. Does anybody can me to help?

Guilherme

guilhermer@.correios.com.br

|||

Can you post the code you are running?

This works:

xp_cmdshell 'dtsrun /SmyServer /NmyPackage /E'

|||DTSRun /S "smg0062" /N "DBTNT - BAIXA SRO" /W "0" /E

Execute SQL Insert Statement from Script Task using package OLEDB Connection

Is there a way to directly do this in one step(Execute SQL Insert Statement from Script Task using package OLEDB Connection)?

Right now I'm using a script task to build a sql insert statement using package variables (to fill values) populated by certain logic in the package.

Then assigning this command string to a package variable.

Then using a sql execute task to execute this variable.

A link to an article or code would be greatly appreciated.

You can use the classes in the System.Data.OleDB namespace and the ConnectionManager.AquireConnection method to do this. It is easiest to do this if you have an ADO.NET OLEDB connection, as that returns a managed object that you can use in your script task.

See jaegd's post in this thread for a sample. It shows use of the SQL Client, and it's a script transform rather than a task, but hopefully it's enough to get you started. If not, post back and we'll help you resolve any difficulties.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1895867&SiteID=1

Execute report and go directly to a save file option

We have a report that is basically a data file. It can have 2 rows or it can
have 100,000 rows. We are running into response time issues, memory issues,
etc. I know that RS is not the preferred method to deliver these sorts of
large data files. We need to do anything possible to satify the user at this
point and the amount of time it's currently taking to run this report with a
large amount of data, even when the report runs successfully, is unacceptable
to them. But, until we have time to re-design, is there any way to have the
report always prompt to save the file instead of waiting for the default HTML
format and then exporting to CSV? Any other short-term solutions?
StephanieFrom your own web page it is easy. Using URL integration you can specify the
format. Even if you are using Report Manager you could have a report that
has the appropriate parameters, they open it up and all you have in the
report is a link that says Export Data. Use the Jump to URL action (I also
make the link blue and underlined).
One other thing, perhaps you have seen this already.If you do this you will
want to change the CSV to use ASCII instead of unicode. Excel puts unicode
into a single column. To have RS export CSV in ASCII format you have to make
a change to a config file.
You only need to change in one place, rsreportserver.config. Reboot after
the change. The below shows commenting out the existing entry and putting in
the needed change to have CSV export as ASCII
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:10819562-B6BC-4535-BE4A-4B5AA6BD2B8A@.microsoft.com...
> We have a report that is basically a data file. It can have 2 rows or it
> can
> have 100,000 rows. We are running into response time issues, memory
> issues,
> etc. I know that RS is not the preferred method to deliver these sorts of
> large data files. We need to do anything possible to satify the user at
> this
> point and the amount of time it's currently taking to run this report with
> a
> large amount of data, even when the report runs successfully, is
> unacceptable
> to them. But, until we have time to re-design, is there any way to have
> the
> report always prompt to save the file instead of waiting for the default
> HTML
> format and then exporting to CSV? Any other short-term solutions?
> Stephanie