I wanted to be able to import Excel files to a generic working table, then use stored procedures to manipulate the data. I have a generic raw table with 60 columns, all varchar. I wanted to be able to have the SSIS package simply take the data from the Excel file and migrate into the raw table. I have a configuration file where I can pass in the directory of the file, along with the name of the worksheet to use.
I am using an Excel Source, Data Conversion, and OLE DB Destination object in the Data Flow. I have this embedded in a ForEachLoop as there can be several files in the directory.
Currently I get a series of errors when trying to automate, and what I find by trying a web search is not much help.
Thanks in advance