load file metadata after matching file name
Folder contain excel files ,yesterday any how I got name of excel files and loaded in sql server table.Now I have to match these file names with other tables that contain file name ,if file name exist than I have to load metadata of matching folder files into share point . How we can do this after matching ,how load file ,is it possible in ssis ,I think we have to use web srvice task but not expert in configuring.Thanks
December 16th, 2010 11:04am

correct me if i am worng , you want to load the files from a folder that are listed in a table? i am right? and i dont get the "....I have to load metadata of matching folder files into .... " part can you provide an example thanks Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
December 16th, 2010 11:31am

correct me if i am worng , you want to load the files from a folder that are listed in a table? i am right? yes you are right Yesterday ,I configured for each loop and execute sql task I got name of file ,I loaded file name in table ,now I am trying to match these file name with already existing table that contain file name column ,if file name match with existing table file name ,than I have to load data of files from folder in to share point .
December 16th, 2010 11:40am

Suppose folder 1 contain more than 20 excel files eg pqt001,abc002,klm003 etc ,I got these files name and created temp table in sql server and loaded file name Temp table file name (col name) pqt001 abc002 klm003 There is already one existing table in sql server database File name table File name (col) pqt001 abc002 klm003 opw009 ghj007 Now I have to match temp table file name with File name column of File name table .If file name exists lets suppose pqt001 than need to load data of this files from folder 1 into share point .
Free Windows Admin Tool Kit Click here and download it now
December 16th, 2010 11:52am

Step A -- get the list of files located in the folders (lets call them FileFolder) 1- use a loop to loop through the folder some thing like to http://plexussql.blogspot.com/2009/10/how-to-loop-through-files-in-ssis.html remove the script in the loop , you dont need it 2- assuming that you have a TEMP table in your SQL server lets call it "tblCurrentFileList" use a SQL TASK to truncate it first like ... Check STEP 5 only http://plexussql.blogspot.com/2010/06/looping-through-csv-files-using-ssis.html 4- insert each file name and its folder in the "tblCurrentFileList" with a SQL Task, its like a simple TSQL Insert 5- use a DFT in the DFT use a OLE DB that is linked to a INNER JOIN script of the table "tblCurrentFileList" and the other table that contains the list needed, you mentioned tha table name as ..... There is already one existing table in sql server database File name table File name (col) ........... 6- so now you have a inner join with in a OLE DB with a innerjoin and the next step is add a RECORD SET (see toolbox in SSIS) with is hooked up to a SSIS Object 7- so up to now you have the list on the files that are in the Table in SQL server within a SSIS OBJECT. 8- use a second LOOP to loop through the SSIS OBJECT (LOOP NUMBER 2) 9- SCR- Make a connection string for the current file 10- SCR find the Excel sheets for the current table and save them in another SSIS Object list 11- Another loop to loop though the SSIS excel sheets one by one (LOOP NUMBER 3) 12- DFT to import the information to the destination table 13- end LOOP 3 14- End LOOP 2 This is a big project, good luck Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
December 16th, 2010 12:27pm

up to 6 ,I did it .Used for execute sql task inside for each loop configure ,get file name and stored in sql server temp table .After that again used dataflow did lookup get match and unmatched file ,not sure where to store this because not need to store just again need to match from folder and load folder data. from 8 ,I am not getting what you are trying to say ,please can you provide little clarification.Thanks
Free Windows Admin Tool Kit Click here and download it now
December 16th, 2010 4:30pm

From 8 and on is basically looping through each file listed in the SSIS object , then setting the CONNECTION STRING on each loop for each file something like step 9 Loop , file 1 = Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\SSIS\file1.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;" Loop , file 2 = Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\SSIS\File2.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;" Loop , file 3 = Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\SSIS\File3.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;" etc..... so on each loop we want to connect to a different file step 10 looping through on each file you may want to loop thor each EXCEL SHEET , becaus the other sheets may contain data and save the list of sheets in a SSIS object ....... Step 11 -- ..... so that in step 11 by using another loop to loop through each EXECL SHEET one by one Step 12 --- get the data fron each sheet and insert into final destination yo can check http://plexussql.blogspot.com/2010/04/looping-through-excel-files-and-sheets.html whaich has PARTS of what you are looking for NOT ALL. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
December 17th, 2010 10:38am

up to 6 ,I did it .Used for execute sql task inside for each loop configure ,get file name and stored in sql server temp table .After that again used dataflow did lookup get match and unmatched file ,not sure where to store this because not need to store just again need to match from folder and load folder data. Now i dont understand you have a list of files in the tblListFile table and a list of files in the tblFileFromFolder table when you do an inner join the list will be the matched List, I dont know how you are providing the unmatched list?????? Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 10:43am

Suppose folder A contain more than 20 excel files ,I configured execute sql task inside for each loop and created temp table tblCurrentFileList and loaded file names in this table .Now I am thinking instead of loading file name into temp table ,load file name into variable .Than trying to use this variable file name for matching from table A of sql server contain filename column ,if file name match with table A filename column than trying to load data. Last part is really challenging after matching again need to load match file data from folder A to some location.Please let me know about this .Thanks
December 17th, 2010 11:39am

Ganesh9 I do understand what your gole is what you have to do is 1- get the list of the files from folder A and save the list into tblCurrentFileList , now tblCurrentFileList has 20 records because you mentioned you have 20 files 2- you mentioned "Now I am thinking instead of loading file name into temp table ,load file name into variable " well you are making it harder , if you want to do that you will need to do .NET coding and loop through the other Listed table and compare it with the tblCurrentFileList one by one , then remove the unwanted records from tblCurrentFileList and you have to do that by adding a Flag feild in the tblCurrentFileList table 1 for matching file and 0 for non matching files I am not suggestion what you have mentioned but its possible 3- what ever you do the provided list must be in a SSIS so that it gets looped through the FOR each object. 4- you mentioned "Last part is really challenging after matching again need to load match file data from folder A" A: well no thats not right because what happends if you have 4 files in table A and 3 file was provided , the ETL will faile It has to be the INNER JOIN list between the 2 tablesSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 11:52am

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

Other recent topics Other recent topics