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,

Emailing an Excel file

Hi ,

Is it possible for us to send an excel file generated in the package to the users using Sendmail task or anyother task using SSIS?

Any help is greatly appreciated.

Thanks,

SVGP.

Yes. You will need to set the Attachment property for the task. Now, I am not sure if you are creating a new instance of the Excel file for each package run; if you are then you will need to create a variable or two to provide the full path of the file and then set the Attachment Property using an expression that references the variable.

For instance, I use a Flat File destination and open it getting created by the package I use a File System Task to copy the file to another location, renaming with an extension of the current date. For each run therefore the file name is going to be different and so I created a variable that dynamically captures the new file name each day. The Send Mail task then attaches the correct file each day.

Hope this helps!

|||

Hi swan_sgp,

Yes. This is Possible.

1) Use a global variable (gvExcelFile) to fetch your so created fully qualified excel file path with extension name.

2) You must manually configure an attachment (creates a new connection manager), as Desibull already informed.

3) Assign gvExcelFile to Connection String property in expression builder for the connection manager meant for attachment.

4) Set the DelayValidation Property of the Send Mail to True.

Thanks

Subhash Subramanyam

|||

Hi Subash,

I need to create a new excel file daily and then need to mail it.

The problem iam facing is that ,when i schedule a job the task that dumps data into excel(Dataflow task ) gives a validation error.Everytime i need to manually link the sheets of the excel to the corresponding tables.

Any idea why this is happening?So iam unable to schedule as a job...

Thanks,

Vani

|||

You might take a look at this for some ideas:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Emailing an Excel file

Hi ,

Is it possible for us to send an excel file generated in the package to the users using Sendmail task or anyother task using SSIS?

Any help is greatly appreciated.

Thanks,

SVGP.

Yes. You will need to set the Attachment property for the task. Now, I am not sure if you are creating a new instance of the Excel file for each package run; if you are then you will need to create a variable or two to provide the full path of the file and then set the Attachment Property using an expression that references the variable.

For instance, I use a Flat File destination and open it getting created by the package I use a File System Task to copy the file to another location, renaming with an extension of the current date. For each run therefore the file name is going to be different and so I created a variable that dynamically captures the new file name each day. The Send Mail task then attaches the correct file each day.

Hope this helps!

|||

Hi swan_sgp,

Yes. This is Possible.

1) Use a global variable (gvExcelFile) to fetch your so created fully qualified excel file path with extension name.

2) You must manually configure an attachment (creates a new connection manager), as Desibull already informed.

3) Assign gvExcelFile to Connection String property in expression builder for the connection manager meant for attachment.

4) Set the DelayValidation Property of the Send Mail to True.

Thanks

Subhash Subramanyam

|||

Hi Subash,

I need to create a new excel file daily and then need to mail it.

The problem iam facing is that ,when i schedule a job the task that dumps data into excel(Dataflow task ) gives a validation error.Everytime i need to manually link the sheets of the excel to the corresponding tables.

Any idea why this is happening?So iam unable to schedule as a job...

Thanks,

Vani

|||

You might take a look at this for some ideas:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

EMail Task Problem

I have a pretty simple process that copies some files from an AS400 to a directory on the same server where my sql 2005 instance lives. I then use an email task to send these files to a client.

However, the email task does not work. I get the following message:

[Send Mail Task] Error: Either the file "BYNSOSR1;BYNSOSR2;BYNSOSR3;BYNSOSR4;BYNSOSR5;BYNSOSR6;BYNSOSR7" does not exist or you do not have permissions to access the file.

Well, the files definitely exist where they are supposed to be so I guess it's a permissions error. However, I have no clue which user would need permissions on that directory. As a test, I gave everyone read permissions and still got this error.

Any suggestions about which user I need to allow read access to the directory where these files exist?

Thanks in advance for any info.

I should say the path info in the previous post was changed so as not to reveal path info. But, the files do indeed exist where the email task expects them to be. I wrote a vb.net app that can email the files from that location just fine using the SMTP client of the .net.mail object hierarchy.|||Well, I never could figure out why the send email task wasn't working. It must have been some sort of permissions issue. I solved the problem by writing a console app in vb.net that sends the email via the net.mail smtpclient. I then run the console app as a execute process task. That sent the email with no problems and was a perfectly adequate way to solve the problem.

