Hello to ALL IT people Out THERE !!!!!

 

MAy be in another couple of weeks , my IT group is gonna assign me a new project.

At presnt-- A small number of people uses Microsoft ACcess 2000 as their front end application to connect to SQL SERVER 2000. The Access is design with some tab buttons , which uses query to get data from sql server. They aslo use Import Export wizard to load data manually from excel to sql server 2000. and then move and archive those files. They say sometimes the file comes late and also format is not consistent.

Need FOR--- We are moving our database to SQL SERVER 2005. I think i can handle this part. ALso we want to get rid of Access as front end, becoz its slower. We are getting over the network to get data. May be write a small front end apllication in VB and connect to our SQL SERVER 2005 database. The store procedures and query used by access are also not optimized for performance.

I was thinking of replacing their manual load by SSIS packages, but according to them its hard becoz we don't know that what time  files will come. also sometimes format of excel file that we receive everyday varies..

I am not good at coding , but i can get help.

SO everyone out there plz help me to approach the problem, and also how can i sound techy and ready for this when i approach with this to my group on thursday,

Thanks

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

I dont know on which programming language you work to make the front end . But for both C# and VB.net the following webcast series of microsoft helps a lot about connecting data with VB or C# form in front end and sql server in back end.

this was quite helpful when I migrated to VB + SQl, rather than access.

http://www.msdnwebcastportal.com/DesktopDefault.aspx?tabindex=5&tabid=24

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.

When you download vb or c# express edition, you can  also download "Microsoft® Visual Basicâ„¢ 2005 Express Edition: Build a Program Now!", which has some projects, or if you already have VB, you can look at http://msdn.microsoft.com/en-us/vbasic/ms789080.aspx  where you can find some starter kits, which takes only half/ one hour but will be a starting guide for simple windows interface. Rather than the front end coding to how to access the data is harder part for me, but you are a sql guy, so u can do that easily I guess.

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.

 

Can they use enterprise manager as the front end? If you make stored procedures that they need, they just have to execute the procedure and they can visualize the data.

I think you can solve the import part using SSIS in 2005. You can define a conditional statement and check for valid data.
As per the triggering the import process, you can do this from scheduler to run every 30mins or so.

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.