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

x
?
Solved

500 PTS! Dynamically changing the source table.

Posted on 2005-05-10
25
Medium Priority
?
189 Views
Last Modified: 2010-03-19
Ok my question is somewhat twofold but I will try to kill two birds with one stone.  What I'm tyring to do is run a DTS package that is pretty much as follows.

2 DB Connections
1 Text File (Destination)

The 2 DB connections have a data pump in the middle that collects a number of fields by a query and then uses a copy column transformation package to pump the data into the destination table.

That table is then later dumped into the text file at a later time.

My question is:
 A) How can I execute this query and dynamically set a portion of the where statement?
-------Example:  Select * from table as t1 where t1.id = ?

B) 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?
'Psuedo Code
-Lookup parameter value from recordset
-Execute data pump with value returned from recordset
-Loop through recordset
-Finished remaining part of DTS Package

Thanks a whole lot...in advance!
0
Comment
Question by:thcit
  • 12
  • 12
24 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13974300
>>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.
0
 
LVL 3

Author Comment

by:thcit
ID: 13977889
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.







0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13979157
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Author Comment

by:thcit
ID: 13979245
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13979412
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.
0
 
LVL 3

Author Comment

by:thcit
ID: 13979993
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 ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13980367
>>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 "?"
0
 
LVL 3

Author Comment

by:thcit
ID: 13981260
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)
0
 
LVL 3

Author Comment

by:thcit
ID: 13981322
Here is a screenshot of what I get.
http://www.theheritagecompany.com/ee/error.jpg 
0
 
LVL 3

Author Comment

by:thcit
ID: 13981503
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?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13982141
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
0
 
LVL 3

Author Comment

by:thcit
ID: 13985976
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!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13987148
>>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 :)
0
 
LVL 3

Author Comment

by:thcit
ID: 13987693
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13989269
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.
0
 
LVL 3

Author Comment

by:thcit
ID: 13989641
The global variable comes from an external table and yes it is constant throughout the package.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13991247
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.
0
 
LVL 3

Author Comment

by:thcit
ID: 13996158
I got that part working already but how do I make it repeat the workflow for each guid in the external table?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13996368
>>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.
0
 
LVL 3

Author Comment

by:thcit
ID: 13997105
How would I do that?
0
 
LVL 3

Author Comment

by:thcit
ID: 13997113
When I used the Inner Join it only return a set of records for one of the value and nothing else.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 13997856
>>How would I do that?<<
Do what?  Use a cursor? I don't know for sure, I don't use them and have not for many years.  Look up CURSORS in BOL.

>>When I used the Inner Join it only return a set of records for one of the value and nothing else.<<
Again without knowing anything about the "external table" it is difficult to hazard a guess.  Suggest you post the structure and some sample data.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14063445
I realize you are new here, so I will just ask that you please re-read the EE Guidelines regarding grading standards at:
http://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?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14072811
Thanks, I appreciate it.
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question