Do you know the time frame  when the files come? Also, are you trying to automate the job for the load or it is an on demand approach?  I understand what you are trying to do but just wanted to know a little bit in depth so that i might be able to help you on this.

If you do not mind, are you working with Wells Fargo?

Thanks

No I am not.

I am not so much worried about the load part as of rt now. These group knows how to use Export and Import Wizard from Query Analyzer to load data from Excel. The problem is somtimes the format of the excel file (header name, and few things change). Also we don't know exact time when this file get downloads from FTP site. They chacek constantly to see files on folder, and oncethey see the new file, they proceed with manual load.

They are using Access as a front end application to connect to Sql serevr. All teh databases are no SQL SERVER and they use queries to pull data from this databases.

I have few more weeks before i move with these project, But these thursday i ahave meeting, so i just need to sound techy and ready for this project.

I know I can use SSIS packages for load, and also use File Wtacher Task in SSIS for files to download, but these is problem becoz we have to leave our package running 24/7 to use these file Watchet task

Thanks acbe for ur response

Thanks SHivaNagar!!!!! really appreciate that link.

The problem is I am not a code guy. I really don't like or rather best is i don't know coding. I work with SQL . I was thinking -- try to build a small GUI in VB and i think i have 9 or 10 queries and SPs to connect to SQL server.

If u have more link or give me some suggestions to where i can start learning from scrach. I think the tough part will be to build the front end application for me rather than connecting to database.

Tahnks againa.

The excel file not being in a correct format is a big bummer. In sap b1 for export they provide u with a template and you have to fill the data in the excel use their sql tool to insert data. I like the idea but its always a big problem copying data form one excel to another sometime you miss something and everthing screws up so i made my own tool in c# it loads up excel file and then you get to define which column map to which one in the template and sumbit buttons excutes it for appending it. you can add the rows to the table as a transaction so if something fails it rolls-over or u can add one row at a time(which take lots a time) and handle the insert error to output a file with all your problem rows. If i have some more time in future i will making a simulator which will tell you what rows has problems before you excute it.

pointer to make this tool. reading excel file is always slower than csv or a tab separated file. Use Datatable, use background worker process while loading files and while doing all the heavy process, otherwise ur GUI will hang. dont output the whole datatable, mine crashed on 65000 rows, create a preview of just 10 to 50 rows just to check the mapping.

if you are just making reports then try the sql reporting services or just make sprocs and load to one of the .net datagrid

ThAnks

Cyberdude & Shivanagar.

I guess we have very few people in Sajha that are related to IT or Computer STuffs.

Anyway, Lets make Sajha also one of the IT - computer related forum site.

Thanks dev/null,

What I was thinking was- --- Use WMI Event Wtacher Task in SSIS to look for files in folder, and then connect this task with my sequence conatiner, where rest of the logic fro load will be build. The problem is we don't know even estimate time that these files will be FTP ed. SO this means i have to execute my package for a very long period of time just to watch for files to come. The biggest issue---the excel file we receive doesn't follow any standard formats. So when we make this excel connection to load to SQL server , it gives all kinds of errors. ( what we are working on rt now is telling our vendor to send those files everyday in standaradized format).

the other issue with myself i cannot code. so i am worried about these GUI that i will build to connect to my SQL SERVER.

I am confused---enetrprise manager?.....we are moving from SQL 2000 to SQL 2005 for database. Now these group of people don't know lot on SQL server, so we have to build something like inetrface where they can enter some value and serach the data from SQL. Now they are using Access 2000 as a front end application.