asked
SSIS Excel fields into variables and populate them into one row in database
I have a Excel file which I need to load each field into a single row in a database table. the fields are located like C3:C3, E2:E2, F7:F7 etc. you get the idea. How do I save the values in each field into variables and insert them into a single row? Please help. Thanks
One possible solution that I see is by using VSTO (Visual Studio Tools for Office) from a Script Component in the Data Flow. That of course means that you'll need to write some .NET code to retrieve the values from Excel, then output them on the output fields that you need to define in the Script Component.
DFT task which has: Excel Source:To read the excel Script Component: assign values to the varaiables Oledb Destination: Dynamically form the sql query using the variable value.
thank you very much for helping me out. I also feel Script Component will do the trick. But how do I get value from Excel into the variable? a sample code will be helpful. Thanks
Never mind. I figured it out. Here is a very useful link:
http://www.bimonkey.com/2009/09/the-script-transformation-part-1-a-simple-transformation/
