Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 import csv files to SQL table using T-SQL

Author  Topic 

ziggy2015
Starting Member

11 Posts

Posted - 2015-04-01 : 07:54:42
Hello,
I am new to sql server programming. My problem is this:

I want to import csv file from a folder in a different domain to sql server stagging table in another network.

How do I declare variables for UserID and Password and database to allow sql server read files in the csv folder.

I want to use dynamic SQL and after import then, move files to archive folder.

Hope I explained myself

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-01 : 09:14:18
This sounds like a job for SSIS.

Under what account will the import run? If under the same account as that running the database engine, then *that* account needs permission to access the network share.
Go to Top of Page

ziggy2015
Starting Member

11 Posts

Posted - 2015-04-01 : 09:23:08
Hello
No. It is a different account. The folder has an account, the ssis is on different domain ad well ad sql server.
Thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-01 : 09:24:59
OK -- so the account under which the import will run needs to be a domain account with access to the network share holding the csv file.

I find it hard to believe that there is no SQL server in the domain where the import will run that does not have SSIS installed. Ask your DBA.
Go to Top of Page

ziggy2015
Starting Member

11 Posts

Posted - 2015-04-02 : 09:14:12
Hello

But, if I use ssis package how do I get the file name? The file name is always changing every month. Please help
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-02 : 10:30:10
Are the files dropped in a consistent shared folder or do they have a consistent naming convention?

If so, a SSIS For Each File container will do the trick
Go to Top of Page

ziggy2015
Starting Member

11 Posts

Posted - 2015-04-02 : 12:00:28
Hello, the files are dropped in the same folder but, different file names everytime. I am also using sql server authentication. Can this be scheduled to run automatically every month without manual intervention.

Thanks in advance
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-02 : 12:10:49
As long as the folder is known and you program the package to move completed files to an archive folder, this is easy to do in SSIS

If you build a SSIS package, you can schedule it using SQL Server Agent as desired
Go to Top of Page

ziggy2015
Starting Member

11 Posts

Posted - 2015-04-02 : 12:30:39
What about the file name which is not constant. This month, the name might be ts.csv and next month might be ts123.csv. Will I lose my connection when file names are different everytime.
Thank
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-02 : 13:17:45
Since the folder is known, in your For Each File container you look for "*.csv" then move the files to an archive folder as they are processed (preferred) or change the extension to "csv_done" or something like that. That way you only process them once.
Go to Top of Page
   

- Advertisement -