embedding a DTS package which contains a 'Copy SQL Server Objects Task'

Hi,

I am getting an error when doing the above.

I create a new SSIS package, drag in an Execute DTS 2000 Package Task, select and embed a DTS package which consists only one one task (as above), and then change the source & destination details (svr + user/pwd). Then when I go to the Copy tab, I get the following error when I hit Select Objects, to view the objects which the embedded DTS package should copy:

SQL-DMO error 21776: general error.

On further inspection, none of the objects selected for copy within the atomic/original DTS package, remain selected for copy within the embedded DTS package.

I have googled to search for an answer to this one, but to no avail. Any ideas would be greatly welcomed.

Thanks,

Tamim.

Off the top of my head I am not sure...but I wonder if there is more to this or perhaps what the real goal is?

I ask because if your only dealing with one transfer task from a dts package, then why not just create a new SSIS package using the new ssis transfer tasks? Prehaps you have 100s of said DTS packages, and you need to keep them going rather than rebuild all. However, we should always keep in mind that while the Execute DTS 2000 Package Task is nice for 'keeping things running' from the old version, over time you will want to rebuild any DTS pacakge as a 'native' SSIS 2005 pacakge allows you to do a lot more AND DTS is considered a deprecated technology, which does mean its fading form the picure. If you want you can read more about that here.

http://msdn2.microsoft.com/en-us/library/ms403408.aspx

Hope that helps.

|||

'...if your only dealing with one transfer task from a dts package, then why not just create a new SSIS package using the new ssis transfer tasks?...'

Sure. And that's exactly what I did, thus reducing the problem to being merely academic - but it's still there....

Thanks Craig,

Embedded Code Executing SQL

I'm trying to execute a stored procedure in the header section of a report
(so I can't use a dataset in the data tab) using the shared datasource of the
report. Does anyone have a simple example executing SQL from the embedded
code of a report?Bryan,
You can use the objects from the detail section to show in the header
or...try to have a select statement in the code behind function.
if u are okay with the first line, just pop up again, i will check and give
u the code
"Bryan" wrote:
> I'm trying to execute a stored procedure in the header section of a report
> (so I can't use a dataset in the data tab) using the shared datasource of the
> report. Does anyone have a simple example executing SQL from the embedded
> code of a report?

Emailing results from query

Hi,
I just installed sql05 ent version and set up the DB Mail and ran a test
mail without a problem. I also managed to create a job which would execute a
query. I'd like now the sql server send to several email addresses the
results of the query and here where I'm stuck.
HOWDO you do it?
TIA
Ana
Read about sp_send_dbmail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test mail without a problem. I
> also managed to create a job which would execute a query. I'd like now the sql server send to
> several email addresses the results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana
|||Hi,
This atricle may be of use:
http://builder.com.com/5100-6388-6164310-2.html#Listing%20B
Dan
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test
> mail without a problem. I also managed to create a job which would execute
> a query. I'd like now the sql server send to several email addresses the
> results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana

Emailing results from query

Hi,
I just installed sql05 ent version and set up the DB Mail and ran a test
mail without a problem. I also managed to create a job which would execute a
query. I'd like now the sql server send to several email addresses the
results of the query and here where I'm stuck.
HOWDO you do it?
TIA
AnaRead about sp_send_dbmail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test m
ail without a problem. I
> also managed to create a job which would execute a query. I'd like now the
sql server send to
> several email addresses the results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana|||Hi,
This atricle may be of use:
http://builder.com.com/5100-6388-61...tml#Listing%20B
Dan
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test
> mail without a problem. I also managed to create a job which would execute
> a query. I'd like now the sql server send to several email addresses the
> results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana

Email Task

In my DTS package I tried to make an email task and it works if I execute it manualy, but if I schedule it I get the following error:

DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_16 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_16 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 8 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 8 DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSSendMailTask_1 DTSRun OnError: DTSStep_DTSSendMailTask_1, Error = -2147220352 (80040480) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220352 (80040480); Provider Error: 0 (0) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Erro... Process Exit Code 1. The step failed.

What could it be?SQLAgent service account should be used to set up the mail profile.|||Got that to work, Thanks,

But now I am getting into another error:
Executed as user: UPSAPP\SQLService. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_16 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_16 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

This is happends when I am exporting a table into .xls file on the network drive.
Any ideas? Why is it using Microsoft JET Database Engine from SQL Server? Why can't it create a file, and why is it creating a file, i already have the file there.

Thanks in advance.|||So, if you're trying to append to the file, you just say so in your task desgn. The reason you're getting the error is because upon an attempt to append to the file it looks for it, and it can't find it because this account (SQLAgent service account) doesn't have permission to even read that share. So, it tries to create one (by design), and failes due to the same permission issue.|||So how would I tell the network admin to add that account for permission to that drive?|||First, confirm that permission is the issue by logging in with SQLAgent service account to demonstrate to yourself and then to your network admin that this is the case.|||And remember, when you execute a DTS package, it runs using your account. When you schedule it as a job, it runs as SQLAgent. This can confuse things when you are trying to debug permissions.