April 7, 2011 at 2:11 pm
Could some guide me in the proper way to create an SSIS package which will do the following:
1. Connect to CSV file
2. Select MAX(ticket_no) from SQL Server table called Tracking
3. Loop through each record in CSV and retrieve fName, lName, and EntryDate column values
4. Use ticket_no, fname, lname, and entrydate values from file to insert a record into Tracking table
5. Then retrieve IDENTITY value for each newly inserted record
6. Pass IDENTITY and EntryDate value to another sproc/sql statement to insert a corresponding linked record in Category table
7. Lastly, I would need to pass MAX ticket_no and all data from CSV file, downstream, so it can be ultimately transformed (derived column, data type conversion, etc) and written to final table called Appointments.
NOTE:
Main parts which I need help on are Steps 3-6...I've got 1 and 2 already covered. I'm fairly new to SSIS and tried to use OLEDB Command and dabbled with Script Component but could not get it work correctly. Maybe there's a better way of accomplishing this...
Any feedback or suggestions would be greatly appreciated.
BTW...I'm using Visual Studio 2008 and C# as scripting language in VSTA
April 7, 2011 at 3:54 pm
Just a suggestion, to get you thinking of another method to use.
Import the CSV data into a staging table, and then using set based T-SQL (for example a Merge statement) to perform tasks 2 through 7.
Will likely be faster than looping through each row in the CSV file.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply