March 13, 2017 at 6:30 am
Hi,
We have a requirement to import comma separated file into SQL SERVER, new file is generated after every 1-3 minutes and file data needs to go into 3/4 different tables.
We are thinking about creating a data flow SSIS package to import file data into different tables in SQL SERVER, the other option we have is to create a .Net batch file which will read file and insert data into SQL Server tables.
Can anyone please advise if we can create SSIS data flow package in any SQL SERVER express addition ? Also is there any other better way of importing data (from one file) into different SQL tables? (We want some batch process which can read data from one file and insert them into different SQL tables , also either delete the file or move the file into different folder once the data has been read)
March 13, 2017 at 7:17 am
momer79 - Monday, March 13, 2017 6:30 AMHi,
We have a requirement to import comma separated file into SQL SERVER, new file is generated after every 1-3 minutes and file data needs to go into 3/4 different tables.
We are thinking about creating a data flow SSIS package to import file data into different tables in SQL SERVER, the other option we have is to create a .Net batch file which will read file and insert data into SQL Server tables.
Can anyone please advise if we can create SSIS data flow package in any SQL SERVER express addition ? Also is there any other better way of importing data (from one file) into different SQL tables? (We want some batch process which can read data from one file and insert them into different SQL tables , also either delete the file or move the file into different folder once the data has been read)
SSIS is not an option for you with SQL Server Express Edition.
You should also consider using BULK INSERT. Your process could be something similar to this:
1) Import data to (truncated) staging table
2) Run stored proc to move imported data to target tables
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 13, 2017 at 7:26 am
You can use SSIS to connect to SQL Express, however, you can't run packages on it. If your intention is not for SQL Server to run the package (running it from VS), then you should be able to achieve what you want. If you want to schedule the task, how are you planning to achieve this without Agent? As you state that the files will be new every 1-3 minutes, it sounds like you'll need to keep running the task, which you won't be able to do on SQL Express.
If you don't want to use SSIS, I believe that OPENROWSET (Transact-SQL) and BULK INSERT (Transact-SQL) are both available on Express (someone please correct me if I'm wrong), so these may need your needs. However, you'll need to consider things like file archiving, getting the name of the file (as I assume it will change each time), etc.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply