March 8, 2009 at 7:13 pm
Oh okay. Thanks for the informative input Adi. I will try this and will let you know on my progress.
Thanks so much.
March 12, 2009 at 12:37 am
Hello guys,
There has been a changes with our design and i think it is good. Instead of having one internal file with multiple tables, the service beforehand created one file per table.
So on a certain directory the is batch header file which is an xml file and contains the path and filename of those batch internal files that belongs to a certain batch. Each batch internal file contains data for one table only and in CSV format. The structure would look like this now:
batch header file
<header>
<batch_id>...</batch_id>
</header>
<data>
<table>name of the table to be updated</table>
<file>file path and file name</file>
<table>name of the table to be updated</table>
<file>file path and file name</file>
<table>name of the table to be updated</table>
<file>file path and file name</file>
.....
</data>
batch internal file
batch_id, followed by a value (may be empty) for each value in the table followed by a comma (except for the last value in the record)
1,col1,,col2,col3,col4
1,col1,,,col3,col4
1,col1,,col2,col3,col4
1,col1,,col2,,col4
1,col1,,col2,col3,col4
.....
Each record in the internal file equates to a record in the table. There will be a separate file for each table.
I am planning on having an assembly (C# dll) to read the header file and traverse all the internal files and bulk insert it right away to the database. Is this the correct path on how to deal this kind of scenario? or if there is any better suggestion would greatly be appreciated.
Thanks for the help.
March 12, 2009 at 3:46 am
Hi,
Regarding this subject, I am battling in creating a proc to bcp distinct text files into distinct multi - tables by using one table as a parameter. The parameter table has all the table and file names. I have used cursors but their fail.
eg:
declare @filename varchar(100)
declare @bcpCommand as nvarchar(4000)
declare @defaultdirs as nvarchar(4000)
declare @dd as varchar(2)
--SET @bcpCommand = (' bcp General.dbo.'+@filename + ' in '+ @DefaultDirs +'\' +@filename +'.' + @dd + ' -T -f'+@DefaultDirs +'\Format\'+@filename +'.fmt' + ' -k -e'+@DefaultDirs +'\Error\'+@filename +'.err' )
--set @filename = (select DataSource FROM HO_Control_Table)
--select DataSource FROM HO_Control_Table
declare Next_Table_Cursor CURSOR FOR
select Filename FROM Parameter_Control_Table;
OPEN Next_Table_Cursor;
FETCH NEXT FROM Next_Table_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Next_Table_Cursor;
END;
CLOSE Next_Table_Cursor;
DEALLOCATE Next_Table_Cursor;
GO
Please help!!
March 12, 2009 at 4:18 am
Not with SQL Server on-board means. Thats the reason for SSIS. Sure you can write any external program or script to handle this.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 12, 2009 at 1:22 pm
[font="Verdana"]You should be able to bulk insert the header file into an XML column, and read it out from there. The second part just use normal bulk insert.[/font]
March 12, 2009 at 7:06 pm
Bruce W Cassidy (3/12/2009)
[font="Verdana"]You should be able to bulk insert the header file into an XML column, and read it out from there. The second part just use normal bulk insert.[/font]
Hi Bruce,
Do you have any example (working code) or reference on what you are trying to point out?
Thanks,
James
March 12, 2009 at 7:30 pm
[font="Verdana"]Reading over what you were proposing, I'm not sure it actually would add any value to load the XML into SQL Server. Processing it with your application is probably the better approach.
No, I don't have a working example of this. We have a tool that largely does this sort of thing for us, but it uses pre-generated code based on metadata, rather than generating the code on the fly.
[/font]
March 12, 2009 at 8:40 pm
Bruce W Cassidy (3/12/2009)
[font="Verdana"]Reading over what you were proposing, I'm not sure it actually would add any value to load the XML into SQL Server. Processing it with your application is probably the better approach.No, I don't have a working example of this. We have a tool that largely does this sort of thing for us, but it uses pre-generated code based on metadata, rather than generating the code on the fly.
[/font]
Oh okay. I already created an multi-threaded assembly to process the files and call a procedure to simply bulk insert it right away to the database. Thank you for the input Bruce and everyone. I really appreciate it.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply