| 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 myselfThanks |
|
|
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. |
 |
|
|
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 |
|
|
|
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. |
|
|
|
ziggy2015
Starting Member
11 Posts |
Posted - 2015-04-02 : 09:14:12
|
| HelloBut, if I use ssis package how do I get the file name? The file name is always changing every month. Please help |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 SSISIf you build a SSIS package, you can schedule it using SQL Server Agent as desired |
|
|
|
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 |
|
|
|
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. |
|
|
|
|
import csv files to SQL table using T-SQL