Import multiple csv into multiple tables

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecietedChicagoboy27 (jeremy.bird@.gmail.com) writes:

Quote:

Originally Posted by

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecieted

You could use BCP or BULK INSERT, but it appears that you would have to
add quite some control code on top that.

A better alternative could be to turn to SQL Server Integration Services,
which is what the Import Wizard uses. Unfortunately, though, I am
completely unexperienced myself with SSIS, so I cannot assist further.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

import MDF and LDF backup from SQL2000 to SQL2005

hi,

i have six files ( 3 MDF and 3 LDF) from a backup of a MS SQL 2000 server.

I installed MS SQL 2005 and wanted to Restore these Dataset. but they cant be accessed. doesnt SQL 2005 support these files ?

How can i use these files to access them with SQL 2005 ?

Thx,

Hello -

The files you're talking about are the "raw" data files that SQL Server uses, not a backup. When you take a backup from within SQL Server, files are acutally packaged a little differently, into a .bak file. When you use the BACKUP DATABASE command in SQL Server, it will create these files for you, which inlcude all of the "raw" files. You can then use the RESTORE DATABASE command to restore them to another (or the same) SQL Server.

All is not lost, however. If you have the MDF and LDF files, you can "adopt" these files directly into a running SQL Server system. That is called "attaching" a database. You can read more about that here:

http://msdn2.microsoft.com/en-us/library/ms190794.aspx

Buck Woody
http://www.buckwoody.com

Import data from sql2005 into sql mobile

Hi,

i need to import data from sql 2005 into sql mobile, is there anyway to do it?

Need help urgently....

thanks

Joel

There are various options available:

Remote Data Access (RDA).

SQL Server Integration Services (SQL Server 2005 Standard or higher)

3rd party tools from for example www.primeworks-mobile.com

import data from ODBC to SQL2005

Hi, there;
I want to importing data from ODBC, I created DataReader Source which use a .NET Provide \Odbc Data Provider and connected successfully. My destination is a OLE DB Destination that points to SQL2005. I set the SQL command as "SELECT * from ....".
I also have problem to create new table in SQL2005 using SSIS Import and Export Wizard, it doesn't know the source table table schema (two date type column). So I create the new table manully and run the package, I got error:

SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02090F5 at Data Flow Task, Source - Query [1]: The component "Source - Query" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, Destination - PITest CMF [169]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, Destination - PITest CMF [169]: The final commit for the data insertion has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - PITest CMF" (169)" wrote 0 rows.
Task failed: Data Flow Task
SSIS package "Package1.dtsx" finished: Success.

Can any one know what's wrong here?

And it is quite pain that you have to specify the table name every time you want to create a new table in SQL2005. It is so easy in SQL2000!!!

Thanks.Did you verify that your source query worked and the column mappings metadata is correct? That's where I would start looking.

Import CSV file data from URL/HTTP

Hi,

I'm truly a newbie to this, so please ignore any stupidity :-)

I am trying to (in SQL2005 - integration services)

1) Import a CSV file over http (using an URL)
2) schedule the job (so it is done regularly)
3) *bonus* edit the URL dynamically

I have a web link, e.g.

http://dummy.com/cgi-bin/run.cgi?from=050101&to=050102

when accessing this "page" the result is sort of a CSV file. It looks something like this:

Date;cust;id;...
20050101;CLIENTA;0121210310; ..
20050101;CLIENTB;238241268;...
...

Step 1) How can I import this to be used in a Data Flow task (or similar)?I have managed to set up a HTTP Connection Manager, but I can't seem to use this as a "Data flow source"!? The "Web service task" seem to be the only task accepting a http conn. manager as input, but that requries a wsdl file and does not seem to be the right solution for me.

Step 2) When succeeding with step 1), how can I automate the solution? so the web link is accessed every day.

Step 3) Ideally, I would like to alter the weblink daily (by changing the dates in the link). How can this be done?

Hope someone can help me!

Cheers,

Carl

One more thing that might help:

What I am trying to do is really imitate the "web query" functionality in Excel.

|||

You can download a file using the webclient in a script task

http://www.sqljunkies.com/HowTo/A77EA078-79A6-48D4-B55C-14D103820413.scuk

