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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 import csv files to SQL table using T-SQL
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ziggy2015
Starting Member

United Kingdom
11 Posts

Posted - 04/01/2015 :  07:54:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/01/2015 :  09:14:18  Show Profile  Reply with Quote
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

United Kingdom
11 Posts

Posted - 04/01/2015 :  09:23:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/01/2015 :  09:24:59  Show Profile  Reply with Quote
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

United Kingdom
11 Posts

Posted - 04/02/2015 :  09:14:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/02/2015 :  10:30:10  Show Profile  Reply with Quote
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

United Kingdom
11 Posts

Posted - 04/02/2015 :  12:00:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/02/2015 :  12:10:49  Show Profile  Reply with Quote
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

United Kingdom
11 Posts

Posted - 04/02/2015 :  12:30:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/02/2015 :  13:17:45  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000