Best way to load data from text files

Hi,
I have problem I'm hoping someone can give me some pointers with.

I need to load data from several text files into one table. The format of the files are simple - each line is comma separated, with double quotes around each element e.g.

"parameter 1","value 1","parameter 2","value 2"...
"parameter 12","value 12","parameter 13","value 13"...

However, the files themselves will have different numbers of columns e.g file 1 may have 8 columns, file 2 may have 16 columns.

I'm going to load the data into a table that has at least as many columns as the longest file. The table columns are all varchar, and are named simply as [Col001] [Col002] [Col003] etc...

The first two columns of this table must be left empty during the load (I use these later on), so the data entry will start at [Col003].

My question is what is the best way to do this? I thought perhaps using a BULK INSERT in a stored procedure might do the trick, but I haven't used it before and haven't got very far. I gather another approach might be to use bcp utility. Someone has also suggested a DTS package, but the filenames will be suffixed with current date/time stamp, so i don't think that will work.

My preferred appraoch would be the BULK INSERT..but i'm open to any pointers.

Many Thanks
GregYou could use BCP or BULK INSERT but you would need to know, a priori, how many columns were in the current input file and then use a different format file that matched.|||I would bring them into a staging table as the first step. Then I would have a control proc count the number of commas in a row and then call a proc set up to handle that number of columns. The called proc would then put the columns into the table designed to hold the largest number of columns.

That way, if someone comes along later and adds another file with n more columns, you merely create another proc to handle n more columns, alter your table to hold n more columns, and modify your control proc to count up to n more columns and call the appropriate sproc.

Best way to handle License issues

Here is the scenario:

We are developing an ETL type data load application and we're thinking building a SSIS package to assist with this.

The application and data files to be loaded would be on the client Windows XP Workstation. The SQL Server 2005 instance would be on a networked server elsewhere.

The XP Workstation would NOT have sql server 2005.

What is the best way to handle this? Can this be achieved fairly easily?

Kevin

You’ll have to be a bit more specific on how you want to do your licensing... on a per seat basis? Per server?

No matter the model, I would highly recommend taking a read of this article on the mechanisms provided by the .NET Framework to help you implement a licensing mechanism.

|||I'm sorry I wasn't clear. I do not want to CREATE licensing for my application. I want to find out what the best, cost effective solution for me would be.

Example:
Buy 1 SQL Server 2005 and call SSIS packages from the Client?
I think in this scenario I would only need 1 SQL Server Standard license?

Kevin
|||Ahh... that I’m afraid I cannot help you with much... instead I would suggest starting with the SQL Server 2005 Licensing: Frequently Asked Questions if you have not already found it.|||

If the ETL application is to be on the client then you will need to have a licensed SQL Server install there - although you need only install SSIS and tools, not the server itself.

A better - and more economical - scenario for you may be to have SSIS running on the SQL Server box. No additional license required. You can create a SQL Agent job to execute the package and invoke that job remotely from the client using T-SQL.

Either way, of course, you will have data moving over the network from client to server - and that may be a bottleneck for you.

Donald

Best way to create SQL2005 DB from VB.Net

I have written a VB.Net (2005) program to load an empty SQL Server 2005
database from another data source. At the moment I create the empty SQL
database by manually running a DDL script in SQL Server Management Studio.
However, I need to modify the VB.Net program so that the database is created
programmatically before beginning the load process. My question is how best
to do this?
I know I can do this by cutting and pasting the DDL into Visual Studio and
converting it into a string. But this is an onerous task, and as the DDL is
still evolving I do not consider this an option.
Another option is to open the DDL file, read it into a string, and then
execute it. Has anyone done this? One divantage of this option is that I
need to ship the DDL as a separate file with my app and this leaves it open
to modification by the user.
If there is another way I haven’t come across I would greatly appreciate a
few pointers.You can create a vb application that accepts the user inputs like database
name, server name and other parameters and use SQL-DMO to creae the database
.
You can check this link for the code...
http://groups.google.co.in/group/mi...7dfc44c4bf8e926
and this link for the interface...
http://www.codeproject.com/useritems/SQLDBBackup.asp
Hope this was what you wanted.
"David" wrote:

