passing parameters in SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

passing parameters in SSIS

Author

Topic

cmrhema
Starting Member

20 Posts

Posted - 2008-07-18 : 08:12:42

Hi,

I want to create a package in SSIS. I am going to use a stored procedure.
I want to pass parameters to the stored procedures, How can I pass.
i tried to pass in executesql parameter mapping, but then wehre will it prompt. Secondly I need one more advise.

I have a procedure where i copy values from one table and move to another table, Both tables are in different databases. Will a procedure that just shifts from one table to another table is faster, or take the values from one table put in a flat file and take the values from the flat file and insert into the next table.

Kindly help

Thanks in advance

Regards
cmrhema

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 08:15:37

1.are you trying to make ssis prompt for param values?
2.are the dbs in same server?

cmrhema
Starting Member

20 Posts

Posted - 2008-07-19 : 00:48:26

quote:
Originally posted by visakh16

1.are you trying to make ssis prompt for param values?
2.are the dbs in same server?

thanks for the reply, Yes i wanted to make SSIS prompt, i could not get it. If there is any way kindly let me know.
No I am going to manipulate two different db in two different server

Meanwhile I attempted passing through command prompt as below


dtexec /file c:\package1.dtsx /Set \Package.Variables[tblname];gpsdata_history1

and this works

But I had one problem, in my stored procedure I had given


begin catch
    declare @error as int
	select @error=@@Error
	if(@error<>0)
	begin
		RAISERROR ('Unable to Delete - Error' , 16, 1)
		rollback tran
	end
end catch

and when this lines were excluded, I could run the package.

I have done as below

Dropped the control ExecuteSQLTask, gave an OLEDB connection, Soruce type was Direct Input, and in SQLSTATEMENT gave exec [backupdata24_Split_check] ?

Then included the parameter User::tblname and gave the Parameter name as 0

Executed the program, was executed successfully
and went to the command prompt and executed as above.

Now I have to pass many parameters not in one single time, but one after the other.
What should i go for in that case

Kindly let me know
Thanks in advance

Regards
cmrhema

cmrhema
Starting Member

20 Posts

Posted - 2008-07-19 : 07:08:35

I tried to use the ForEach Loop Container.
What I tried was created an Execute sql task and gave the stored procedure, the stored procedure is as below

quote:
alter procedure Return_TableNames
as
begin
select tablename1 from [mapping1].[dbo].[virtualmapping_web] group by tablename1 order by tablename1
end

Now what I did was inserted a FOR EACH LOOP CONTAINER

All I want know is to pass the results from the ExecuteSqlTask to the For Each Loop.

Please Help

Regards
cmrhema

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 12:15:40

quote:
Originally posted by cmrhema

I tried to use the ForEach Loop Container.
What I tried was created an Execute sql task and gave the stored procedure, the stored procedure is as below

quote:
alter procedure Return_TableNames
as
begin
select tablename1 from [mapping1].[dbo].[virtualmapping_web] group by tablename1 order by tablename1
end

Now what I did was inserted a FOR EACH LOOP CONTAINER

All I want know is to pass the results from the ExecuteSqlTask to the For Each Loop.

Please Help

Regards
cmrhema

you can put the result of executesql to recordset and then use this as source for foreachloop with ADO enumerator.

http://www.codeproject.com/KB/database/foreachadossis.aspx

cmrhema
Starting Member

20 Posts

Posted - 2008-07-21 : 00:58:51

quote:
Originally posted by visakh16

quote:
Originally posted by cmrhema

I tried to use the ForEach Loop Container.
What I tried was created an Execute sql task and gave the stored procedure, the stored procedure is as below

quote:
alter procedure Return_TableNames
as
begin
select tablename1 from [mapping1].[dbo].[virtualmapping_web] group by tablename1 order by tablename1
end

Now what I did was inserted a FOR EACH LOOP CONTAINER

All I want know is to pass the results from the ExecuteSqlTask to the For Each Loop.

Please Help

Regards
cmrhema

you can put the result of executesql to recordset and then use this as source for foreachloop with ADO enumerator.

http://www.codeproject.com/KB/database/foreachadossis.aspx

Thank you very much Visakh for the reply.
As per the link which u forwarded, I could pass the result into the for loop,successfully.

Now I wanted to ask one more thing, in most of the places I have seen that they use the script to see of the values inside the for loop, when i executed too it works well.

Will it be possible to pass the parameter directly from the for loop to ExecuteSQLTask, and if possible can u pls forward me any links for the same.

and many many thanks for the reply

regards
cmrhema

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:59:22

quote:
Originally posted by cmrhema

quote:
Originally posted by visakh16

quote:
Originally posted by cmrhema

I tried to use the ForEach Loop Container.
What I tried was created an Execute sql task and gave the stored procedure, the stored procedure is as below

quote:
alter procedure Return_TableNames
as
begin
select tablename1 from [mapping1].[dbo].[virtualmapping_web] group by tablename1 order by tablename1
end

Now what I did was inserted a FOR EACH LOOP CONTAINER

All I want know is to pass the results from the ExecuteSqlTask to the For Each Loop.

Please Help

Regards
cmrhema

you can put the result of executesql to recordset and then use this as source for foreachloop with ADO enumerator.

http://www.codeproject.com/KB/database/foreachadossis.aspx

Thank you very much Visakh for the reply.
As per the link which u forwarded, I could pass the result into the for loop,successfully.

Now I wanted to ask one more thing, in most of the places I have seen that they use the script to see of the values inside the for loop, when i executed too it works well.

Will it be possible to pass the parameter directly from the for loop to ExecuteSQLTask, and if possible can u pls forward me any links for the same.

and many many thanks for the reply

regards
cmrhema