July 6, 2011 at 8:15 am
I am trying to create a package with following steps.
a) a SOURCE connection which will pull one client at a time from a table.(using dataflow)
b) for each client selected in above process get the details from other transaction table.(using executesql task)
c) get the transaction details inserted into destination.(using vb scripting)
Stepa) is what i am trying to work on. even if i pass 1 client ata time after stepc how it can go back to step a for a reiterative process and pick up the next client.
ANy thoughts, ideas, links, examples or suggestions are always appreciated. TIA
July 6, 2011 at 8:23 am
Is there any reason why you want to do this one client at a time? Can you give an example of some rows in the tables, please? Are they on the same server? What is your destination?
John
July 6, 2011 at 8:33 am
Thank you for the response.
Yes we prefer to do it one client ata time reason is for each client there couldbe a million transaction records so when pulling data from source(sql server ) we want to limit the no of rows based on clinet selected.secondly destination is Oracle so had to use a vb script as structure on source and destination is not 1-1.
client table structure is something like this
Clientid
clientname
Release Date
where for a relese date there could be many clients
data will be something like this
clientid clientname releasedate
1 AAA 12/12/2011
2 BBB 12/12/2011
3 CCC 12/12/2011
4 DDD 2/2/2012
5 EEE 2/2/2012
so ideally when choosing client in stepa we are looking at 1st 3 recs if going for release date as 12/12/2011. thanks
July 6, 2011 at 8:44 am
Your details are still a bit sketchy, but given what you've said, I think this is how I'd do it. Write a query that joins the two tables and puts the data into a staging table that has the same structure as the Oracle table. No looping required. Copy the data from the staging table to the Oracle table with a dataflow task. If you're worried about copying too much data at once, do it in batches.
John
July 6, 2011 at 9:07 am
Just to make it simpler how is it possible to make an SSIS pckg as an iterative process for say 10 times for 10 set of clients
July 6, 2011 at 9:12 am
If you insist on looping in that way, just use an Execute SQL task to get your clients into a variable, then use a ForEach Loop to do the work for each client at a time.
John
July 6, 2011 at 12:58 pm
Thanks . Thats what i am trying now but do you have any link which could provide some insite on how it can be done. Thanks a lot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply