I am trying to create a package that reads from csv, pass the id to an oledbsource sql command (select phonenbr from person where personid = ?) and insert into oledbdestination. I don't know how to pass the personid from the csv. I am very new with SSIS 2005. Could you please explain with step by step? Thanks.
Hi Dinakwan, and welcome. 1. in your blank project add a data flow task and double click it to open 2. Add 1 flat file source (for the csv file) and fill in the appropriate fields for the connection (I am assuming that you know how to do this). 3. Add 1 OLE DB Source control to the data flow. This source will hold all of your phone numbers. (There is a more advanced way to do this with a lookup, but for beginners this is the best way). 4. Create the connection for the OLE source created in 4, and select SQL Command from the Data access mode drop down list. 5. Next, in the SQL command text box area, write "select personid, phonenbr from person" to get ALL of your phone numbers. 6. Add two sort controls to the data flow and place them under each source control and connect them. 7. Sort each sort control by personid. (each dataset has to be sorted before a join) 8. Add a Merge Join control. Connect the CSV sort to the merge join and select "Merge Join left input" from the input DDL. And connect the other sort. (the second will not provide the option of selecting the input as there is only one input left). 9. Open the Merge Join control and make sure that personid is the join. If you want only people who have a phone number included, then make sure the join type in the merge join type is an inner join, if you want to insert people you do not have a phone number as well (NULL) then make the join type a left join. 10. Add the fields that you need to pass through the join, i.e. at least phone number and person id and any other fields from the CSV source you need. 11. Now you have a dataset that contains personid and phone number and you can create an OLEDB destination control and insert the dataset. Before you insert the data, I always add a data viewer to the dataflow before the OLEDB destination. Right click the green data flow pipe and select data viewers, then click on add and select the grid tab and click OK. When your SSIS skills are more advanced, then i suggest that you have a look at using lookups instead of the OLDESC source with the phone numbers, but there are a few gotchas when you use lookups. Good luck and happy SSISing, let me know if you need some more help