Where should I store my SSIS data?
I have a slow custom datasource in SSIS DataA Flow Task. I need to run a package with multiple parameters
If I want to load data into DB using SQL data assignment (Bulk Insert), connection time
If I write data to a flat file, I cannot run multiple instances of the package as they will write to the same file. Can I pass the filename as a parameter somewhere?
Do I need to write a custom Script Destination as a last resort?
source to share
The Raw File purpose allows you to store the filename in a package variable. You can then use the / Set or / Conf command line switches for dtexec to either set a variable on the command line or to create a configuration file for this package run. Raw files are not suitable for manual processing, but if the data stream ends with one then the next data stream picks it up and starts with it, it is very easy to process.
source to share
You can use a package variable as the filename. Then you need to determine how you are going to receive the data. If you have a staging table that contains the file name you want to use, you can set its value in the job step before running the package. Then the packet can read this value. Want to get additional files? No problem, just take one more step which will update the table and run the package again. Alternatively, you can also configure packages to iterate over all the values in the table.
source to share