I want to reuse an ssis package that was created by using the import wizard in a sql server 2008 r2 standard edition database. To accomplish this task, I have the following questions:
1. Is it better to save the SSIS package to the file system or the database and why? Can y0u give me directions how have to save and reexecute the package?
2. The input and output file names are the same except for having month/day/year as part of the file name. Thus can you tell me how to modify the SSIS package so the package will take file names that are dependent upon the date? Do I supply the SSIS package with parameters?
Save as file. Use bids, create a project add the package then you can amend and save it. For the file name I would use a variable which gets set. Then you can set that variable when executing the package or in the package using an expression. Be careful about this though. If you want to process all files that arrive then you would have trouble if the process doesn't run for a couple of days or you need to reprocess. Better to have two processes, one to poll the folder and add new file names to a table, the other to process files from that table in order, you can then use this for other dependencies.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
I have the following additional questions: 1. your statement, ' Use bids, create a project add the package then you can amend and save it'. Can I open the SSIS package in bids directly? Why would I create a project?. I am asking this question since I have only worked with bids for SSRS 2008 r2 reports. I have not worked with bids with SSIS package. 2. The files will be setup to run in individual SSIS packages. I am planning to do that since I need to select a particular tab in the excel 2003 workbook. Do you agree with me that files should be in separate SSIS packages? Why or why not?