How to change SQL script that has variables in it to run in as SSIS package

Question

I have a script below that is a stored procedure, now this stored procedure was being called from a SSIS package before. Now I don't want to call it at the SSIS packages, I want to take the script itself and put it on my SSIS package process called Execute SQL Task. The problem I am having is that this stored procedure has variables, and I have never used a script in SSIS packages with variables. How do I go about changing my script to fit on SSIS packages?

What I tried before was not the much since I have no background about variables in SSIS packages. I would love your help on this.

Declare @cmd varchar(max), @dateStart as date, @dateEnd as Date, 
        @dsTxt as char(10), @deTxt as char(10), @YM as char(6)

set @dateStart = CONVERT(VARCHAR(25), DATEADD(dd,-(DAY(DATEADD(mm, 1, getdate())) - 1), DATEADD(mm, -1, getdate())), 120)
set @dsTxt = @dateStart

set @dateEnd = CONVERT(VARCHAR(25), DATEADD(dd, -(DAY(getdate()) - 1), getdate()), 120)
set @deTxt = @dateEnd 

set @YM = cast(year(@dateStart) as char(4)) + case when len(cast(month(@dateStart) as char(2))) = 1 then '0'+ cast(month(@dateStart) as char(2)) else cast(month(@dateStart) as char(2)) end

print @dateStart
print @dsTxt
print @datEend
print @deTxt
print @YM

truncate table tablename1
--delete from dbo.Wasp_CDR

set @cmd = '
insert into tablename1
SELECT *
FROM OPENQUERY (M_yea, ''SELECT column1,column2
From tablename2
where (column1 like ''''SMSZ%'''' or column2 like ''''08%'''')
and column3 = ''''SMS'''' and column4 = ''''MO''''
and to_char(date, ''''YYYYMM'''') = ''''' + @YM + ''''''');'

--print @cmd
exec (@cmd)

I am expecting to find a way on how do i go about scripts that has variables in SSIS packages.

You are deriving all of the variables in this query, I don't see any external variable for you need to pass values externally (from another query). I don't see any reason for this query to not work. Set the ResultSet property to 'Fullresultset'. Did you try to put this query as it is in the 'Execute SQL Task' component of SSIS? — Niranjan Rajawat
– Niranjan Rajawat, Commented Apr 15, 2019 at 9:49
@NiranjanRajawat my Resultset add button is muted, what must be causing that? — Madonado
– Madonado, Commented Apr 15, 2019 at 10:01
am facing a situation where by it's saying "Command text was not set for command object" on the OLEDB source, how do i fix that? — Madonado
– Madonado, Commented Apr 15, 2019 at 11:01
and also am getting this error. The Declare SQL construct or statement is not supported. — Madonado
– Madonado, Commented Apr 15, 2019 at 11:12

Yohanes Christian · Accepted Answer · 2019-04-15 10:13:48Z

1

from my experience it's possible to run script task from SSIS variable but it's not possible to run dynamic sql query in 'Execute SQL Task' SSIS.
A quick workaround to run dynamic query in SSIS is to generate the query first in 'Script Task' and then save it to temporary String variable. And for the last step, set your temporary variable as 'SQL command from variable' in the 'Execute SQL Task'.
I hope this answer helps you.

answered Apr 15, 2019 at 10:13

Yohanes Christian

991 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yahfoufi · Accepted Answer · 2019-04-15 09:47:25Z

1

You can copy the whole code and use it within an Execute SQL Task:

answered Apr 15, 2019 at 9:47

Yahfoufi

2,5502 gold badges26 silver badges44 bronze badges

Collectives™ on Stack Overflow

How to change SQL script that has variables in it to run in as SSIS package

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related