cursors vs temp table for a SP executed by SSIS

Question

Post reply

cursors vs temp table for a SP executed by SSIS

nabajyoti.b

SSCrazy

Points: 2177
More actions
January 6, 2009 at 2:35 am

#133476

In a SSIS package i want to execute few T-SQL statements sequentially one after another. So, SQL server i already have a superset of sql statements stored in a table. Now, my task will be to fetch out only related sql statements and execute one by one.
I have two options, cursors and temp table. Which one should i go for?
If it is a temp table, could you please provide me an example.
Thanks..............

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Actually, you don't have to use any of them...

This can be easily done by using For Each ADO enumerator.

1. Execute SQL Task 1 (to get the list of queries from sometable to be executed)

2. ForEach Loop Container (set it as ForEach ADO Enumerator & specify the resultset of above task as the source for the container)

3. Execute SQL Task 2 (to execute the queries one by one, provided by the container)

--Ramesh

nabajyoti.b SSCrazy Points: 2177 More actions · Answer 2

After extracting the list of queries, where do i store them?

Also, there are few queries, which are parameterized.

how do i make the whole thing going?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Ok, here is the list of steps you have to do...

1. Create a variable "objQueries" (as Object data type, to store the resultset of SQL Task 1)

2. Create another variable vCurrentQuery (as String data type, to store the query to execute)

3. Set the "ResultSet" property of SQL Task 1 to "Full result set" in General page.

4. In Result Set page of SQL Task 1, type "0" in Result Name & select "objQueries" in Variable property.

5. Now in ForEach Container, select the variable "objQueries" in ADO object source variable propery in Collection page.

6. Further, set the variable mappings page of container as, select "vCurrentQuery" in Variable and type "0" in index column ("0" means the first column of the resultset).

7. Set this variable "vCurrentQuery" as source statement to the "Execute SQL Task 2"

And that's it, you have a loop that will execute one by one for each record in the resultset provided by SQL Task 1...

--Ramesh

nabajyoti.b SSCrazy Points: 2177 More actions · Answer 4

Thanks for the reply Ramesh....

However, i would like to add that there are two columns which will required here. First is used for the sql statements and the second one is used for some records where the insert statement expects a parameter

which is getdate().

These were previously implemented in MS Access.

So, what i have done till now is that i have followed all your steps but for the parameter list included the two variables one for the string and the other for the getdate() part.

e.g if i have the contents as

col1 col2 col3

1 insert....... 0

2 insert.... 1

3 insert..... 1

Now for all the insert statements having col3 as 0 will have to be provided variable explicitly and executed.

So, i think script task will be of out help here.

If we do so, we have to pull the column of the insert statement and change it with a variable, which we will pass to the script task.

After changing the insert statemtn we have to keep it again in the variable you mentioned for keeping individual statements and then execute it with execute sql task.

But how do i go about it?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Well, I could do that....

But first you have to help me by providing enough information about the objects (in form of DDL), the sample data (i.e. INSERTS statements) and the OUTPUT required....

--Ramesh