500 PTS! Dynamically changing the source table.

>>How can I execute this query and dynamically set a portion of the where statement?<<
Using parameters and global variables.

>>How do I execute the data pump for each record in the recordset that will supply the value for the above parameter in question A?<<
Not exactly sure that I am following you here.  But in any case, make sure you are using a staging table and not writing directly to the final table.  This way there is no need to cycle through each row.

Sorry ac about the confusion here.  I'll try to walk you through the exact scenario and see if that makes more sense.

/\/\/\/\/\/\ Component 1 -- Connection ASDM
Database: ASDM

/\/\/\/\/\/\ Component 2 -- Connection Heritage
Database: Heritage

/\/\/\/\/\/\ Component 3 -- Transform Data Task
*********     SOURCE (using SQL Query):
select cl.campaign_id as camp_id, s.intcode, cl.disposition as 'cc_id', s.dept As 'dept', UPPER(s.abvdsc) as 'abvdsc', s.prog_year as 'progyr', s.progno As Progno, s.up_abvdsc As 'up_abvdsc',
                        'emplid' =
                              CASE
                                    When (select phone From ASDM.dbo.users where loginname = cl.creditTo) IS NULL Then '000000000'
                                    When (select phone From ASDM.dbo.users where loginname = cl.creditTo) < 1 Then '000000000'
                                    Else (select phone From ASDM.dbo.users where loginname = cl.creditTo) END,
                                s.class As 'class',
                        s.result_code as 'in_code',
                                (select id from clients.dbo.HTS_ResultCodes Where cc_id = cl.disposition) as 'out_code',
                        Replace(CONVERT(varchar(10),Start ,112),'/','') As 'Date',
                        Substring(Convert(varchar(8),start,108),1,2) As 'Time_Hr',
                        Substring(Convert(varchar(8),start,108),4,2) As 'Time_Min',
                        Substring(Convert(varchar(8),start,108),7,2) As 'Time_Sec',
                        Replace(str(Replace(CONVERT(varchar(8),DateDiff(second,Start,Released),108),':',''),6),' ','0') As 'Call Duration',
                        'lead_amount' =
                              Case
                                    When (Replace(Replace(Replace(str(Replace(s.payment_amt,'.00',''), 6), ' ', '0'),' ',''),'000000','')) Is Null Then '000000'
                                    ELse Replace(Replace(Replace(str(Replace(s.payment_amt,'.00',''), 6), ' ', '0'),' ',''),'000000','') END,
                        'sale_amount' =
                              Case
                                    When (Replace(Replace(Replace(str(Replace(s.x_adprice,'.00',''), 6), ' ', '0'),' ',''),'000000','')) Is Null Then '000000'
                                    When (Replace(Replace(Replace(str(Replace(s.x_adprice,'.00',''), 6), ' ', '0'),' ',''),'000000','')) Is Not Null
                                                AND cl.disposition <> '0923D013-5709-48AB-9251-120DFF02C40B' Then '000000'
                                    Else Replace(Replace(Replace(str(Replace(s.x_adprice,'.00',''), 6), ' ', '0'),' ',''),'000000','') End,' ' As 'RedialFlag',
                        Substring(phone,1,3) + Substring(phone,4,3) + Substring(phone,7,4) As 'Phone',
                        (select id from clients.dbo.HTS_Dispositions where cc_id = cl.disposition) As 'Disposition',
                        '   ' As 'Fill', s.acctno,s.x_client from calllog As cl
Inner Join clients.dbo.somi as s
ON s.intcode = cl.customer_id
where Replace(CONVERT(varchar(10),cl.Start ,101),'/','')  > '04/10/2005' and
cl.campaign_id = 'FC1284BB-A524-4887-9FAA-4F6BA2074DA3' and (select id from clients.dbo.HTS_ResultCodes Where cc_id = cl.disposition) is not null
order by (select description from clients.dbo.HTS_ResultCodes Where cc_id = cl.disposition)

*********     DESTINATION
Table Name: UpAud
Table specs are the exact size and layount of the source columns.

*********     TRANSFORMATIONS
Transformations are a simple 1 to 1 Copy Column

/\/\/\/\/\/\ Component 4
Text File Destination

