• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1600
  • Last Modified:

SSIS Error on a Wizard created SSIS

Hi, I'm new to SSIS and have created a SSIS package, using the wizard, transferring tables from one Databae to another.  The options are to Drop existing tables and rebuild new ones.  The error message I get is:

The connection manager "DestinationConnectionOLEDB" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.

I suspect it's just a setting in the SQL Server that needs to be changed but for those that want to see the SQL Server Agent Job code, I have scripted it below.

Unfortunately I have spent a lot of time trying to overcome this problem, but to no avail, and now I have to deliver.  I need answers please.

USE [msdb]
GO
/****** Object:  Job [CM_Assessors_Database_Import Loans_Data]    Script Date: 05/13/2009 11:40:09 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/13/2009 11:40:09 ******/
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'CM_Assessors_Database_Import Loans_Data', 
		@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'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CM_Assessors_Data_Import_Loans_Data]    Script Date: 05/13/2009 11:40:10 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CM_Assessors_Data_Import_Loans_Data', 
		@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'/SQL "\SSIS_A99Loans_Transfer" /SERVER W00123f536AE8 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF', 
		@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'CM_Assessors_Database_Import_Loans_Data', 
		@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=20090514, 
		@active_end_date=99991231, 
		@active_start_time=53000, 
		@active_end_time=235959
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:

Open in new window

0
davidjk1
Asked:
davidjk1
  • 3
1 Solution
 
PedroCGDCommented:
Dear user,
Open your sssi project again in Visual Studio (BIDS) and uncheck the option offline (See attached image)
Execute the package once or check if the connections in connection manager are all ok!
Helped?
regards,
Pedro
www.pedrocgd.blogspot.com
SSIS-Offline.JPG
0
 
davidjk1Author Commented:
Thanks PedroCGD, I'm going to have to find out how to open the SSIS project.  I created it from SQL Server Management Studio using a wizard from BIDS because I have not ventured into that area of SQL as yet.  I was not given a location where to save it.  I can see it when I schedule it to run, it sits in a location called SSIS_Packages, but I can't find that on the SQL Server drives anywhere.

I'll get back to you once I have this information and tested it.

Cheers

0
 
davidjk1Author Commented:
Sorry PedroCGD,
I meant to say...
 I created it from SQL Server Management Studio using a wizard [and not] from BIDS because I have not ventured into that area of SQL as yet.
0
 
HoggZillaCommented:
If you saved the wizard to an SSIS Package then you can open it.
0
 
davidjk1Author Commented:
You  wanna tell me how Hogg!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now