I have a slow custom data source in a SSIS Dataa Flow Task.I have to run the package with multiple parameters
If I want to upload data to a DB using SQL Data Destination(Bulk Insert) the connection times out
If I write the data to a flat file I cannot run multiple instances of the package, since they will write to the same file. Can I pass the file name as a parameter somewhere?
Do I need to write a custom Script Destination as the last resort?
A Raw File Destination allows you to store the file name in a package variable. You can then use the /Set or /Conf command line switches of dtexec to either set the variable on the command line, or bring in a configuration file for that run of the package. The Raw Files aren't great for manual processing, but if your data flow ends in one, then the next data flow picks it up and runs with it, it's very easy to handle.
You can use a package variable as your filename. Then you have to determine how you are going to retrieve the data. If you have a staging table that holds the filename that you want to use, then you can set its value in the job step before you run the package. Then the package can read that value out. Want additional files? No problem, just have another step that updates the table and run the package again. Alternatively, you could also setup the packages so that it will iterate those all of the values within the table.