Basically what I'm doing is pumping data from ASDM to Heritage via the Transform Data Task.  What I'm needing to do is place the parameter where the hardcoded uniqueidentifier value is (snippet: cl.campaign_id = 'FC1284BB-A524-4887-9FAA-4F6BA2074DA3'). Only problem with that is I get an error message saying "The colomn prefix 'cl' does not match with a table name or alias name used in the query." when I try to substitue the value as follows cl.campaign_id = ? and .....

That's why I didn't know if the parameter and global variable would work.

If I do use global variables how do I populate a) the number of global variables and b) their values dynamically as the numbers will change, and then perform the data task transformation for each variable?

So what I am needing to do is:
Read in global variables
Perform data task transformation with each global variable one at a time
Continue with rest of task

I hope this helps.

Where is the unique identifier coming from?  Does it change every time you run the package?

Also, I suspect you also want to set the date '04/10/2005' as well. While I am on that subject (and totally unrelated to the question) this:

where Replace(CONVERT(varchar(10),cl.Start ,101),'/','')  > '04/10/2005'

is a problem.

Assuming that cl.Start is a date than the result of:
Replace(CONVERT(varchar(10),cl.Start ,101),'/','')

Will look like this:
mmddyyyy

As you can imagine, doing a string comparison to mm/dd/yyyy (04/10/2005) is not going to cut it.  Suggest you do it like this:
where cl.Start > '2005-04-10' and

Don't worry about the date thing it was temporary and is not a part of the final query.  I forgot to take that out of the query when I posted...everything else is legit. Good catch though!


The unique identifier is coming from an external table.
The unique identifier value itself does not change everytime I run the package but the table that holds the unique identifier will added to and subtracted from as new client are added and removed.

You can create and set global variables in the package by right-clicking and selecting Package Properties | Global Variables.  You can then create a Global Variable there called something like CampaignID (string) and set the value to  'FC1284BB-A524-4887-9FAA-4F6BA2074DA3'

Then in your query you can set the WHERE clause to:
where cl.campaign_id = ?

Click on the Parameters... button and assign the CampaignID global variable.

If you now want to automate passing in the CampaignID than you can pass in global variables either on the DTSRun.exe command line or by setting them in code.

I've tried this before.  I get the message "The colomn prefix 'cl' does not match with a table name or alias name used in the query" when I use the ?

>>I get the message "The colomn prefix 'cl' does not match with a table name or alias name used in the query" when I use the ? <<
Once you have verified the query works in SQL Query Analyzer with a constant unique identifier, use it in DTS and replace the constant unique identifier with a "?"

I verified the query worked in QA and got results.  Copy and pasted the query into DTS and got the same message.  Here is what I pasted.

select cl.campaign_id as camp_id, s.intcode, cl.disposition as 'cc_id', s.dept As 'dept', UPPER(s.abvdsc) as 'abvdsc', s.prog_year as 'progyr', s.progno As Progno, s.up_abvdsc As 'up_abvdsc',
                        'emplid' =
                              CASE
                                    When (select phone From ASDM.dbo.users where loginname = cl.creditTo) IS NULL Then '000000000'
                                    When (select phone From ASDM.dbo.users where loginname = cl.creditTo) < 1 Then '000000000'
                                    Else (select phone From ASDM.dbo.users where loginname = cl.creditTo) END, s.class As 'class',
                        s.result_code as 'in_code',(select id from clients.dbo.HTS_ResultCodes Where cc_id = cl.disposition) as 'out_code',
                        Replace(CONVERT(varchar(10),Start ,112),'/','') As 'Date',
                        Substring(Convert(varchar(8),start,108),1,2) As 'Time_Hr',
                        Substring(Convert(varchar(8),start,108),4,2) As 'Time_Min',
                        Substring(Convert(varchar(8),start,108),7,2) As 'Time_Sec',
                        Replace(str(Replace(CONVERT(varchar(8),DateDiff(second,Start,Released),108),':',''),6),' ','0') As 'Call Duration',
                        'lead_amount' =
                              Case
                                    When (Replace(Replace(Replace(str(Replace(s.payment_amt,'.00',''), 6), ' ', '0'),' ',''),'000000','')) Is Null Then '000000'
                                    ELse Replace(Replace(Replace(str(Replace(s.payment_amt,'.00',''), 6), ' ', '0'),' ',''),'000000','') END,
                        'sale_amount' =
                              Case
                                    When (Replace(Replace(Replace(str(Replace(s.x_adprice,'.00',''), 6), ' ', '0'),' ',''),'000000','')) Is Null Then '000000'
                                    When (Replace(Replace(Replace(str(Replace(s.x_adprice,'.00',''), 6), ' ', '0'),' ',''),'000000','')) Is Not Null AND cl.disposition <> '0923D013-5709-48AB-9251-120DFF02C40B' Then '000000'
                                    Else Replace(Replace(Replace(str(Replace(s.x_adprice,'.00',''), 6), ' ', '0'),' ',''),'000000','') End,' ' As 'RedialFlag',
                        Substring(phone,1,3) + Substring(phone,4,3) + Substring(phone,7,4) As 'Phone',
                        (select id from clients.dbo.HTS_Dispositions where cc_id = cl.disposition) As 'Disposition',
                        '   ' As 'Fill', s.acctno,s.x_client from calllog As cl