Then use a data flow as normal.

Its good splitting the download and flow so that you can run the flow without having the to download the file, and also means you don't have to write a custom source for the data flow.

|||

Thanks a lot

This helped me get started. After figuring out how to add variables to the project I managed to do what I want!

Import CSV file data from URL/HTTP

Hi,

I'm truly a newbie to this, so please ignore any stupidity :-)

I am trying to (in SQL2005 - integration services)

1) Import a CSV file over http (using an URL)
2) schedule the job (so it is done regularly)
3) *bonus* edit the URL dynamically

I have a web link, e.g.

http://dummy.com/cgi-bin/run.cgi?from=050101&to=050102

when accessing this "page" the result is sort of a CSV file. It looks something like this:

Date;cust;id;...
20050101;CLIENTA;0121210310; ..
20050101;CLIENTB;238241268;...
...

Step 1) How can I import this to be used in a Data Flow task (or similar)?I have managed to set up a HTTP Connection Manager, but I can't seem to use this as a "Data flow source"!? The "Web service task" seem to be the only task accepting a http conn. manager as input, but that requries a wsdl file and does not seem to be the right solution for me.

Step 2) When succeeding with step 1), how can I automate the solution? so the web link is accessed every day.

Step 3) Ideally, I would like to alter the weblink daily (by changing the dates in the link). How can this be done?

Hope someone can help me!

Cheers,

Carl

One more thing that might help:

What I am trying to do is really imitate the "web query" functionality in Excel.

|||

You can download a file using the webclient in a script task

http://www.sqljunkies.com/HowTo/A77EA078-79A6-48D4-B55C-14D103820413.scuk

Then use a data flow as normal.

Its good splitting the download and flow so that you can run the flow without having the to download the file, and also means you don't have to write a custom source for the data flow.

|||

Thanks a lot

This helped me get started. After figuring out how to add variables to the project I managed to do what I want!

import an xml-file to sql2005

Dear all,
I'm trying to import an xml-file by passing the path i.e.
"D:\sql\project\bill_count_1.xml" to an db-procedure as in-param.
As a result whole the file-content should be saved in one column.
using SQL-2005.
The table looks like
create table test
(
id identity
, doc xml
)
You can solve the problem with openxml, openrowset, bulk insert and bcp
if you use the path as a string directly in the code but I could'nt
manage the import by sending the file-path as in-param to a db-proc
Thanks,
Every kind of help appr.
BR
AnwarHello anwar,
There's isn't any magic proc that does this for you. it can use dynamic SQL
inside of your own proc and openrowset to make this happen.
Cheers,
Kent Tegels
http://staff.develop.com/ktegels/

import an xml-file to sql2005

Dear all,
I'm trying to import an xml-file by passing the path i.e.
"D:\sql\project\bill_count_1.xml" to an db-procedure as in-param.
As a result whole the file-content should be saved in one column.
using SQL-2005.
The table looks like
create table test
(
id identity
, doc xml
)
You can solve the problem with openxml, openrowset, bulk insert and bcp
if you use the path as a string directly in the code but I could'nt
manage the import by sending the file-path as in-param to a db-proc
Thanks,
Every kind of help appr.
BR
AnwarHello anwar,
There's isn't any magic proc that does this for you. it can use dynamic SQL
inside of your own proc and openrowset to make this happen.
Cheers,
Kent Tegels
http://staff.develop.com/ktegels/

import an xml-file to sql2005

Dear all,
I'm trying to import an xml-file by passing the path i.e.
"D:\sql\project\bill_count_1.xml" to an db-procedure as in-param.
As a result whole the file-content should be saved in one column.
using SQL-2005.
The table looks like
create table test
(
id identity
, doc xml
)
You can solve the problem with openxml, openrowset, bulk insert and bcp
if you use the path as a string directly in the code but I could'nt
manage the import by sending the file-path as in-param to a db-proc
Thanks,
Every kind of help appr.
BR
Anwar
Hello anwar,
There's isn't any magic proc that does this for you. it can use dynamic SQL
inside of your own proc and openrowset to make this happen.
Cheers,
Kent Tegels
http://staff.develop.com/ktegels/