[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Problem with output to csv + looping

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.
0
bikieswim
Asked:
bikieswim
  • 5
  • 5
1 Solution
 
Anthony PerkinsCommented:
>>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
0
 
bikieswimAuthor 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.

0
 
Anthony PerkinsCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bikieswimAuthor 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.
0
 
Anthony PerkinsCommented:
>>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.
0
 
bikieswimAuthor 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?
0
 
Anthony PerkinsCommented:
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'
0
 
bikieswimAuthor 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
0
 
Anthony PerkinsCommented:
>>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.
0
 
bikieswimAuthor Commented:
Mr.Acperkins, you are a freakin genious that worked... thx a lot..
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now