Software

Ask something
Question Locked

SSIS

0 helpful
2 replies

by cecil1982

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Hi everyone i need help, i am trying to import data from excel into MSSQL database,but what i want to accomplish is to read each of the incoming name of the excel spread sheet and create a table based on the name of the incoming spread sheet, and then import that data into the created table, and after that i would like to split that data, so can anyone out there help with the process of creating the table by reading a name of the incoming excel spreadsheet? please please help

2 replies

by tony hopkinson

e.g. If they simply appeared in a specific directory.

Yoi could use XP_CmdShell to do a Dir *.xls into a temporary table #FileToProcess and go from there.

However by default shell access is turned off, be very careful if you turn it back on.
It means any command line can be executed by a sql statment, given the service account has permission to accesit and it's files.

Also the account running the job needs permission(possibly over the network) to access that directory.

Another way is to write a wee program / batch file that will do the dir and then call an SSIS job with a the filename as a parameter.

Another possibility might be to put an on close macro in the worksheet that does an insert into a FileToPrcess Table. Then schedule a job to process it's contents.

Put a lot of belt and braces in this, particularly coping with sending the same file again, or SSIS catching it mid change.

Hope there's some clues in here for you.

This discussion is closed to new replies.