Inner Join clients.dbo.somi as s
ON s.intcode = cl.customer_id
where cl.campaign_id = ? and (select id from clients.dbo.HTS_ResultCodes Where cc_id = cl.disposition) is not null
order by (select description from clients.dbo.HTS_ResultCodes Where cc_id = cl.disposition)

Here is a screenshot of what I get.
http://www.theheritagecompany.com/ee/error.jpg 

Ok ac I'm getting closer.  There is something in the query that does not like the parameter being used where it is.  When I strip out the code where the are no sub selects and not CASE statements I get a parameter box that allows me to use parameters.  When I return those pieces of code I then get the error.  Any suggestions or workarounds?

Test this in SQL Query Analyzer to see if you get the same results (It should be more efficient) :

select      cl.campaign_id camp_id,
      s.intcode,
      cl.disposition 'cc_id',
      s.dept 'dept',
      UPPER(s.abvdsc) 'abvdsc',
      s.prog_year 'progyr',
      s.progno Progno,
      s.up_abvdsc 'up_abvdsc',
        'emplid' =
        CASE
              When u.phone IS NULL Or u.phone < 1 Then '000000000'
                Else u.phone
      END,
      s.class 'class',
        s.result_code 'in_code',
      cr.id 'out_code',
        CONVERT(varchar(8), Start, 112) 'Date',                        -- Style 112 returns yyyymmdd
      SUBSTRING(Convert(varchar(8),start,108),1,2) 'Time_Hr',
      SUBSTRING(Convert(varchar(8),start,108),4,2) 'Time_Min',
        SUBSTRING(Convert(varchar(8),start,108),7,2) 'Time_Sec',
        Replace(str(Replace(CONVERT(varchar(8),DateDiff(second,Start,Released),108),':',''),6),' ','0') 'Call Duration',
        'lead_amount' =
        Case
              When (Replace(Replace(Replace(str(Replace(s.payment_amt,'.00',''), 6), ' ', '0'),' ',''),'000000','')) Is Null Then '000000'
              ELse Replace(Replace(Replace(str(Replace(s.payment_amt,'.00',''), 6), ' ', '0'),' ',''),'000000','')
      END,
      'sale_amount' =
      Case
            When (Replace(Replace(Replace(str(Replace(s.x_adprice,'.00',''), 6), ' ', '0'),' ',''),'000000','')) Is Null Then '000000'
            When (Replace(Replace(Replace(str(Replace(s.x_adprice,'.00',''), 6), ' ', '0'),' ',''),'000000','')) Is Not Null AND cl.disposition <> '0923D013-5709-48AB-9251-120DFF02C40B' Then '000000'
            Else Replace(Replace(Replace(str(Replace(s.x_adprice,'.00',''), 6), ' ', '0'),' ',''),'000000','')
      End,
      ' ' As 'RedialFlag',
      SUBSTRING(phone,1,3) + Substring(phone,4,3) + Substring(phone,7,4) 'Phone',    -- You need to qualify this column with the alias
      cd.id 'Disposition',
          '   ' As 'Fill', s.acctno,s.x_client
