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 2000 Package Question/Issue

Hi All,

I am facing with a very intersting question here.

I have an embedded DTS 2000 package inside my SSIS 2005 Package. This DTS 2000 package just execute a transfer from a DB2 server to the 2005 server. Its a very simple transfer, without any transformation ( just column to column ).

Well, when I edit the DTS 2000 and save it, the package size increases about 100/200kb. It sounds like the package version ( the same as we have in the 2000 environment ). But i dont know how deactivate this "auto-increase".

Anyone can help me with this auto-increase? or just explain me what is happening?

Today I have packages with 9mb and just 3 or 4 embedded DTS 2000 packages inside.

Thanks in advance.

Thiago

Hello All,

Any answer?

After 3 months migrating DTS packages to SSIS packages we have some packages with 16mb of size. The SSIS packages which has incorporated 2000 packages, increase size every time we change anything on the 2000 package.

We are considering redesign those packages but we want to make sure that we are not missing any configuration.

Thanks for your attention.

Regards,

Thiago

|||DTS 2000 keeps every version you've saved, so it keeps growing each time. Unfortunately, unlike Enterprise Manager in SQL 2000, SSIS provides no easy way to manage the versions and cleanup the old versions. I've opened a bug for this issue in product defect database.

As a (long) workaround, you may save the DTS package as a file (right click the DTS designer surface, select Save As - this will save the latest version only), then switching the task to use this file instead of embedded package, finally use "load DTS 2000 package internally" in task editor. This way you'll get rid of all the old versions of DTS 2000 package.

Embedded 2000 Package Question/Issue

Hi All,

I am facing with a very intersting question here.

I have an embedded DTS 2000 package inside my SSIS 2005 Package. This DTS 2000 package just execute a transfer from a DB2 server to the 2005 server. Its a very simple transfer, without any transformation ( just column to column ).

Well, when I edit the DTS 2000 and save it, the package size increases about 100/200kb. It sounds like the package version ( the same as we have in the 2000 environment ). But i dont know how deactivate this "auto-increase".

Anyone can help me with this auto-increase? or just explain me what is happening?

Today I have packages with 9mb and just 3 or 4 embedded DTS 2000 packages inside.

Thanks in advance.

Thiago

Hello All,

Any answer?

After 3 months migrating DTS packages to SSIS packages we have some packages with 16mb of size. The SSIS packages which has incorporated 2000 packages, increase size every time we change anything on the 2000 package.

We are considering redesign those packages but we want to make sure that we are not missing any configuration.

Thanks for your attention.

Regards,

Thiago

|||DTS 2000 keeps every version you've saved, so it keeps growing each time. Unfortunately, unlike Enterprise Manager in SQL 2000, SSIS provides no easy way to manage the versions and cleanup the old versions. I've opened a bug for this issue in product defect database.

As a (long) workaround, you may save the DTS package as a file (right click the DTS designer surface, select Save As - this will save the latest version only), then switching the task to use this file instead of embedded package, finally use "load DTS 2000 package internally" in task editor. This way you'll get rid of all the old versions of DTS 2000 package.sql

Emailing Errors

I'm sort of new to SSIS so I apologize if this is a trivial question:

I setup a package and want it to email me if there is any errors. Right now I got it working to send me a pre-written message about the failure, but I want to load the full task failure message into variable and email it on the email body.

But so far I have not found a way to save the error messages into variables so I can email them. Anyone can help with that one?

As far as I know there is no one system variable that stores the error message. So, you may not be able to use expressions with variables.

Also, keep in mind that you could get multiple errors per package. So, a single variable won't do the job.

An error report is what you basically need.

Assuming you are setting the "OnError" event handler, the error message, container name, etc. are logged in the sysdtslog90 table. You can query the table using the execution guid, which is available as a system variable to the package.

You can then dump the result set into an excel or a text file and email it as an attachment.

|||

How did you set up even a pre-written mail message for errors?

Right now, I have an error log and a success mail message if everything else completes, but an error mail would be awesome.

|||

sorry. i figured it out. i am new to ssis (3 months) and just noticed the event handler tab. please try to control your laughter.

|||

Under email task editor- build an Expression for "Message source"

you could then do something like:

" There was a critical error in the Your Load. Error Msg follows " + @.[System::ErrorDescription]

to bubble up the error message.

sql

Emailing Errors

I'm sort of new to SSIS so I apologize if this is a trivial question:

I setup a package and want it to email me if there is any errors. Right now I got it working to send me a pre-written message about the failure, but I want to load the full task failure message into variable and email it on the email body.

But so far I have not found a way to save the error messages into variables so I can email them. Anyone can help with that one?

As far as I know there is no one system variable that stores the error message. So, you may not be able to use expressions with variables.

Also, keep in mind that you could get multiple errors per package. So, a single variable won't do the job.

An error report is what you basically need.

Assuming you are setting the "OnError" event handler, the error message, container name, etc. are logged in the sysdtslog90 table. You can query the table using the execution guid, which is available as a system variable to the package.

You can then dump the result set into an excel or a text file and email it as an attachment.

|||

How did you set up even a pre-written mail message for errors?

Right now, I have an error log and a success mail message if everything else completes, but an error mail would be awesome.

|||

sorry. i figured it out. i am new to ssis (3 months) and just noticed the event handler tab. please try to control your laughter.

|||

Under email task editor- build an Expression for "Message source"

you could then do something like:

" There was a critical error in the Your Load. Error Msg follows " + @.[System::ErrorDescription]

to bubble up the error message.

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 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"?

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

Editing SSIS Packages

I have successfully created several SSIS packages in the Development Studio. I want to schedule those packages to run with SQL Agent. The only way I could schedule them was to go to File>Save copy as...(not save as) which would give me a choice of either to save it to SQL Server or SSIS Package Store. Using this method allowed me to schedule the package but then I couldn't figure out how to edit the package without going back into the Development Studio, right-clicking on SSIS packages and selecting "Add Existing Package" and selecting that package from the SSIS Package store then opening it to edit.

That seems like a lot of work to edit a package. Is that the desired way to create, save, schedule and edit a SSIS package?

Thanks for any help.

This is the correct way. The idea is that you have a development version of your package and what is what you edit in BIDS. When you are happy with the package then you deploy it to your production environment. If you want to make changes then make changes to the package in BIDS and redeploy.

-Jamie

|||Thanks so much! That makes me feel better.