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:\>