We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Problem with output to csv + looping

bikieswim
bikieswim asked
on
Medium Priority
313 Views
Last Modified: 2006-11-18
The criteria is based on the Client, I want to run a query for all the data
that matches that client and send an email to that client, then loop back
and run the query for the next client and send an email to them and loop back again..

What I am trying to do is the following in a DTS Package:
Step1: Create a "ActiveX Script Task" using IF statements for each client. The client info is passed into a
      global variable.

Step2: Now, in the next step I want to run the query based on the global vaiable(which has for the specific
      client). Now the reason I am not running the query within the ActiveX Script Task is coz, the output
      from the query will be huge and I want to be able to put this in a csv file to send to the client.

Step3: I will just email the client the csv file.

Step4: I want to loop back to Step1 and redo the who process for the next client.


I am having problems in Step2:
      a) how can I dump my query results to a csv file, I am using "Execute SQL Task" to run the query
         which doesnot allow me to put the results in a csv file.
      b) How can I loop back to Step1 to redo the process.

Please assist.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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

Author

Commented:
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.

CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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.

Author

Commented:
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?
CERTIFIED EXPERT
Top Expert 2012

Commented:
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'

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2012
Commented:
>>So, how do I used the dynamic SQL or the temporary table<<

This is how you solve it by resorting to Dynamic SQL:

Create procedure Rp_Unique
      @sh varchar(50)

As

Declare @SQL varchar(1000)

Set @SQL =      'Select      a.ph,
                  a.gp
            From      tdu
                  inner join ad on ad.ph = td.ph
            Where td.sh In ' + @sh

exec (@SQL)

If you do use Dynamic SQL make sure to:

A. Give Select permissions to the tdu and ad tables to the user in addition to Execute permissions for the Stored Procedure.  This is one of the downsides of using Dynamic SQL.

B. Test it in SQL Query Analyzer, prior to implementing it in your DTS Package.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Mr.Acperkins, you are a freakin genious that worked... thx a lot..
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.