August 14, 2013 at 10:24 am
in the Excel data source the incoming data will be called Field1,Field2,Field3
What you do with them is completely up to you and the dataflow.
You will have two different types of 'record' (row) in Excel: Those that have "Name" in the Field1 and those that have proper data. The conditional split will allow you to test the values in the row that is being processed and send them to different processes. In this instance, we are not interested in the header rows so you can simply discard the data from the data pipe.
If you have 32 'tables' of data, you would expect to lose 32 rows from the datapipe, so if 1000 rows went in, only 968 would come out. If you wanted to be really sure, you could send the 32 rows to a flat text file destination and you would then be able to read them and confirm the data.
Thinking about this, it might make more sense to prototype the dataflow task to send the data to a text file rather than to a database, this way you can visually inspect it and make sure that the data looks how you would expect.
If you want to PM me I woudl be happy to take your spreadsheet, make the formula / VBA changes and write a package that reads the data from Excel to a flat file. You would then just need to create the database connection in the connection manager and alter the dataflow destination from flat file to oledb.
You will need to let me know which version on Excel (2003/2007/2010) and BIDS(2005/2008) you are using as the configurations will be different depending on your combination. Also if you are planning to deploy the package on a 64bit server, there may be other settings that are required.
August 14, 2013 at 3:06 pm
I would use the a script transformation task inside a data flow task.
I have used this before for files that have different records types/number of columns in them.
Takes a little bit of research to set-up but is pretty reliable if you set-it up correctly.
Then you can load the data into a table like:
Record_Type (Header or detail)
Import_Column1,
Import_Column2,
etc (set up the max number of columns you will need)
The only difference I had was I was using Pipe delimited files not xls
Source Connection -> script transformation -> Database
Sorry I can't give much more detail but if you google this, there should be plenty of examples on how to set it up.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply