SSIS + Procedure

Question

SSIS + Procedure

LOVER OF SQL

SSCommitted

Points: 1900
More actions
April 10, 2014 at 11:15 am

#287521

Hello guys,
I have this procedure:
create proc [dbo].[BulkPROC] (@table nvarchar(255), @dateimport datetime)
as
begin
Exec msdb.dbo.sp_start_job @job_name = N'ClickETL'
declare @sql varchar(max) = 'insert into ' + @table+ ' Select
X
,''' + cast(@dataimport as varchar) + '''
FROM TABLE_Temporary'
exec(@sql)
end
GO
How can i define step in procedure? for example:
if execute BulkPROC 'table1,'20141004'
1. step Exec msdb.dbo.sp_start_job @job_name = N'ClickETL' and 2.step declare @sql.... is executing, i want to know this:
1.step execute Exec msdb.dbo.sp_start_job @job_name = N'ClickETL' - FINISH? Yes, 2.step...
if execute BulkPROC 'table1,'20141004' the second step don't wait for 1.step... all execute the same time...
Thank.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

I think you'd be better off to either design the job in BIDS (SSDT in 2012) or build a Sql Agent job with the steps you want. You could also put lots of conditional code in your proc along with try-catch blocks, but why not just use the tools (SSIS/Agent) that are designed for jobs like this?

Gerald Britton, Pluralsight courses

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 2

I can't use a SSDT in SQL Server 2012. Need to pass 2 parameters (table and dateimport) - procedure. in job, i don't know how can i do that'

declare @sql varchar(max) = 'Insert into '+@tabela+'

Select XX

,''' + cast(@dateimport as varchar) + '''

from table_temporary'

how can i put condition code in my proc if second step (declare..) execute only when the first finished?

a4apple SSCrazy Points: 2620 More actions · Answer 3

Can you not make this into two steps using two execute sql tasks directly and in the second task choose to create a sql statement using expression builder and execute the sql statement through variable rather than direct input. that will solve the issue and you will get to know step1 and step2 as well...

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 4

the big problem is because i need pass to parameter (application) when execute this procedure, how can i pass two parameter in executing procedure or job ?

a4apple SSCrazy Points: 2620 More actions · Answer 5

LOVER OF SQL (4/10/2014)
the big problem is because i need pass to parameter (application) when execute this procedure, how can i pass two parameter in executing procedure or job ?

You can always create configuration files to pass variables to the stored procedures in SSIS. and you also can pass those files using MSDB job scheduler as well.

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 6

LOVER OF SQL

SSCommitted

Points: 1900

April 10, 2014 at 1:27 pm

#1705025

Thanks. Do you have any example to describe this?

g.britton SSChampion Points: 13863 More actions · Answer 7

I regularly pass many parameters to Stored Procedures in Execute SQL tasks or OLE tasks in the Data Flow. Adding two is just as easy as adding one

Gerald Britton, Pluralsight courses

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 8

Grass, do you have an example? blog, site that describe that?

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 9

Grass, how can i pass two parameters in my application?

a4apple SSCrazy Points: 2620 More actions · Answer 10

LOVER OF SQL (4/10/2014)
Thanks. Do you have any example to describe this?

I don't have an example for passing variables through the application, but here is how you do it in SSIS.

https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-variables/

I will try to come up with a basic article at my website. Stay Tuned.

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 11

Thanks man. I used xp_cmdshell call bat... and bat call .dtsx, in the case, all code in procedure not execute the same time, mas second step wait for first step.... i'll have problem performance with that? what difference about performance between job and .bat from stored procedure?