January 8, 2016 at 8:28 am
We have a process where our vendor can send us either an html or excel data sheet 1x/week. We chose to receive the data in excel.
The current connection manager looks like this.
SELECT *
FROM [CMC_PL_by_MS$A2:F2]
Union all
select * from
[CMC_PL_by_MS$A4:F4]
Union all
select * from
[CMC_PL_by_MS$A10:F]
Normally this will return 6 column heading that are then mapped to columns in our data destination. However, occasionally the files we receive contain no data.
When this happens the package fails and reports a metadata issue "VS_NEEDSNEWMETADATA". I have identified the issue being that when we do get a file that is empty (3rd query above) but still has data description values in the Cells A1 and A4 (1st two queries) that the Microsoft JET connector does not see any data in the first row so it excludes columns 5 and 6 or F5 and F6 in this case.
So the good file preview looks like this... and has F5 and F6 columns populated since the 3rd query does ha
F1 F2 F3 F4 F5 F6
Facility NULL NULL NULL NULL NULL
Type NULL NULL NULL NULL NULL
12345 22222 smith john 04/22/14 06/13/14
24568 33333 ...
and the bad file will fail looking like this...
F1 F2 F3 F4
Facility NULL NULL NULL
Type NULL NULL NULL
NULL NULL NULL NULL
Whats odd is that F2, F3, and F4 columns are also all NULL yet they still come back as columns.
How can I force this to always return 6 columns?
January 8, 2016 at 9:14 am
The easiest thing to do is tell your vendor to stop sending you invalid files.
There are many ways of handling errors in SSIS. Not sure if you can come up with one that would detect if an error is caused by this condition and not another condition that you do not want to ignore.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply