1

I am very new to SSIS still and mot much further along in SQL in general. I have created a Data Flow Task that pulls records which match a UNION ALL script from a table and dumps them to a .CSV file on the server. I then added an FTP task to upload the file to the recipient for processing on their end. One of the criteria for dumping the data is that a TransDate field in the table is blank and a StartDate field is equal to today. Because there are 4 StartDates and 4 TransDates, I am using the UNION All and assigning a "line #" based on which of the 4 sets of data it is. What I am missing is a way to verify the record dumped to the CSV and then set the TransDate which corresponds to that line. 

SELECT
    CallLog.CallID as Ticket
    , 1 as Line
    , CallLog.CustID as Store#
    , AcctNum as SoldTo
    , CAST(BillStart01 as DATE) as BillStart
    , Cast(BillEnd01  as DATE) as BillEnd
    , CostSheet01 as BillAmount
    , SKU01 as SKU
    , Term01 as Terms
    , CAST(EffDate01 as DATE) as EffStart
    , CAST(EffDate02 as DATE) as EffEnd
FROM
    CallLog, Detail, Subset
WHERE
    (CallLog.CallID=Detail.CallID and CallLog.CallID=Subset.CallID) 
AND CallType='Contract' 
    AND TransDate01 = ''
    AND Cast(SentOps as Date) = CONVERT(date,GETDATE()) 
    AND Month(RcvDate01) <= Month(GETDATE()) 
    AND YEAR(RcvDate01) = YEAR(GetDate())

UNION ALL

SELECT CallLog.CallID as Ticket
    , 2 as Line
    , CallLog.CustID as Store#
    , AcctNum as SoldTo
    , CAST(BillStart 02 as DATE) as BillStart
    , Cast(BillEnd02 as DATE) as BillEnd
    , CostSheet02 as BillAmount
    , SKU02 as SKU
    , Term02 as Terms
    , CAST(EffDate01 as DATE) as EffStart
    , CAST(EffDate02 as DATE) as EffEnd
FROM
    CallLog, Detail, Subset
WHERE
    (CallLog.CallID=Detail.CallID and CallLog.CallID=Subset.CallID) 
AND CallType='Contract' 
    AND TransDate02 = ''
    AND Cast(SentOps as Date) = CONVERT(date,GETDATE())
    AND Month(RcvDate02) <= Month(GETDATE()) 
    AND YEAR(RcvDate02)=YEAR(GetDate())

UNION ALL

SELECT
    CallLog.CallID as Ticket
    , 3 as Line
    , CallLog.CustID as Store#
    , AcctNum as SoldTo
    , CAST(BillStart 03 as DATE) as BillStart
    , Cast(BillEnd03 as DATE) as BillEnd
    , CostSheet03 as BillAmount
    , SKU03 as SKU
    , Term03 as Terms
    , CAST(EffDate01 as DATE) as EffStart
    , CAST(EffDate02 as DATE) as EffEnd
FROM 
    CallLog, Detail, Subset
WHERE
    (CallLog.CallID=Detail.CallID and CallLog.CallID=Subset.CallID)
    AND CallType='Contract' 
    AND TransDate03 = ''
    AND Cast(SentOps as Date) = CONVERT(date,GETDATE())
    AND Month(RcvDate03) <= Month(GETDATE())
    AND YEAR(RcvDate03) = YEAR(GetDate())

UNION ALL

SELECT
    CallLog.CallID as Ticket
    , 4 as Line
    , CallLog.CustID as Store#
    , AcctNum as SoldTo
    , CAST(BillStart 04 as DATE) as BillStart
    , Cast(BillEnd04 as DATE) as BillEnd
    , CostSheet04 as BillAmount
    , SKU04 as SKU
    , Term04 as Terms
    , CAST(EffDate01 as DATE) as EffStart
    , CAST(EffDate02 as DATE) as EffEnd
FROM
    CallLog, Detail, Subset
WHERE
    (CallLog.CallID=Detail.CallID AND CallLog.CallID=Subset.CallID) 
AND CallType='Contract' 
    AND TransDate04 = ''
    AND Cast(SentOps as Date) = CONVERT(date,GETDATE()) 
    AND Month(RcvDate04)<=Month(GETDATE()) 
    AND YEAR(RcvDate04)=YEAR(GetDate())
Order BY Ticket, Line

Any help / direction is appreciated.

Thank you,

Jeff

Improve this question

2 Answers 2

Reset to default
1

Jeff, Looks like you need to keep each of those 4 select statements in separate data source - so 4 OLEDB data sources. This seems to be the key to your solution.

Then after each data source, use a conditional split to determine if that row should be included in output or not. So, 4 conditional splits for each of the sources.

You would also need a derived column transformation right after the conditional split (if the criteria has been met) to set the TransDate.

Eventually, union them together using the Union All transformation and put the output of the union all to your csv file.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Anoop, since I am still new this doesn't make much sense to me. The select statements with the UNION ALL are pulling the records I need, I just need to go back and update the TransDate to show the record was pulled. I would think there should be an easy to say if Ticket = 123 and line = 1 appears in the csv, go to record 123 in the SQL table and set TransDate01 = today, and then work through all the records that were pulled for the csv file. I can do that between tables in SQL with UPDATE scripts, I just don't know how to do any of this in SSIS.
Can I say that you have two things to do - create a csv file and then also update a table that was used as a source? If yes, then can you not identify and flag those record using SQL (as you mentioned) and just create the CSV using SSIS? This would save you the round-trip.
If I am reading this correctly, you are saying I should update the TransDate field as a first step, and then pull the records that have the TransDate? Unfortunately, this is a process that would run several times throughout the day, probably every 15 minutes, and I don't want to pull the same record more than once, since this is for a billing application. What I am thinking now is to put in an interim step that dumps the records to a temp table in SQL, use that to validate the record to update the TransDate, and then dump the contents or the temp table to csv and then clear the temp table.
I've decided I was over complicating this and just need to break it down into steps like this: 1) query data and dump to temp table in SQL; 2) Run SQL UPDATE script to set TransDate based on comparison between original table and temp table; 3) Dump temp table to CSV; and 4) Run SQL DELETE script to clear the temp table. It may not be the most elegant or cleanest way, but I will get the functionality I need. Thanks for your efforts to help me.
Wonderful! A few questons just out of curiosity... 1. Did you create your temp table on the server where the original table resides? 2. Did you use Merge command for comparing temp and original? Merge is very efficient. Thanks for the update - that's very professional!
|
0

A more robust way to manage an export batch:

  1. Mark the records (in all four tables) you are about to export by updating all blank transdates with a marker date, say 2099-01-01. This identifies and 'freezes' the batch of records you are about to export.

  2. Now Export and transfer only records with transdate of 2099-01-01

  3. After the export and FTP processes are finished without error, update the records with marked Transdate to be todays date

  4. If the process did not occur without errors, set the 2099-01-01 dates back to blank.

This process allows you to isolate which records you are about to export. If you don't mark them first, then new (blank) records can arrive while you are still exporting an earlier set, and they are then incorrectly marked as 'exported' afterwards.

Improve this answer

2 Comments

This sounds like it would work well, too. I have pretty much finished building and testing my project at this point, but I will try follow this and see if there is any performance improvement. Thank you.
There won't be any performance improvement, just a reliability improvement. Good luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.