WIts End
-
Hi,
I've been struggling with a VB.Net 2005 project. I need to import a client file into SQL 2005. Currently my vb application calls a stored procedure, passing it variables from the 'Client File'. Its a bit messy but works well, when there is only 1 record, however, when there is more than one record i start running into problems. I have asked on the MSDN Newsgroups about importing a file with 20,000+ records (transaction records ) in it and was pointed in the direction of creating an XML file and importing that. I have created the XML file, but am still at a loss as to how to get the data from the XML file to the SQL Tables. Other suggestions were to use SSIS, but i wouldnt know how to 'parm' in the ClientFile name and get a result back ??
Sample of XML file
thanks for any input
-
If the VB program is working correctly why would the program 'run into problems' when you were reading more then one record? This does not sound like a SQL issue.
Is the a process that is repeated every night?
Will there always be 20k records?
What is the original format of the data? I am not a member of the XML saved the world club.
daralick
-
Hi Daryl,
I may not have explained my problem enough ...
Clients send in files during the day which need to be checked and imported into the sql server. When the files come into our FTP server it fires a vb.net application that checks bits n peices and then tries to import the data into two tables on the SQl server. currenty i read the clients file ( CSV format ) and the 1st 10 lines into variables which are passed through to a stored procedure to do testing and import in to a header table, the rest of the file is where my problem lies . The transaction records can be 1 to 20,000+ depending on the client. I tried reading these into an array but cannot pass the arrray into the stored procedure. On the MSDN Newsgroups it was suggested i used either XML ( which converting the client file wasnt a problem ) though im still stuck on parming this file into the stored proc, or use SSIS, again parming the file name in is causing a problem .. I need to get a result back from the stored procedure, so unsure how to proceed.. If there is only one record in the client file there isnt a problem, any more that where i run aground. The main problem is parming the 'transaction records' into the stored procedure or finding an alternitive way of dealing with these file and still being able to return a result
-
This sounds simple enough so here goes.
If your file is already in CSV, leave it. It's simpler.
The way I understand it is you want to take the values from the file and pass them to a proc.
There are two ways that come to mind.
BCP, Bulk insert and SSIS (3 I know
BCP:
BCP the file into a temp table and loop over it picking out the records one at a time and executing the proc.
Bulk insert:
Using bulk insert, the same as above. Cleaner if doing it from a SQL point of view.
SSIS:
Creating a package with a data flow task, use the Flat file source component. Add a OLEDB component which you will use to execute the proc and mapp the parameters (Values from your file) to it.
Have a look in BOL for examples. There are a couple which will help.
Post back if you get stuck.
THT
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply