March 10, 2012 at 4:56 am
Hi Everyone,
I have problem "Extra rows with null values are getting added in transformation for excel source to sql server table destination.
What am i doing ?--> I am looping through excel sheets using for each loop container. I was successful in pulling the data from source to destination , but two extra rows getting added with null as values for all the columns .
One more thing i fell like asking you is : I have 3 sheets in my excel file .Why values in 3 sheet are getting populated to destination table first .
The sequence is like this .. 3 sheet -> 1sheet ->2nd sheet
Values in 3 sheets got poulated first followed by 1 sheet and finally 2 nd sheet, why is this order followed, is there any way to set this order manually. What's happening internally ?
Any help would be really appreciated
Thanks and Regards
chaithanya M
March 12, 2012 at 12:44 am
Regarding the extra rows:
if you type something in a cell in Excel and delete it, Excel will "remember" that there used to be data and you'll get null rows in SSIS. You have to delete the actual rows to solve this issue.
You could also write a SQL clause to your Excel file and include WHERE myColumn IS NOT NULL.
Regarding the looping order:
you first need to explain how you configured the loop.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 12, 2012 at 6:19 am
Hi keon,
I used the following url to build my package : "http://stackoverflow.com/questions/7411741/looping-through-excel-files-and-tables-in-ssis-using-foreach-loop-containers".
I have 2 excel files in my folder .1 st excel file contains 3 excel sheets and 2 nd excel file contains 1 sheet. I wonder how come the order of parsing the excel sheets is changing for every run.
Something like, 3 rd excel sheet of 1 st file is getting populated first followed by the 1 st sheet of 2 nd excel file , and then 1st and 2 nd sheets of the 1 st excel file.
Please help me to understand this.
Thanks and Regards
Chaithanya M
March 12, 2012 at 6:23 am
I can't see the images here at work, so it's a bit hard to understand the configuration.
Is it really important that an order is preserved?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 12, 2012 at 6:43 am
Hi Koen,
Not really the order is preserved. Thank you very much for the answer to the first question 🙂
Regards
Chaithanya M
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply