Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I transferred a job from the old SQL Server 2005 to an upgraded SQL Server 2008 R2 instance, and I scripted out everything, changed the data source and other details... "This job should copy some files automatically from stage locations to active directories" but it's not doing that like it was in the old server! So I'm not sure what I'm missing.

It seems that it's been built on an SSIS package as I can see from the file path location. I'm trying to open this file using the SQL Server Integration Services, hoping to see the details about the stage location and the active directory details but I get an error about "Registration information for this application needs to be updated...", although there is a MS Visual Studio in the remote server and it should be up-to-date!

I don't know anything about Integration Services, and I see something in the code about CONNECTION RCSMTP that I didn't change, maybe that need to be changed to this new server SMTP?

Below is the script for this job that's called elfCopy which is Electronic Library Format

USE [msdb]
GO

/****** Object:  Job [eLFCopy]    Script Date: 12/30/2013 10:51:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/30/2013 10:51:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'eLFCopy', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'This job copies eLF files & folders from stage locations to active directories.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'BIO\WDDocManagement', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RunPackage]    Script Date: 12/30/2013 10:51:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RunPackage', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'SSIS', 
        @command=N'/FILE "C:\SSIS\Packages\elfcopy.dtsx" /CONNECTION "BRORPORAP3.WDDocumentManagement";"Data Source=BRORPORAP3;Initial Catalog=WDDocumentManagement;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /CONNECTION RCSMTP;"SmtpServer=brutmurex3.bio.ri.redcross.net;UseWindowsAuthentication=True;EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'WeeklySchedule', 
        @enabled=1, 
        @freq_type=8, 
        @freq_interval=2, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20110701, 
        @active_end_date=99991231, 
        @active_start_time=100, 
        @active_end_time=235959, 
        @schedule_uid=N'c406cae9-49b7-40dd-9438-cef44160f562'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

--Then I ran the following from the Command Prompt to see the error descriptions with the help of @billinkc and @Zane:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

F:\>dtexec.exe /FILE "C:\SSIS\Packages\elfcopy.dtsx" /CONNECTION "BRORPORAP3.WDD
ocumentManagement";"Data Source=BRORPORAP3;Initial Catalog=WDDocumentManagement;
Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /CONNECTION R
CSMTP;"SmtpServer=brutmurex3.bio.ri.redcross.net;UseWindowsAuthentication=True;?
?EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.2500.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started:  9:21:36 AM
Error: 2014-01-02 09:22:38.54
   Code: 0x00000003
   Source: PrepFiles PrepFiles
   Description: There was an exception while loading Script Task from XML: Syste
m.ApplicationException: The VSTA designer failed to load:  "System.Runtime.Inter
opServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned fro
m a call to a COM component.
   at VSTADTEProvider.Interop.VSTADTEProviderClass.GetDTE(String bstrHostID, UIn
t32 dwTimeout)
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.EnsureDTEObject()"
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.EnsureDTEObject()
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.InitNewScript(String l
anguageID, String projectname, String projectext, Boolean bCleanupOnClose)
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.InitNewScript(String l
anguageID, String projectname, String projectext)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.MigrateVSAScriptTask(X
mlElement elemProj, IDTSInfoEvents events)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement
 elemProj, IDTSInfoEvents events)
End Error
Error: 2014-01-02 09:23:40.42
   Code: 0x00000003
   Source: SetCopyFail SetCopyFail
   Description: There was an exception while loading Script Task from XML: Syste
m.ApplicationException: The VSTA designer failed to load:  "System.Runtime.Inter
opServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned fro
m a call to a COM component.
   at VSTADTEProvider.Interop.VSTADTEProviderClass.GetDTE(String bstrHostID, UIn
t32 dwTimeout)
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.EnsureDTEObject()"
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.EnsureDTEObject()
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.InitNewScript(String l
anguageID, String projectname, String projectext, Boolean bCleanupOnClose)
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.InitNewScript(String l
anguageID, String projectname, String projectext)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.MigrateVSAScriptTask(X
mlElement elemProj, IDTSInfoEvents events)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement
 elemProj, IDTSInfoEvents events)
End Error
Error: 2014-01-02 09:23:50.24
   Code: 0x00000003
   Source: SetPathFail SetPathFail
   Description: There was an exception while loading Script Task from XML: Syste
m.ApplicationException: The VSTA designer failed to load:  "System.Runtime.Inter
opServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned fro
m a call to a COM component.
   at VSTADTEProvider.Interop.VSTADTEProviderClass.GetDTE(String bstrHostID, UIn
t32 dwTimeout)
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.EnsureDTEObject()"
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.EnsureDTEObject()
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.InitNewScript(String l
anguageID, String projectname, String projectext, Boolean bCleanupOnClose)
   at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.InitNewScript(String l
anguageID, String projectname, String projectext)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.MigrateVSAScriptTask(X
mlElement elemProj, IDTSInfoEvents events)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement
 elemProj, IDTSInfoEvents events)
End Error
Error: 2014-01-02 09:23:50.29
   Code: 0x00000003
   Source: PrepFiles
   Description: The Script Task is corrupted.
End Error
Error: 2014-01-02 09:23:50.29
   Code: 0xC0024107
   Source: PrepFiles
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  9:21:36 AM
Finished: 9:23:50 AM
Elapsed:  134.161 seconds

F:\>dtexec.exe /FILE "C:\SSIS\Packages\elfcopy.dtsx" /CONNECTION "BRORPORAP3.WDD
ocumentManagement";"Data Source=BRORPORAP3;Initial Catalog=WDDocumentManagement;
Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /CONNECTION R
CSMTP;"SmtpServer=brutmurex3.bio.ri.redcross.net;UseWindowsAuthentication=True;?
?EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E>MyOutpu
t.txt

F:\>dtexec.exe /FILE "C:\SSIS\Packages\elfcopy.dtsx" /CONNECTION "BRORPORAP3.WDD
ocumentManagement";"Data Source=BRORPORAP3;Initial Catalog=WDDocumentManagement;
Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /CONNECTION R
CSMTP;"SmtpServer=brutmurex3.bio.ri.redcross.net;UseWindowsAuthentication=True;?
?EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E >MyOutp
ut.txt

F:\>
share|improve this question
    
I ran scripts as an administrator, as few sites suggested. Also, I didn't change anything about the user in both servers, so @Owner_Login_name is the same user. –  Shayma Ahmad Dec 31 '13 at 5:22
    
what's the error message? –  Edward Dortland Jan 2 at 17:13
    
I found this link. social.msdn.microsoft.com/Forums/sqlserver/en-US/… It seems to have some good links that may help you. –  Kenneth Fisher Jan 3 at 2:22
    
Ok, so many questions I have but here are a few so I can hopefully help more. ① Most obvious question, so pardon me if you already did this. Did you copy the elfcopy.dtsx object from the old server to the same location on the new server? (C:\SSIS\Packages\elfcopy.dtsx). If you did, then the following would be more questions to attempt to assist... –  FembotDBA Jan 3 at 20:36
    
② Are you able to find the BIDS project file for the SSIS package? Look on the old server, if possible, for objects with a .sln extension. The easiest way to ensure all your connections are updated is to edit the package in BIDS - SQL Server Business Intelligence project. ③ Is the server and database names the same as on the old server? I see "BRORPORAP3" as the server name and "WDDocumentManagement" as the database name. If those are not the same the package will fail. –  FembotDBA Jan 3 at 20:36

1 Answer 1

I would advice you to upgrade the package by following these steps

If after your upgrade you still run in to problems, it could be that your package is using components/drivers that are not available on 64bit. In that case the "easiest" work around would be to have your package use a 32bit runtime.

Open de Job properties and then edit the job step that is calling the SSIS package.

Go to the Execution Options tab and check "use 32bit runtime" enter image description here

It adds the switch /X86 to the Dtexec execution string. (please note that this switch will be ignored if you directly run it from the command line.)

However, be aware that in that case, you are running the package in 32bit while not making use of the 64bit benefits. This shouldn't have to be a problem, just something to keep in mind.

share|improve this answer
    
I followed the steps to upgrade it using the Wizard but the problem is that in SQL Server 2005 where the .dtsx file origionaly created and works, doesnt exist when I right click in any node in the Integration Services, nor in the Installation Center but it exists in the SQL Server 2008, you meant I have to upgrade it in the new 2008 SQL Server? I also tried the approach you showed me in the screen shot, its not doing anything also, can I send you the copy of this dtsx file with the scripted out job? Thanks –  Shayma Ahmad Jan 9 at 18:04
    
Can I follow these intsturctions without messing up anything in that server: msdn.microsoft.com/en-us/library/cc176071(v=vs.90).aspx or msdn.microsoft.com/en-us/library/cc175414(v=vs.90).aspx –  Shayma Ahmad Jan 13 at 20:22
    
As a follow up question: is there a way to do this in a (job create/update) script? –  ChargingPun Nov 27 at 16:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.