> I have written a VB.Net (2005) program to load an empty SQL Server 2005
> database from another data source. At the moment I create the empty SQL
> database by manually running a DDL script in SQL Server Management Studio.
> However, I need to modify the VB.Net program so that the database is creat
ed
> programmatically before beginning the load process. My question is how bes
t
> to do this?
> I know I can do this by cutting and pasting the DDL into Visual Studio and
> converting it into a string. But this is an onerous task, and as the DDL i
s
> still evolving I do not consider this an option.
> Another option is to open the DDL file, read it into a string, and then
> execute it. Has anyone done this? One divantage of this option is that
I
> need to ship the DDL as a separate file with my app and this leaves it ope
n
> to modification by the user.
> If there is another way I haven’t come across I would greatly appreciate
a
> few pointers.
>|||I had a look at SQL-DMO in the SQL Server Books Online and quickly discovere
d
it has been superseded by SQL-SMO (SQL-DMO is a COM object, whereas SMO is
implemented as a .NET assembly).
Since I'm using VS2005 I'll look into SMO and will see how I go.
"Omnibuzz" wrote:
> You can create a vb application that accepts the user inputs like database
> name, server name and other parameters and use SQL-DMO to creae the databa
se.
> You can check this link for the code...
> http://groups.google.co.in/group/mi...7dfc44c4bf8e926
> and this link for the interface...
> http://www.codeproject.com/useritems/SQLDBBackup.asp
> Hope this was what you wanted.
>
> "David" wrote:
>|||Thats right. But implemetation in SMO hasn't got much documentation yet. I
had been trying to get it and didn't get much help.
Thats why suggested DMo. I apologise if I misled you :)
--
"David" wrote:
> I had a look at SQL-DMO in the SQL Server Books Online and quickly discove
red
> it has been superseded by SQL-SMO (SQL-DMO is a COM object, whereas SMO is
> implemented as a .NET assembly).
> Since I'm using VS2005 I'll look into SMO and will see how I go.
> "Omnibuzz" wrote:
>|||I have been experimenting with SMO, and while I can get it to create
databases, tables, etc, I can't work out how to submit a script file for
execution. Do you know how this is done in SQL_DMO (there is a good cross
reference in the docs)?
P.S. In my case I have to use a script file as the DB design is done in
Visio and the script is generated for me. To convert this to command using
the SMO objects would take forever, and I would have to duplicate changes
made in the Visio diagram.
"Omnibuzz" wrote:
> Thats right. But implemetation in SMO hasn't got much documentation yet. I
> had been trying to get it and didn't get much help.
> Thats why suggested DMo. I apologise if I misled you :)
> --
>
>
> "David" wrote:
>|||try ExecuteImmediate and the ExecuteWithResults methods in the dmo for
executing scripts..
Is this the one you are looking for'
--
"David" wrote:
> I have been experimenting with SMO, and while I can get it to create
> databases, tables, etc, I can't work out how to submit a script file for
> execution. Do you know how this is done in SQL_DMO (there is a good cross
> reference in the docs)?
> P.S. In my case I have to use a script file as the DB design is done in
> Visio and the script is generated for me. To convert this to command using
> the SMO objects would take forever, and I would have to duplicate changes
> made in the Visio diagram.
> "Omnibuzz" wrote:
>|||Did you try, for instance Database.ExecuteNonQuery or Database.ExecuteWithRe
sults?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:17A1C906-2BF4-4662-8ADC-6FA34C673214@.microsoft.com...
>I have been experimenting with SMO, and while I can get it to create
> databases, tables, etc, I can't work out how to submit a script file for
> execution. Do you know how this is done in SQL_DMO (there is a good cross
> reference in the docs)?
> P.S. In my case I have to use a script file as the DB design is done in
> Visio and the script is generated for me. To convert this to command using
> the SMO objects would take forever, and I would have to duplicate changes
> made in the Visio diagram.
> "Omnibuzz" wrote:
>|||Many thanks to you both (Ominbuzz and Tibor). Databases.ExecuteNonQuery work
s
like a charm.
The only thing I need to do now is work out how to get the script text file
into the compiled exe so that I don't have to deploy it and users can't
change it. But that would probably be better posted in the Vb.Net newsgroup.
"Tibor Karaszi" wrote:

> Did you try, for instance Database.ExecuteNonQuery or Database.ExecuteWith
Results?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:17A1C906-2BF4-4662-8ADC-6FA34C673214@.microsoft.com...
>
>|||Perhaps you can zip (or similar) it with a password and/or encryption and ha
ve your code unzip it.
I'm not .NET expert, though, so there are probably much neater ways of doing
this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:51F43AB1-FE3A-440E-BF67-F9A9859C80AE@.microsoft.com...
> Many thanks to you both (Ominbuzz and Tibor). Databases.ExecuteNonQuery wo
rks
> like a charm.
> The only thing I need to do now is work out how to get the script text fil
e
> into the compiled exe so that I don't have to deploy it and users can't
> change it. But that would probably be better posted in the Vb.Net newsgrou
p.
> "Tibor Karaszi" wrote:
>|||or you can just add the script as a string or a file your project resources.
http://msdn2.microsoft.com/en-us/li...resourcemanager(VS
.80).aspx
-oj