How to Declare a variable in Execute SQL task to Insert a row..?

Question

Hi There,

  I need to Insert two rows into a database table in a SSIS Package.

I am planning to add a Execute SQL Task.

  However I am not understanding how to write this query.


Insert Into Table
(colA, colB,colC,colD)
values
(1,2,3, @variable=?)

   If I wrote query like this...it is throwing an error "


[Execute SQL Task] Error: Executing the query "Insert Into table
(ColA,ColB..." failed with the following error: "Must declare the scalar variable "@variable".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


How do declare variable in Execute SQL Task...?

---

Unknown

Answer 1

Use:

Insert Into Table
(colA, colB,colC,colD)
values
(1,2,3, ?)


and map it in parameters window in Execute SQL Task.


See: http://technet.microsoft.com/en-us/library/ms140355.aspx

and

http://www.sqlis.com/post/The-Execute-SQL-Task.aspx


Best Regards
Onur

---

BI and ERP Consultant @ Nexum Bogazici Personal Blog: http://iszekasinedir.wordpress.com/

Answer 2

THanks Onur...it worked like ACE...


   I appriciate your help

---

Unknown

Answer 3

Hi,

I have similar issue that I am trying to insert data from Source DB to destination DB. Here table name should be dynamic.

Insert Into @DTableName
(@Columns)
values

Select  * from @STABLENAME

In Query detination columns names we should provide. So I have taken @columns variable to return column names with cama seperation

Declare @Columns varchar(max)

SET @Columns =(select SUBSTRING(
 (SELECT ',' + s.name
 FROM (select name from sys.columns
 where object_id=(select object_id from sys.tables  where type='u' and name =@DTableName
 )) s
 FOR XML PATH('')),2,200000) )

While I parse this expression in Execute SQL  SQL Source Staatement expression editor I am getting this Error:

[Execute SQL Task] Error: Executing the query "insert into dbo.Test(@columns  )selec..." failed with the following error: "Must declare the scalar variable "@column".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

can any one give solution for this....

---

Ram