Problem with output to csv + looping

>>I am using "Execute SQL Task" to run the query which doesnot allow me to put the results in a csv file. <<
You need to use a Transform Data Task.  Add a Text File (Destination) Connection and link it to your SQL Server connection.

>>How can I loop back to Step1 to redo the process. <<
That is more complicated and I would suggest you take a look at this example for looping:
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246

Wow ACPerkins, you are a genious..That got me going. But, when I run the DTS, non of the steps run though.
Here is what I have:

Step1: (ActiveX Script Task)
'************************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Function Main()
      Dim Conn
       Dim RS
       Dim SQL

      Set Conn = CreateObject("ADODB.Connection")
      Set RS = CreateObject("ADODB.Recordset")

      DTSGlobalVariables("Counter").Value       =       0

      Conn.Provider = "SQL"
      Conn.Properties("Data Source").Value = "A"
      Conn.Properties("Initial Catalog").Value = "A"
      Conn.Properties("User ID").Value = "A"
      Conn.Properties("Password").Value = "A"
      Conn.Open

'---
            If DTSGlobalVariables("Counter").Value = 0 Then
                  DTSGlobalVariables("sSh").Value = ""
                  DTSGlobalVariables("sSh").Value  = DTSGlobalVariables("sSh").Value & "('48')"

            elseIf    DTSGlobalVariables("Counter").Value = 1 Then
                  DTSGlobalVariables("sShortcode").Value = ""
                  DTSGlobalVariables("sShortcode").Value  = DTSGlobalVariables("sSh").Value & "('96')"
            End If

      Conn.Close
      Set Conn = Nothing
      Main = DTSTaskExecResult_Success
End Function



Step2: Microsoft OLE DB Provider for SQL Server
Step3: Text File Destination (csv file)
Step4: Email
Step5: Activex Script Task (for looping):
'**********************************************************************
'  Visual Basic ActiveX Script
'**********************************************************************

Function Main()

      DTSGlobalVariables("Counter").Value = DTSGlobalVariables("Counter").Value + 1
      If DTSGlobalVariables("Counter").Value <2 then
            Dim pkg, stpbegin
            set pkg = DTSGlobalVariables.Parent
            set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_1")

            stpbegin.ExecutionStatus = DTSStepExecStat_Waiting
      End If
      Main = DTSTaskExecResult_Success

End Function


Now, somehow none of these steps seem to work :(

Please help.

I am afraid I have never done it that way and in my opinion that is not the best way to take advantage of DTS.  You do not need to explicitly use ADO in DTS, instead create a Text File (Destination) Connection and then link it to your SQL Server Connection with a Transformation Data Task.  Again, take a look at the example from the link I posted (with the caveat that they are using a Text File (Source) Connection and in your case you are using a Text File (Destination) Connection) and notice that they are using ActiveX Script Tasks to control the execution of the Steps.

OK I have fixed everyting and the process seems to work but nothing is put into the .csv file. All I am doing is:

DTSGlobalVariables("sSh").Value = ""
DTSGlobalVariables("sSh").Value = DTSGlobalVariables("sSh").Value & "('4800')"

Now my Transform Data Task is like:
Rp_Unique ?    (Rp_Unique is my stored procedure and the ? is the parameter i pass which would be in this case "('4800')"

This is what my stored procedure looks like:

Create procedure Rp_Unique
@sh varchar(50)
As
Select a.ph, a.gp
from tdu
inner join ad on ad.ph = td.ph
and td.sh in @sh



The csv file populates properly when I hardcode ('4800') instead of passing it via variable @sh.

What am I doing wrong here. I have checked this for hours and I am helpless
Please assist.

>>What am I doing wrong here. <<
You need to click on the Parameters button and assign the parameter in "exec Rp_Unique ?" to the sSh Global Variable.

Yes AcPerkins, I did that.. it is really bizarre that it doesnot work. this is so simple but yet it is not working though, any ideas?

There is a problem is your stored procedure.  You cannot pass in a parameter like ('4800') and expect to use it with an In clause, without using Dynamic SQL or a temporary table. Since it is only one parameter I suggest you make the following change:

Create procedure Rp_Unique
@sh varchar(50)
As
Select a.ph, a.gp
from tdu
inner join ad on ad.ph = td.ph
and td.sh = @sh

And instead of ('4800') pass in just '4800'

Yeah, but the problem is I also eventually have something like ('4800', '4900'), so thats why I am using ('4800') right now.. so when I pass the parameter to the stored procedure, it will pull all the numbers. Thats the reason I am using "IN" instead of "=".

Also, just to do a test, I had also tried passing only '4800', one of my plethora of tests this morning.

So, how do I used the dynamic SQL or the temporary table, can you please be more specific and give some details !

Thannks in advance

Mr.Acperkins, you are a freakin genious that worked... thx a lot..