Email task failure urgent

i use email task and it dosent send an email at all....this task causes an error..

If i need to set up email for the package if the package fails...

or how should i set in the sql server agent...iknow there is an option there but i dont know how to..

Please let me know...

Sureshv,

Please post the error.

|||

Here is the error

[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.".

|||Did you create an appropriate SMTP connection to your mail server? When editing the Send Mail task you'll be able create an SMTP connection. Make sure the information is correct and that you have permissions to the SMTP server.|||

Here is what i did..

I droped the send mail task to my control flow.

then smtp connection :i typed in the ip address

from:i typed in some email address

to :i typed in my email address

message source type:variable(dont know just put int theses)

message ource User::Variable( " )

priority normal...

when i run i come of with this message.

please let me know if i am right

|||MessageSourceType is exactly what it says. The message of the e-mail is either directly input in the box below, or it is a variable that already has the message assigned to it, or it is contained in a file somewhere.

Try to make sure the IP address is really an SMTP server and that it is accepting anonymous connections:

In windows, Start->Run->cmd.exe

From there, "telnet your-ip-address-here 25"

If you get something to come up, then simply type "quit".|||

i checked as u asked me to " telnet your-ip-address-here 25" it was jus blank and the came up with c:\documents and settigs\my name>

Whats that 25 for?

|||

sureshv wrote:

i checked as u asked me to " telnet your-ip-address-here 25" it was jus blank and the came up with c:\documents and settigs\my name>

Whats that 25 for?

It should've done something. Some message should've shown up.

telnet 111.222.333.444 25

"25" indicates the port on the server. Port 25 is the SMTP port.|||thanks..how to set this to a group of people receiving email...i want to set this email only on error in the package how do i set this..|||how to send email only on failure?|||Use the red arrows in the control flow to hook up to a send mail task.|||

i wonder if there are read arrows to data flow,execute sql etc...

|||Sorry. When you connect (for instance) a data flow to a send mail task, the arrow will initially be green. Double click on the arrow and change the "Value" parameter from "Success" to "Failure."

Then the arrow will turn red.|||thanks|||

I have data flow (DB1 to DB2) task and then send mail task. The package executes everyday and mail notificate sent regularly. But if DB1 or DB2 connection failed (due to expired password or server not available or any network issues), the package failing in validation stage itself and send mail task never executed.

The expected result is, email must be sent with the result of data flow task.

Anyone comments please

Email task failure urgent

i use email task and it dosent send an email at all....this task causes an error..

If i need to set up email for the package if the package fails...

or how should i set in the sql server agent...iknow there is an option there but i dont know how to..

Please let me know...

Sureshv,

Please post the error.

|||

Here is the error

[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.".

|||Did you create an appropriate SMTP connection to your mail server? When editing the Send Mail task you'll be able create an SMTP connection. Make sure the information is correct and that you have permissions to the SMTP server.|||

Here is what i did..

I droped the send mail task to my control flow.

then smtp connection :i typed in the ip address

from:i typed in some email address

to :i typed in my email address

message source type:variable(dont know just put int theses)

message ource User::Variable( " )

priority normal...

when i run i come of with this message.

please let me know if i am right

|||MessageSourceType is exactly what it says. The message of the e-mail is either directly input in the box below, or it is a variable that already has the message assigned to it, or it is contained in a file somewhere.

Try to make sure the IP address is really an SMTP server and that it is accepting anonymous connections:

In windows, Start->Run->cmd.exe

From there, "telnet your-ip-address-here 25"

If you get something to come up, then simply type "quit".|||

i checked as u asked me to " telnet your-ip-address-here 25" it was jus blank and the came up with c:\documents and settigs\my name>

Whats that 25 for?

|||

sureshv wrote:

i checked as u asked me to " telnet your-ip-address-here 25" it was jus blank and the came up with c:\documents and settigs\my name>

Whats that 25 for?

It should've done something. Some message should've shown up.

telnet 111.222.333.444 25

"25" indicates the port on the server. Port 25 is the SMTP port.|||thanks..how to set this to a group of people receiving email...i want to set this email only on error in the package how do i set this..|||how to send email only on failure?|||Use the red arrows in the control flow to hook up to a send mail task.|||

i wonder if there are read arrows to data flow,execute sql etc...

|||Sorry. When you connect (for instance) a data flow to a send mail task, the arrow will initially be green. Double click on the arrow and change the "Value" parameter from "Success" to "Failure."

Then the arrow will turn red.|||thanks|||

I have data flow (DB1 to DB2) task and then send mail task. The package executes everyday and mail notificate sent regularly. But if DB1 or DB2 connection failed (due to expired password or server not available or any network issues), the package failing in validation stage itself and send mail task never executed.

The expected result is, email must be sent with the result of data flow task.

Anyone comments please

email task and file system deployment

does email task only work with sql server deployment? I have an email task in my ssis package and i want go for file system deployment.

thanks,

kushpaw

It should work on both deployments.
I use file system deployment and get all emails successfully

Email task - no error - no mail

I am trying to explore the email task in SSIS. I have a task which has only the email task. When I run its not throwing any error instead it says successfuly run but I am not recieving mail. I dont know how to trace this, could someone help me on this?

I'm sending email by sript component because in the company I cant send via SMTP...

Do you want to try send by script?

Regards!

|||Yes, I can make a try on it. Please send the script. Thanks.|||

Dhanasu wrote:

I am trying to explore the email task in SSIS. I have a task which has only the email task. When I run its not throwing any error instead it says successfuly run but I am not recieving mail. I dont know how to trace this, could someone help me on this?

Perhaps it's getting caught up in a spam filter. Or the SMTP server isn't letting that e-mail through. Have you talked to the server admin?|||

here you have...

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net

Imports System.IO

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim strServer As String = "mail.Grupo.com"

Dim strSender As String = "pedro"

Dim strTo As String = "mswail@.mail.com"

Dim strSenderAlias As String = "Pedro"

Dim strSubject As String = "SUCESS Import CLiq - " & Today()

Dim strBody As String = "OK"

Dim sUri As String

sUri = "http://" + strServer + "/Exchange/" + strSenderAlias

sUri = sUri + "/%23%23DavMailSubmissionURI%23%23/"

Dim myUri As System.Uri = New System.Uri(sUri)

Dim HttpWRequest As HttpWebRequest = CType(WebRequest.Create(myUri), HttpWebRequest)

Dim sQuery As String

sQuery = "To: " & strTo & vbNewLine & _

"Subject: " & strSubject & vbNewLine & _

"Date: " & Date.Now & vbNewLine & _

"X-Mailer: My DAV mailer" & vbNewLine & _

"MIME-Version: 1.0" & vbNewLine & _

"Content-Type: text/plain" & vbNewLine & _

"Charset = ""iso-8859-1""" & vbNewLine & _

"Content-Transfer-Encoding: 7bit" & vbNewLine & vbNewLine & _

strBody

Dim myCred As NetworkCredential = New NetworkCredential("GRUPOCGD\rhs0002", "275007")

Dim myCredentialCache As CredentialCache = New CredentialCache()

myCredentialCache.Add(myUri, "Basic", myCred)

HttpWRequest.Credentials = myCredentialCache

HttpWRequest.Headers.Set("Translate", "f")

HttpWRequest.ContentType = "message/rfc822"

HttpWRequest.ContentLength = sQuery.Length

HttpWRequest.Timeout = 300000

HttpWRequest.Method = "PUT"

Dim ByteQuery As Byte() = System.Text.Encoding.ASCII.GetBytes(sQuery)

HttpWRequest.ContentLength = ByteQuery.Length

Dim QueryStream As Stream = HttpWRequest.GetRequestStream()

QueryStream.Write(ByteQuery, 0, ByteQuery.Length)

QueryStream.Close()

Dim HttpWResponse As HttpWebResponse = CType(HttpWRequest.GetResponse(), HttpWebResponse)

Dim iStatCode As Integer = CType(HttpWResponse.StatusCode, Integer)

Dim sStatus As String = iStatCode.ToString()

myCred = Nothing

myCredentialCache = Nothing

HttpWRequest = Nothing

HttpWResponse = Nothing

QueryStream = Nothing

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||I am wiating for the reply from the admin. The from and to addresses are mine, so there are no possibility for Spam filter. If the SMTP is in error I hope I should get error message !!!|||

The SMTP i am trying is incorrect. I used the correct SMTP and it worked.

Question: Why does the SSIS not throwing error on incorrect SMTP.

|||

Dhanasu wrote:

The SMTP i am trying is incorrect. I used the correct SMTP and it worked.

Question: Why does the SSIS not throwing error on incorrect SMTP.

You mean you used the wrong server?

|||Yes, i was using wrong server.|||

Dhanasu wrote:

Yes, i was using wrong server.

But it must've been a valid SMTP server -- one that accepts connections and such. So technically there wasn't an error.|||

Phil Brammer wrote:

Dhanasu wrote:

Yes, i was using wrong server.

But it must've been a valid SMTP server -- one that accepts connections and such. So technically there wasn't an error.

If the SMTP server is invalid, because it will not be able to find that server at all, it wont throw any error for opening the connection I agree. But since its not able to find the server specified in the connection manager, it should throw the error for this right?

Thanks
|||

Karunakaran wrote:

Phil Brammer wrote:

Dhanasu wrote:

Yes, i was using wrong server.

But it must've been a valid SMTP server -- one that accepts connections and such. So technically there wasn't an error.

If the SMTP server is invalid, because it will not be able to find that server at all, it wont throw any error for opening the connection I agree. But since its not able to find the server specified in the connection manager, it should throw the error for this right?

Thanks

When I set it up to point to a server that doesn't exist, I get an error message: "[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.". "

So if the OP above didn't get an error message and the task succeeded, then it must've been a valid SMTP server, just not the correct one.|||

An added question: Since the mail task is directly bind with the SMTP I can send emails from anonymous email as the "from email id". Is this an issue in my server configuration or in SSIS "send task"?

sql

Email task - no error - no mail

I am trying to explore the email task in SSIS. I have a task which has only the email task. When I run its not throwing any error instead it says successfuly run but I am not recieving mail. I dont know how to trace this, could someone help me on this?

I'm sending email by sript component because in the company I cant send via SMTP...

Do you want to try send by script?

Regards!

|||Yes, I can make a try on it. Please send the script. Thanks.|||

Dhanasu wrote:

I am trying to explore the email task in SSIS. I have a task which has only the email task. When I run its not throwing any error instead it says successfuly run but I am not recieving mail. I dont know how to trace this, could someone help me on this?

Perhaps it's getting caught up in a spam filter. Or the SMTP server isn't letting that e-mail through. Have you talked to the server admin?|||

here you have...

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net

Imports System.IO

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim strServer As String = "mail.Grupo.com"

Dim strSender As String = "pedro"

Dim strTo As String = "mswail@.mail.com"

Dim strSenderAlias As String = "Pedro"

Dim strSubject As String = "SUCESS Import CLiq - " & Today()

Dim strBody As String = "OK"

Dim sUri As String

sUri = "http://" + strServer + "/Exchange/" + strSenderAlias

sUri = sUri + "/%23%23DavMailSubmissionURI%23%23/"

Dim myUri As System.Uri = New System.Uri(sUri)

Dim HttpWRequest As HttpWebRequest = CType(WebRequest.Create(myUri), HttpWebRequest)

Dim sQuery As String

sQuery = "To: " & strTo & vbNewLine & _

"Subject: " & strSubject & vbNewLine & _

"Date: " & Date.Now & vbNewLine & _

"X-Mailer: My DAV mailer" & vbNewLine & _

"MIME-Version: 1.0" & vbNewLine & _

"Content-Type: text/plain" & vbNewLine & _

"Charset = ""iso-8859-1""" & vbNewLine & _

"Content-Transfer-Encoding: 7bit" & vbNewLine & vbNewLine & _

strBody

Dim myCred As NetworkCredential = New NetworkCredential("GRUPOCGD\rhs0002", "275007")

Dim myCredentialCache As CredentialCache = New CredentialCache()

myCredentialCache.Add(myUri, "Basic", myCred)

HttpWRequest.Credentials = myCredentialCache

HttpWRequest.Headers.Set("Translate", "f")

HttpWRequest.ContentType = "message/rfc822"

HttpWRequest.ContentLength = sQuery.Length

HttpWRequest.Timeout = 300000

HttpWRequest.Method = "PUT"

Dim ByteQuery As Byte() = System.Text.Encoding.ASCII.GetBytes(sQuery)

HttpWRequest.ContentLength = ByteQuery.Length

Dim QueryStream As Stream = HttpWRequest.GetRequestStream()

QueryStream.Write(ByteQuery, 0, ByteQuery.Length)

QueryStream.Close()

Dim HttpWResponse As HttpWebResponse = CType(HttpWRequest.GetResponse(), HttpWebResponse)

Dim iStatCode As Integer = CType(HttpWResponse.StatusCode, Integer)

Dim sStatus As String = iStatCode.ToString()

myCred = Nothing

myCredentialCache = Nothing

HttpWRequest = Nothing

HttpWResponse = Nothing

QueryStream = Nothing

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||I am wiating for the reply from the admin. The from and to addresses are mine, so there are no possibility for Spam filter. If the SMTP is in error I hope I should get error message !!!|||

The SMTP i am trying is incorrect. I used the correct SMTP and it worked.

Question: Why does the SSIS not throwing error on incorrect SMTP.

|||

Dhanasu wrote:

The SMTP i am trying is incorrect. I used the correct SMTP and it worked.

Question: Why does the SSIS not throwing error on incorrect SMTP.

You mean you used the wrong server?

|||Yes, i was using wrong server.|||

Dhanasu wrote:

Yes, i was using wrong server.

But it must've been a valid SMTP server -- one that accepts connections and such. So technically there wasn't an error.|||

Phil Brammer wrote:

Dhanasu wrote:

Yes, i was using wrong server.

But it must've been a valid SMTP server -- one that accepts connections and such. So technically there wasn't an error.

If the SMTP server is invalid, because it will not be able to find that server at all, it wont throw any error for opening the connection I agree. But since its not able to find the server specified in the connection manager, it should throw the error for this right?

Thanks
|||

Karunakaran wrote:

Phil Brammer wrote:

Dhanasu wrote:

Yes, i was using wrong server.

But it must've been a valid SMTP server -- one that accepts connections and such. So technically there wasn't an error.

If the SMTP server is invalid, because it will not be able to find that server at all, it wont throw any error for opening the connection I agree. But since its not able to find the server specified in the connection manager, it should throw the error for this right?

Thanks

When I set it up to point to a server that doesn't exist, I get an error message: "[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.". "

So if the OP above didn't get an error message and the task succeeded, then it must've been a valid SMTP server, just not the correct one.|||

An added question: Since the mail task is directly bind with the SMTP I can send emails from anonymous email as the "from email id". Is this an issue in my server configuration or in SSIS "send task"?

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.

Ejecting Tape automatically after Backup

I had configured a Database Maintenance task for performing daily full
database backup into tape drive and it is working fine.
My problem is after performing the backup it is not ejecting the tape out of
the drive automatically. Also there is no option available in the Database
Maintenace Wizard to configure same.
Can any body help me'
Thanks
Satyakisatyaki wrote:
> I had configured a Database Maintenance task for performing daily full
> database backup into tape drive and it is working fine.
> My problem is after performing the backup it is not ejecting the tape out
of
> the drive automatically. Also there is no option available in the Database
> Maintenace Wizard to configure same.
> Can any body help me'
> Thanks
> Satyaki
Just a suggestion, we leave the tapes in the drive as it shows that the
tape did not run out of space. When a tape is full, it ejects so you
can put another in - and the person changing the tapes immediately knows
something is wrong.|||We have got tape for all week days and database size is not big enough to be
full.
Problem is that SQL server is in a differnt location where there is no
skilled person available. They generally forgot to remove the tape at night
time shutdown or replacing it next day.
Thats why I am asking this.
"Ian" wrote:

> satyaki wrote:
> Just a suggestion, we leave the tapes in the drive as it shows that the
> tape did not run out of space. When a tape is full, it ejects so you
> can put another in - and the person changing the tapes immediately knows
> something is wrong.
>|||Have to script. The maintenance wizard does not provide an interface for
the full statement.
Check out:
http://msdn.microsoft.com/library/e...asp?frame=true
WITH UNLOAD is the option you want.
Sincerely,
Anthony Thomas
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:114A011E-EC7C-4874-A895-827FC312B648@.microsoft.com...
> We have got tape for all week days and database size is not big enough to
be
> full.
> Problem is that SQL server is in a differnt location where there is no
> skilled person available. They generally forgot to remove the tape at
night[vbcol=seagreen]
> time shutdown or replacing it next day.
> Thats why I am asking this.
> "Ian" wrote:
>
out of[vbcol=seagreen]
Database[vbcol=seagreen]

Ejecting Tape automatically after Backup

I had configured a Database Maintenance task for performing daily full
database backup into tape drive and it is working fine.
My problem is after performing the backup it is not ejecting the tape out of
the drive automatically. Also there is no option available in the Database
Maintenace Wizard to configure same.
Can any body help me'
Thanks
Satyakisatyaki wrote:
> I had configured a Database Maintenance task for performing daily full
> database backup into tape drive and it is working fine.
> My problem is after performing the backup it is not ejecting the tape out
of
> the drive automatically. Also there is no option available in the Database
> Maintenace Wizard to configure same.
> Can any body help me'
> Thanks
> Satyaki
Just a suggestion, we leave the tapes in the drive as it shows that the
tape did not run out of space. When a tape is full, it ejects so you
can put another in - and the person changing the tapes immediately knows
something is wrong.|||We have got tape for all week days and database size is not big enough to be
full.
Problem is that SQL server is in a differnt location where there is no
skilled person available. They generally forgot to remove the tape at night
time shutdown or replacing it next day.
Thats why I am asking this.
"Ian" wrote:

> satyaki wrote:
> Just a suggestion, we leave the tapes in the drive as it shows that the
> tape did not run out of space. When a tape is full, it ejects so you
> can put another in - and the person changing the tapes immediately knows
> something is wrong.
>|||Have to script. The maintenance wizard does not provide an interface for
the full statement.
Check out:
http://msdn.microsoft.com/library/e...asp?frame=true
WITH UNLOAD is the option you want.
Sincerely,
Anthony Thomas
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:114A011E-EC7C-4874-A895-827FC312B648@.microsoft.com...
> We have got tape for all week days and database size is not big enough to
be
> full.
> Problem is that SQL server is in a differnt location where there is no
> skilled person available. They generally forgot to remove the tape at
night[vbcol=seagreen]
> time shutdown or replacing it next day.
> Thats why I am asking this.
> "Ian" wrote:
>
out of[vbcol=seagreen]
Database[vbcol=seagreen]

Ejecting Tape automatically after Backup

I had configured a Database Maintenance task for performing daily full
database backup into tape drive and it is working fine.
My problem is after performing the backup it is not ejecting the tape out of
the drive automatically. Also there is no option available in the Database
Maintenace Wizard to configure same.
Can any body help me?
Thanks
Satyaki
satyaki wrote:
> I had configured a Database Maintenance task for performing daily full
> database backup into tape drive and it is working fine.
> My problem is after performing the backup it is not ejecting the tape out of
> the drive automatically. Also there is no option available in the Database
> Maintenace Wizard to configure same.
> Can any body help me?
> Thanks
> Satyaki
Just a suggestion, we leave the tapes in the drive as it shows that the
tape did not run out of space. When a tape is full, it ejects so you
can put another in - and the person changing the tapes immediately knows
something is wrong.
|||We have got tape for all week days and database size is not big enough to be
full.
Problem is that SQL server is in a differnt location where there is no
skilled person available. They generally forgot to remove the tape at night
time shutdown or replacing it next day.
Thats why I am asking this.
"Ian" wrote:

> satyaki wrote:
> Just a suggestion, we leave the tapes in the drive as it shows that the
> tape did not run out of space. When a tape is full, it ejects so you
> can put another in - and the person changing the tapes immediately knows
> something is wrong.
>
|||Have to script. The maintenance wizard does not provide an interface for
the full statement.
Check out:
http://msdn.microsoft.com/library/en...asp?frame=true
WITH UNLOAD is the option you want.
Sincerely,
Anthony Thomas
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:114A011E-EC7C-4874-A895-827FC312B648@.microsoft.com...
> We have got tape for all week days and database size is not big enough to
be
> full.
> Problem is that SQL server is in a differnt location where there is no
> skilled person available. They generally forgot to remove the tape at
night[vbcol=seagreen]
> time shutdown or replacing it next day.
> Thats why I am asking this.
> "Ian" wrote:
out of[vbcol=seagreen]
Database[vbcol=seagreen]

Ejecting Tape automatically after Backup

I had configured a Database Maintenance task for performing daily full
database backup into tape drive and it is working fine.
My problem is after performing the backup it is not ejecting the tape out of
the drive automatically. Also there is no option available in the Database
Maintenace Wizard to configure same.
Can any body help me'
Thanks
Satyakisatyaki wrote:
> I had configured a Database Maintenance task for performing daily full
> database backup into tape drive and it is working fine.
> My problem is after performing the backup it is not ejecting the tape out of
> the drive automatically. Also there is no option available in the Database
> Maintenace Wizard to configure same.
> Can any body help me'
> Thanks
> Satyaki
Just a suggestion, we leave the tapes in the drive as it shows that the
tape did not run out of space. When a tape is full, it ejects so you
can put another in - and the person changing the tapes immediately knows
something is wrong.|||We have got tape for all week days and database size is not big enough to be
full.
Problem is that SQL server is in a differnt location where there is no
skilled person available. They generally forgot to remove the tape at night
time shutdown or replacing it next day.
Thats why I am asking this.
"Ian" wrote:
> satyaki wrote:
> > I had configured a Database Maintenance task for performing daily full
> > database backup into tape drive and it is working fine.
> >
> > My problem is after performing the backup it is not ejecting the tape out of
> > the drive automatically. Also there is no option available in the Database
> > Maintenace Wizard to configure same.
> >
> > Can any body help me'
> >
> > Thanks
> > Satyaki
> Just a suggestion, we leave the tapes in the drive as it shows that the
> tape did not run out of space. When a tape is full, it ejects so you
> can put another in - and the person changing the tapes immediately knows
> something is wrong.
>|||Have to script. The maintenance wizard does not provide an interface for
the full statement.
Check out:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ba-bz_35ww.asp?frame=true
WITH UNLOAD is the option you want.
Sincerely,
Anthony Thomas
"satyaki" <satyaki@.discussions.microsoft.com> wrote in message
news:114A011E-EC7C-4874-A895-827FC312B648@.microsoft.com...
> We have got tape for all week days and database size is not big enough to
be
> full.
> Problem is that SQL server is in a differnt location where there is no
> skilled person available. They generally forgot to remove the tape at
night
> time shutdown or replacing it next day.
> Thats why I am asking this.
> "Ian" wrote:
> > satyaki wrote:
> > > I had configured a Database Maintenance task for performing daily full
> > > database backup into tape drive and it is working fine.
> > >
> > > My problem is after performing the backup it is not ejecting the tape
out of
> > > the drive automatically. Also there is no option available in the
Database
> > > Maintenace Wizard to configure same.
> > >
> > > Can any body help me'
> > >
> > > Thanks
> > > Satyaki
> >
> > Just a suggestion, we leave the tapes in the drive as it shows that the
> > tape did not run out of space. When a tape is full, it ejects so you
> > can put another in - and the person changing the tapes immediately knows
> > something is wrong.
> >
> >

Efficient way to transfer huge amount of records

Hi All,

I used a data flow task, and when trying to transfer data from a OLE DB Source (records ~ 75 lac) to a destination OLE DB Source, SSIS fails at the middle giving an error saying the Transaction log got filled, try again after clearing the same.

My query is what is the most efficient way to transfer say records more than 50 lac ensuring that it doesn't fail in the middle?

Thanks in Advance,

Mithun.

To avoid the error you need to increase transaction log size in SQL Server - it is SQL's transaction log, not SSIS'. I believe you can also decrease the batch size in OLE DB destination properties.

The most efficient way is SQL Destination - if the destination server is on the same machine where package runs, it is faster than OLE DB Destination.|||

Thanks for the comments!! I'll try out the options.

|||

Michael,

But if you need to copy from .xls source into a table you can't use Sql Destination.

Correct me if I'm going wrong.

|||

enric vives wrote:

Michael,

But if you need to copy from .xls source into a table you can't use Sql Destination.

Correct me if I'm going wrong.

That's wrong. The source of the data is irrelevant. There are some pre-requisites to using SQL Server Destination and the April 2006 drop of BOL tells you all you need to know.

-Jamie