October 2, 2014 at 3:21 pm
Hi all,
We have a job that is related to BI. We need to move that job to another server.
Is it better to use transfer job task in ssis or script out the job and run that script on other server?
Is there any options available to do this task?
Before move and after move what are the steps we need to take? What are the probable errors and risks we may get.
Share your experiences please
October 2, 2014 at 3:38 pm
My preference is to script it and execute the script on the new server - very easy and takes little time or effort.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 2, 2014 at 3:43 pm
Thanks LNNR
Any precautions we may have to follow?
Any constraints we may face in this process?
October 2, 2014 at 3:46 pm
No precautions that would be any different than moving the job any other way. The same considerations always have to be taken into account when you move a job. Make sure the objects exist and that the references are proper.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 2, 2014 at 3:48 pm
Thanks LNNR
Any precautions we may have to follow?
Any constraints we may face in this process?
October 2, 2014 at 4:02 pm
ramana3327 (10/2/2014)
Thanks LNNRAny precautions we may have to follow?
Any constraints we may face in this process?
Look at my previous post.
And it is SQLRNNR. NOT LNNR;-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 3, 2014 at 12:42 am
Sorry for the repeated posts.
If that job is running the ssis package. How to start the process. Do we need to create that ssis package under the new folder? And what are the next steps? Do we need to create the config file also.
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/2/2014 11:28:50 PM ******/
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'Test',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SSISProd', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Refresh',
@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 "\"D:\SSIS\Refresh\Package.dtsx\"" /CONFIGFILE "\"D:\SSIS\Refresh\Refresh.dtsConfig\"" /CHECKPOINTING OFF /REPORTING E',
@database_name=N'master',
@flags=0,
@proxy_name=N'SSISProd'
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'9am run',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140715,
@active_end_date=99991231,
@active_start_time=90000,
@active_end_time=235959,
@schedule_uid=N'46'
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
October 3, 2014 at 7:56 am
ramana3327 (10/3/2014)
Sorry for the repeated posts.If that job is running the ssis package. How to start the process. Do we need to create that ssis package under the new folder? And what are the next steps? Do we need to create the config file also.
Think through it logically.
You changed the server. Therefore the path you are using is pointing where? Once you see that connection then you should be able to figure out the answers to your questions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 3, 2014 at 12:43 pm
Not clear about the /CHECKPOINTING OFF /REPORTING E',
What is this?
October 3, 2014 at 1:48 pm
ramana3327 (10/3/2014)
Not clear about the /CHECKPOINTING OFF /REPORTING E',What is this?
Here is the documentation on that
http://technet.microsoft.com/en-us/library/ms162810(v=sql.105).aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 3, 2014 at 1:56 pm
Thank you
October 9, 2014 at 11:33 am
Hi,
I am getting the same problem here. Job is failing with the error msg package migration from version 6 to version 3 failed with error code 0*C001700A. The version no of the package is not valid. The version number can't be greater than current version number
I foolowed this link
http://hariadusumalli.blogspot.com/2012/10/dtexec-error-version-number-in-package.html
I didn't understand the solution.
But I need to run this in 2008R2 box only. I can't use the same developed version now(Sql 2012)
October 9, 2014 at 11:50 am
I found the options
1) Rebuild the project in the correct version of BIDS that matches the server agent
used to run the job.
2) One other option is to set the location/path of the DTEXEC file you want to use
(depending on which version you are using)
3) Third option is install Sql 2012 and then run that job
4) 4th option is we already have 2014 in some another box, so need to upgrade the ssis package and run run the job in 2014.
I need the quickest and 100% sure run the job.
Any suggestion about option 1 and option 2.
How it will work?
October 9, 2014 at 12:42 pm
Hi All,
I opened the new project in sql 2008 and take ssis project and try to add the existing package .dtsx which is developed in sql data tools but it is failing.
error 0*C0010014 CPacakge::Load from XML fails
I need some suggestion please
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply