0

I have created SSIS packages which imports Excel files from a folder. The name of the Excel file changes based on months. e.g.: I:\Test\User_09-05-2016.xlsx or I:\Test\User_09-06-2016.xlsx etc.....

I want to create SQL jobs to run the packages because I will get a new file every month. I want to archive the excel file after successful package execution(to keep only one excel file in folder at a time).

How can I automate the process?

BIDeveloper
  • 2,628
  • 4
  • 36
  • 51
Apoorv
  • 1
  • 4

2 Answers2

0

Just use a ForEach container within SSIS.

Import each spreadsheet within that folder.

Delete (or move) each spreadsheet after processing.

BIDeveloper
  • 2,628
  • 4
  • 36
  • 51
0

Alternative approach to FE loop - define path and filename of Excel file on the package run.
If you know rules for naming Excel files, create a string variable Excel_FilePath containing complete path with Expression like [User::Folder Path]+"\\"+[User::Filename]+".xlsx". Then take your Excel file Connection Manager and add the following expression to the ConnectionString property "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Excel_FilePath] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
In Package properties - set DelayValidation=true.
More details on this approach with screenshots and exactly your question reviewed.

Community
  • 1
  • 1
Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • Thanks for your replay.... I have gone through with the screen shots which you provided. But i don't want to extract data from multiple files. I need to pull the data from single excel file which is having different file names (dates) for every month. – Apoorv Jun 09 '16 at 12:01
  • In the article author wants to process multiple files. In your case - do not use Loops and create ConnectionManager string as described. Another approach - create your package processing single Excel file and pass adjusted *ConnectionString* when your are calling this package from SQL Job. But in this case you have to dynamically create connection string in SQL Job, which can be tricky. – Ferdipux Jun 09 '16 at 12:13