Dynamicallly Importing csv file into MS SQL

I have an app written in delphi v5 with a MS SQL Database and I'm struggling with an import feature i need

The user needs a function to import Contact data from a txt/csv file into the Contact Table

Details of feature:
the user enters the different parameters into the Delphi app e.g.
File Location
Files Includes Column Headings or not
Whether the file Comma or Tab Seperated
Mapping the fields

Then i need to use those parameters and run some sort of import routine putting the data into the specific table.

The tables consists of 3 fields - First Name, Surname, Mob Number - but these fields can be in any order in the file. for example Col 1 (in file) needs to go into Field 3 in DB. this is sorted in the Mapping Fields above

How do I do this using MS SQL??

I've been looking at using the BULK INSERT command e.g
BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
{
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
}
but at the minute i just get error -
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

Is this the correct command to use??

Do you know any websites that can point me in the right direction??

ThanksIt's syntax error.
Use () instead of {} as below:

BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)|||if you still hit error, try to see if you have grant the right permission in Windows for your csv files.

dynamic XML using for XML explicit

Hello everyone,
I had written a procedure to generate the XML in which the xml
structure was static.
eg.
<root>
<parent1>
<child></child>
</parent1>
<parent2>
<child></child>
</parent2>
</root>
for this i wrote two select queries and using UNION ALL and FOR XML explicit
generated it.
now the structure of XML to be generated is dynamic depending on the
parameter that is passes to the storedprocedure.
so the xml cud be <root><parent><subparent><child><subchild> for one
parameter
and
<root><child>
for another and <root><parent><child>
for other so it wud keep changing.
My question is how should i write the procedure to generate dynamic XML.
i can know the no of levels as soon as the parameter os given.
i'd be glad if somebody could tell me an approach to generate dynamic XML.
thanks and regards,
toby
Hello Toby,
If you only have those three possibilities to worry about, consider using
a T-SQL case based on the parameter value, then using having a clause that
executes the correct FOR XML EXPLICIT query as part of that. Otherwise, you're
asking for basically non-maintainable code.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||And if you use SQL Server 2005, you can use nested FOR XML PATH expressions
to make the code more modular and maintainable...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74ed968c7ba38196ca340@.news.microsoft.com ...
> Hello Toby,
> If you only have those three possibilities to worry about, consider using
> a T-SQL case based on the parameter value, then using having a clause that
> executes the correct FOR XML EXPLICIT query as part of that. Otherwise,
> you're asking for basically non-maintainable code.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

dynamic XML using for XML explicit

Hello everyone,
I had written a procedure to generate the XML in which the xml
structure was static.
eg.
<root>
<parent1>
<child></child>
</parent1>
<parent2>
<child></child>
</parent2>
</root>
for this i wrote two select queries and using UNION ALL and FOR XML explicit
generated it.
now the structure of XML to be generated is dynamic depending on the
parameter that is passes to the storedprocedure.
so the xml cud be <root><parent><subparent><child><subchild> for one
parameter
and
<root><child>
for another and <root><parent><child>
for other so it wud keep changing.
My question is how should i write the procedure to generate dynamic XML.
i can know the no of levels as soon as the parameter os given.
i'd be glad if somebody could tell me an approach to generate dynamic XML.
thanks and regards,
tobyHello Toby,
If you only have those three possibilities to worry about, consider using
a T-SQL case based on the parameter value, then using having a clause that
executes the correct FOR XML EXPLICIT query as part of that. Otherwise, you'
re
asking for basically non-maintainable code.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||And if you use SQL Server 2005, you can use nested FOR XML PATH expressions
to make the code more modular and maintainable...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74ed968c7ba38196ca340@.news.microsoft.com...
> Hello Toby,
> If you only have those three possibilities to worry about, consider using
> a T-SQL case based on the parameter value, then using having a clause that
> executes the correct FOR XML EXPLICIT query as part of that. Otherwise,
> you're asking for basically non-maintainable code.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Dynamic SSIS Configurations

Hey there,

I know that many articles have been written describing configurations for packages but I have yet to have found one that describes if it is possible to use SQL Server configuration type for a package that is to be tested on DEV, then UAT, then PROD boxes.

I would like to know if there's a way to store values in a config table in a database on DEV, UAT and PROD but never have to change anything in the package.

I mean, I wonder if I can pass a parameter that defines the server to go get the configurations from.

The 3 servers will contain a database with a config table named the same on each server but have different configuration values to point them to the proper sources and destinations depending on which server the configuration database resides.

Thank you,

Robin

Sure. This is how I do it. I have one XML file that is stored on each server that stores the connection string to the "configuration" database. Then each time we promote the package, no changes have to be made to it.|||

Awesome,

Thank you.

Also, for scheduling purposes, if I did not want to use an xml configuration file, I can use the /SET option with the dtexec.exe command and set the Package.configurationLocation (don't quote me on the name of the configuration) property as well right?

The DBA's over here would rather only have packages and not config files to move over from server to server.

Would that be an option as well?

Of course it would be defined in the documentation that the package expects a certain parameter.

Thanks

|||I can't comment on all of that, but the way we do it here, we create the XML config file once and never change it. So once it's on the server, we never change it. Of course we would have to change it if the database contained in the config file changes to a new name...|||

Again, Thanks for the rapid responses.

Cheers

Rob

|||

r4enterprises wrote:

Awesome,

Thank you.

Also, for scheduling purposes, if I did not want to use an xml configuration file, I can use the /SET option with the dtexec.exe command and set the Package.configurationLocation (don't quote me on the name of the configuration) property as well right?

The DBA's over here would rather only have packages and not config files to move over from server to server.

Would that be an option as well?

Of course it would be defined in the documentation that the package expects a certain parameter.

Thanks

You can use /SET or /CONN (I think) to set the connection strings in the packages through DTEXEC. However, be aware that if you run other packages from the first package, the connection string won't be passed along, unless you use Parent Package variables.

|||

Thats great!

Luckily, I do not use other packages inside the first one so I don't have to worry about it.

However, this is noted and I'll forward this to the documentation group so they make a note.

If I use /SET to set the connection string of the child package, would that work though?