How will SSIS be helpful in this scenario

Question

How will SSIS be helpful in this scenario

cmrhema

SSCommitted

Points: 1558
More actions
July 16, 2008 at 4:51 am

#381724

Hi,
First of all let me admit that I do not know anything in SSIS. Just surfed through some websites, but have not implemented anything.
What we are doing is we have written a stored procedure that will transfer data from one server to another server after every two days. The stored procedure has been called in batch file and included in the scheduled tasks.
However, it seems that using SSIS this task can be done smoothly.
Can anyone shed some light on this please.
thanks in advance
regards
cmrhema

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

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

When your solution works as required, there is no need to change it. If you want to get rid of the scheduled task: in your scenario SSIS has to be scheduled also (either by SQL Agent or Scheduled Task). And in your scenario all you would do is put the SP into the SSIS package.

SSIS can be particular helpful if data has to be modified along the way, or save to another format.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

cmrhema SSCommitted Points: 1558 More actions · Answer 2

Thanks for the reply, Now I need one more help, I want to pass parameters to the stored procedures, How can I pass. 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

steveb. SSC-Forever Points: 46733 More actions · Answer 3

Writing a procedure that simply inserts the data into one table from the other will be faster than writing to a flat file first.

you can either write a procedure or if it is a bit more complex you can create a SSIS package.

Post your table defs if you need further help.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

Hemalatha (7/18/2008)
Thanks for the reply, Now I need one more help, I want to pass parameters to the stored procedures, How can I pass.

CREATE PROCEDURE dbo.Dummy

@Dummy as int

AS

SELECT * FROM DummyTbl WHERE ID = @Dummy

GO

EXEC Dummy 1

Change all Dummy to your needs 😎

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

cmrhema SSCommitted Points: 1558 More actions · Answer 5

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 SSCommitted Points: 1558 More actions · Answer 6

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

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 7

To return the value of a SP use this:

CREATE PROCEDURE dbo.Dummy

@Dummy int

@Output varchar(50) output

AS

SELECT @Output = ColumnValue FROM DummyTbl WHERE ID = @Dummy

RETURN @Output

Of course you can also use the SET command to define a specific value for the output according to your needs.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

cmrhema SSCommitted Points: 1558 More actions · Answer 8

Unfortunately my output parameter will not return one single value, it will return some 20 rows, Of course i can put a coalesce and take in single row with a splitter ,but i cant do in that manner.

At present it will return some 25 tables, later on when the db increases the number of tables will increase .

So the output rows is not constant its dynamic

Now each of this tablename is the input for the FOR EACH loop container, which i donot know how to pass. And this is what i exactly want.

Inside the FOR EACH loop will be an executesqltask, which should take this value as an parameter and process.

Kindly let me know if you understood, if not i will try to be more precise.

regards

cmrhema