Hi,
What I want to do seems fairly straightforward. Implementing it however, seems quite tricky, to me at least.
I need to set up a repetitive testing scenario. What I want to do is execute a series of stored procedures on 2 servers and compare the text file outputs of the stored procedures against each other.
So, run sp1 on server1, then run sp1 on server2, then write the outputs to 2 text files, then compare the output. Repeat this process for X number of stored procedures.
I was thinking that I would create a list of the stored procedures in a table, then dynamically build a process that would execute each stored procedure and write to file in turn. Then once, I have all the files, do a compare on the files.
I am trying to implement this in SSIS. I'm SURE there is a way to do what I am trying to do, but I'm pretty lost.
I found an article by Kirk Haselden which sort of gave me a start. I think what I want to do is run an EXECUTE SQL TASK (select report names from aforementioned table), then use a FOREACHLOOP container to process each row from the EXECUTE SQL TASK.
That is, "SELECT sp_name FROM Reports" > then for each "sp_name", run the stored procedure in the FOREACHLOOP container, then write the output to a flat file.
Seems simple yes?
But I just don't know how to connect the EXECUTE SQL TASK to the FOREACHLOOP container.
Any advice GREATLY appreciated!!!
Thanks
What I want to do seems fairly straightforward. Implementing it however, seems quite tricky, to me at least.
I need to set up a repetitive testing scenario. What I want to do is execute a series of stored procedures on 2 servers and compare the text file outputs of the stored procedures against each other.
So, run sp1 on server1, then run sp1 on server2, then write the outputs to 2 text files, then compare the output. Repeat this process for X number of stored procedures.
I was thinking that I would create a list of the stored procedures in a table, then dynamically build a process that would execute each stored procedure and write to file in turn. Then once, I have all the files, do a compare on the files.
I am trying to implement this in SSIS. I'm SURE there is a way to do what I am trying to do, but I'm pretty lost.
I found an article by Kirk Haselden which sort of gave me a start. I think what I want to do is run an EXECUTE SQL TASK (select report names from aforementioned table), then use a FOREACHLOOP container to process each row from the EXECUTE SQL TASK.
That is, "SELECT sp_name FROM Reports" > then for each "sp_name", run the stored procedure in the FOREACHLOOP container, then write the output to a flat file.
Seems simple yes?
But I just don't know how to connect the EXECUTE SQL TASK to the FOREACHLOOP container.
Any advice GREATLY appreciated!!!
Thanks