from      calllog cl
      Inner Join clients.dbo.somi s ON s.intcode = cl.customer_id
      Left Join ASDM.dbo.users u On cl.creditTo = u.loginname                        -- You may want to change to an Inner Join
      Left Join clients.dbo.HTS_ResultCodes cr On cl.disposition = cr.cc_id          -- You may want to change to an Inner Join
      Left Join clients.dbo.HTS_Dispositions cd On cl.disposition = cd.cc_id         -- You may want to change to an Inner Join
where      cl.campaign_id = ?            -- Add Unique Identifier here to test in SQL QA
      and cr.id is not null
order by
      cr.description

Perfect that worked and now I can use Global Variables.  (You'll have to explain after we get this working.)

Ok so now that the global variables are working how would I go about reading in variables executing the data task transformation (data pump) for each one?  Remember these variables may change before the task starts but never during.

P.S.  When we get this done I'm going to post a question in VB databases for you worth 500 because to me this thing is worth 4K!

>>Ok so now that the global variables are working how would I go about reading in variables executing the data task transformation (data pump) for each one? <<
You are going to have to explain to me (again) where these variables come from.  By the way, I thought it was only one variable (for the campaign_id column)

>>P.S.  When we get this done I'm going to post a question in VB databases for you worth 500 because to me this thing is worth 4K!<<
That's OK there is a maximum of 500 points allowed here at EE, so make a contribution to your favorite charity instead :)

You're right. I mis-spoke.  When I said variables I meant variable.  Here is what I percieve that I am needing to do.

Execute SQL Task
Collect a row value for the variable

Perform datapump with global variable

Move to next record in row and populate global variable

Perform datapump again with global variable

Loop until all records have been used in global variable.

There should not be any need to "Loop until all records have been used". But, never mind the mechanics, that is not relevant at this point, just let me know where you are getting the global variable.  Does it come from another table?  Is it constant throughout the execution of the package?  The answers to those questions will help answer what tool to use.

The global variable comes from an external table and yes it is constant throughout the package.

Then the simplest solution is to query external table and assign the value to the global variable.  How you do that, depends on the type of that "external table".  For example, if it is a SQL Server table you can write a simple stored procedure or even a query and assign the result to that global variable.  You have not given me much in the way of details, but supposing you want to get the unique identifier for today (and there is only one) out of a table called Table1 than you could write a query like this:
Select YourGuidColumn From Table1 Where DateCreatedColumn = CONVERT(char(8), GETDATE(), 112)

You can then select "Parameters..." | "Output Parameters" and assign YourGuidColumn to the global variable.

And of course, make sure you place this at the start of the workflow.

I got that part working already but how do I make it repeat the workflow for each guid in the external table?

>>how do I make it repeat the workflow for each guid in the external table?<<
I would not do it that way.  I would instead join that table to your query (Assuming of course that it is a SQL table or one that you can join against), as in:

from     calllog cl
     Inner Join clients.dbo.somi s ON s.intcode = cl.customer_id
     Left Join ASDM.dbo.users u On cl.creditTo = u.loginname    
     Left Join clients.dbo.HTS_ResultCodes cr On cl.disposition = cr.cc_id
     Left Join clients.dbo.HTS_Dispositions cd On cl.disposition = cd.cc_id
     Inner Join ExternalTable et On cl.campaign_id = et.campaign_id
where     cr.id is not null
order by
     cr.description

Your only other choice that I know of, is to use a cursor to cycle through the "external table" and execute the stored procedure for each unique identifier.

How would I do that?

When I used the Inner Join it only return a set of records for one of the value and nothing else.

I realize you are new here, so I will just ask that you please re-read the EE Guidelines regarding grading standards at:
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

Specifically this section:
<quote>
C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.

Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.

... Similarly, the C grade is the lowest that can be given by a member, a fact which should be kept in mind when grading as well.

The use of a C in a vindictive manner is likely to be changed by a Moderator. You may not like the answer you get, and in some cases, and you may not like the way it is delivered, but if it is deemed to be accurate, no less than a B is an acceptable grade.

</quote>

Also, see here:
Can I get a grade changed?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18

Thanks, I appreciate it.