import excel sheet data into sql server table
Hi, i have four excel work books. all the excel books are like attendance register. First excel workbook contains name and 1 to 31 days ,each and every days considered as one column. Second excel workbook contains name and 1 to 30 days , each and every days considered as one column. Third excel workbook contains name and 1 to 29 days , each and every days considered as one column. Fourth excel workbook contains name and 1 to 28 days , each and every days considered as one column. Here File name is different for each excel workbooks. FileName is, First-Attendance-31 , Second-Attendance-30 , Third-Attendance-29, Fourth-Attendance-28. if FileName - Attendance-31 means, it contains the 31 columns. it like same for all excel workbooks. Here mainly considered as the FileName. i want January attendance details means,filename Attendance-31 excel workbook sheet import into sql server table. 31 days months means, filename Attendance-31 is import into sql table. 30 days months means, filename Attendance-30 is import into sql table. 29 days months means, filename Attendance-29 is import into sql table, this is only for leap year for Feb month. 28 days months means, filename Attendance-28 is import into sql table, this is non leap year for Feb month. i have created four data flow task, each data flow task contains the each excel sheets. i have run the package at a time only one data flow task is executed remaining is not running at a time. don't disable other data flow task. don't any change manually for every time. Regards, Abdul Khadir.
November 22nd, 2012 1:29am

Hi Abdul, Do you want to execute all dataflow simultaneously (All excels are processed at the same time)? If you want to do so... then instead of 4 dataflow you can use only one dataflow and put it to a each for loop so that all your four excels will processed one by one. If above is your exact requirement then please specify some more info like will it throwing some error and check the execution results to get more info about the process. sarat chandra sahoo
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2012 4:08am

Hi Abdul, Do you want to execute all dataflow simultaneously (All excels are processed at the same time)? If you want to do so... then instead of 4 dataflow you can use only one dataflow and put it to a each for loop so that all your four excels will processed one by one. If above is your exact requirement then please specify some more info like will it throwing some error and check the execution results to get more info about the process. sarat chandra sahoo no. only one excel sheet executed at a time. i want january data means, attendence-31 file name only executed and this data is moved to sql server table. remaining is not executed that time. Regards, Abdul Khadir.
November 22nd, 2012 4:42am

It means, it is depend on the excel sheet what you are having in the source folder. if the excel name is attendence-31 then for Jan it should process and so on for Feb as well. In that case you can use a for each loop till all excels been processed in the specific folder. For each excel name you can insert data to Jan,Feb etc.. to the DB by using the same dataflow and some variables.sarat chandra sahoo
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2012 5:09am

It means, it is depend on the excel sheet what you are having in the source folder. if the excel name is attendence-31 then for Jan it should process and so on for Feb as well. In that case you can use a for each loop till all excels been processed in the specific folder. For each excel name you can insert data to Jan,Feb etc.. to the DB by using the same dataflow and some variables. sarat chandra sahoo Hi, i didnt understood the you answer. i want clear explanations for my above requirements. my all the four excel sheets are available in one folder. i am waiting for your reply. Regards, Abdul Khadir.
November 22nd, 2012 5:13am

Sorry for late reply..:-( I was in the middle of some meeting. my steps are as below. 1. Use a for each loop for your source folder as below. Make the enumerator as "For each file enumerator" and browse the folder path and mention the file type. If you want the full path of the path then select 'full qualified' else others. Create a variable and go to variable mapping in 'for loop each editor' and set the variable to retrieve the whole path of the source excel sheet. 2. In the loop call a 'Data flow task'. Then in the data flow task u can read the excel and dump to your DB. But to do so, you need a connection manager which will dynamically connect to different database. To do so now you can use the variable which has the whole excel path. and we need to set that particular variable to the excel connection manager as below. 3. Use a excel source which will use the connection manager. now the excel source will provide the data from the excel sheet. 4. Now in order to split the excel path and get the name of the only excel file you can use script component/ derived column to do so and later u can process ur data. sarat chandra sahoo
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2012 7:43am

I am sorry for those pics.. I am resending those.. sarat chandra sahoo
November 22nd, 2012 7:44am

Hi, you might look at this post: http://social.msdn.microsoft.com/Forums/de-DE/sqlintegrationservices/thread/ae1057bb-30a2-4243-8d33-c3f95b7276b1 some weeks ago, there was a just very similar question... Best Regards HTH/Patrick
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2012 10:34am

Hi, you might look at this post: http://social.msdn.microsoft.com/Forums/de-DE/sqlintegrationservices/thread/ae1057bb-30a2-4243-8d33-c3f95b7276b1 some weeks ago, there was a just very similar question... Best Regards HTH/Patrick
November 22nd, 2012 6:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics