CODEWITHSUNDEEP
sql-serverssissql-server-2012sql-server-data-toolsssis-2012
user3115933
user3115933

Reputation: 4453

How do I combine these 3 SQL jobs into a single SSIS package?

I am using SQL Server 2012. I currently have 3 jobs running on my database using T-SQL queries. I want to group all these 3 SQL jobs into a single SSIS package, with each job assigned a specific step in the SSIS package.

I will be using SSDT to create this package. The T-SQL queries for my 3 jobs are as follows:

Job 1:

SELECT 
*
INTO dbo.table1
FROM 
   OPENDATASOURCE
   (
      'SQLOLEDB', 
      'Data Source=SourceServer;User ID=MyUser;Password=MyPass'
   ).SourceDatabase.dbo.SourceTable;

Jobs 2 and 3 are the same as above, except for the table names being imported.

Do I need to have 3 separate SSIS packages for each job and then merge these 3 SSIS packages into a single final SSIS package? Also, which task should I use to achieve this?

Upvotes: 1

Views: 602

Answers (2)

Ferdipux
Ferdipux

Reputation: 5256

You do not have to split your Jobs into separate packages, it will not be beneficial in your case. From maintenance reason I would use single package.
Simply put your T-SQL command text in Execute SQL Task - here is Microsoft Docs description

SSIS can offer exception handling. Say, if Step 2 fails then Step 1 changes should be rolled back. This can be achieved if you put all three steps in a sequence and set its TransactionSupport property to Required. This is just an example.

Upvotes: 1

Ivan Sivak
Ivan Sivak

Reputation: 7498

You can have all of these in a single package like this:

enter image description here

I have just one note - what if, say, Job 2 task will fail? Should the entire transaction rollback? If so - you should consider using the DataFlow task preceded by BEGIN TRANSACTION and followed by COMMIT TRANSACTION as described in this article. You can also use Failure task to report possible issues.

Also, one more thing - you don't need SSIS package here. What about creating a stored procedure and just calling this proc from a simple T-SQL Job similarly as you already do now? You could take care of transaction stuff on one place. Just a thought.

Upvotes: 1

Related Questions