SSIS Database Transfer Error - "Role Exists" even though DB is being overwritten in task.

hi,

I have created a SSIS package that does nothing more than loop through all DBs and copies the userDBs to another server. However, I keep getting an error after the task has created the database during its execution of "Create Role" statements. Here is the error:

Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [aspnet_WebEvent_FullAccess] " failed with the following error: "User, group, or role 'aspnet_WebEvent_FullAccess' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an "out" parameter.

 

Now it appears to me that the Transfer DB task keeps using master as the current database even after it has created the new DB? Why would it does this when at the source the database role is under the usersDB?

thanks,

Derek

hi. in ssis i have script task that, in some situations, raise error (Dts.TaskResult = ScriptResults.Failure). But in sysjobhistory table there is only shor description what happend (Description: The script returned a failure result.). Is there any way that i can set some system variable to describe what is error so that description ens up in sysjobhistory table? in the end, i want to look in job activity monitor and see that job is "red" and see the description that couses this failure.

Hi, I am processing a cube partition using an SSIS script task. The script works out the name of the partition which requires processing based on the system date.The problem I have is whenever there is a failure in the processing task the only error I get is that the script task returned a failure result. This is reported by the SQL job that calls the script task and also by SSIS package logging. I am running SQL2005 SP3How can I get my script to return a meningful error? Thanks in advance.

Hello All - I have a very weired SSIS problem. I am using BIDS in two machines - Windows 7 Professional Edition x64 bit (development workstation) and Windows 2003 Standard Edition x32 bit SP2 (server) . The visual studio version on both the machines are Version 2.0.50727 SP2. I found that my package's ExecuteProcessTask fails with "Access is denied" error in Windows2003 but runs ok in Windows 7 dev box. I then tried to debug the package in the Windows 2003 environment itself. The package failed in debug mode too with the same message. After looking for a while I found that when I shorten the Argument length of the failing ExecuteProcessTask the task did seem to work. So in Windows2003 environment if my argument exceeds 1846 characters the ExecuteProcessTask fails with message "Access is Denied". If the argument string is less than 1846 it works ok. In Windows 7 environment there is no problem at all. It does not seem like access problem but I may be wrong. Has anyone seen this? I will appreciate any reply. Thanks, Niben  

On my dev machine every thing works, When i put my SSIS on QA server for testing, I get following error . Customer has only SQL 2005. Please provide any input on how to resolve this issue. I provided HTTP URL as .asmx?WSDL    already in config file.   Code: 0xC002F304    Source: Log Start Message to Process Manager Web Service Web Service Task    Description: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The input Web Services Description Language (WSDL) file is not valid.    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil..ctor(Object connection, String downloadedWSDL)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()". End Error DTExec: The package execution returned DTSER_FAILURE (1).Moonis Tahir MVP, MCPD, MCSD.net, MCTS BizTalk 2006/SQL 2005/SharePoint Server 2007 (Dev & Config)

I have read all the other posts on this site and have not been able to resolve my issue.For testing purposes i created a very simple WCF service that takes no arguments and returns true.  That is all it does.When i create a winform client and instantiate the service i can run my service method and get true back so i know the service works.I then create a test SSIS package with 1 task, a web service task.  I set the web service tasks properties as follows:http connection: http://localhost/WCF_SSIS_Prototype/Service1.svcwsdl file: C:\...\SSIS_WCF_Prototype\Service1.wsdl  (I know this is not a valid path, i changed it for posting to forum)Service: Service1Method: GetDataVariable: User::BResultWhen i run the package i get the following error:[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Method 'ProxyNamespace.Service1.GetData' not found..    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()". I'm no

I have a simple SSIS package with three "Execute SQL Tasks". I am using ADO.Net Connection to execute SPs on a DB server. When I execute this package It works fine. So far so good. Now, I need to implement transation on this package. And problem starts now onwards. When I try to execute package after setting TransationOption = Required for the Sequence container which contains all the tasks, I get following error. [Execute SQL Task] Error: Failed to acquire connection "NYCDB0008.Export". Connection may not be configured correctly or you may not have the right permissions on this connection. "NYCDB0008.Export" is the name of the ADO.Net connection. I have been hunting for any solution but all in vain. I have tried changing all DTC settings on the dev as well as Database server. Please respond if anyone has any solution. Thanks! Anand

I have just been using the Import Data wizard in SSMS and when going through the wizard I checked the "Optimize for many tables" checkbox.

The resultant package contains a task called "Transfer Task". I've never heard of this before. Its not listed in the toolbox and its not documented in BOL.

It does some rather strange things as well.

• From what I can determine it uses an XML manifest file (stored in c:\documents and settings\<user>\Local settings\temp) to construct a SSIS package on the fly which is then executed by an Execute Package Task.
• Its SourceDB property is always "smo_Pubs". Its DestinationDB property is always "smo_Pubs_xfred"
• There's no UI for it

All very peculiar.

 

What is it?

Where has it come from?

Did it arrive with SP1 or have I just never noticed it before?

Why is it preferable to a package with lots of data-flows?

Why is it not documented?

When should I use it/not use it?

 

Questions questions questions...

 

Thanks

-Jamie

 

I'm using SSIS to copy all tables and the data from server1 to server2.  Database names are same on both source and destination servers. dbo.MyTable definately exists in the source so I don't understand this error message:

 [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E37  Description: "Invalid object name 'dbo.MyTable'.".  helpFile=dtsmsg100.rll helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".

 There's nothing fancy about MyTable:

CREATE TABLE [dbo].[MyTable](

[MyId] [