Wednesday, February 15, 2012

Excel into Temp table and validations in SSIS

i have an excel sheet with about 30 columns of data ...i want to validate all the data in these cells of the excel through SSIS.
I want to get this data to a temporary table before running my validation stored proc...how do i get this data from the excel to the temp table , this temp table should accept all the data from the excel file in whatever form it is there should be no rows that get discarded while filling this table from the excel.
Moreever how do i get the column header data if the first row in the excel contains Column names how do i get these names and validate them if they are conferring to a
set of names.

Excel sheet ::

ColName1 ColName 2 ........ColName30

ColData11 ColData12..........ColData130

.....
..... ..... .. ...
..... ..... .. ...
Temp table ::

ColName1 ColName2 .. ...... ColName30 IsValid Description


Also can i do validations like Datatype and Length directly in SSIS ? or do i need to do it with a stored proc
Please Help....

Thanks
Clayton

Hi,

In SSIS, when you are transferring data from one Excel File to Another File you have to create an Excel Connection Manager for Source and Destination and in that you have an option as "First Row has Column Names".

But I am not sure about the validation at this point of time. Will verify and let you know that.

Thanks,

Prakash Srinivasan

No comments:

Post a Comment