Currently What I have is:
bcp "select * from TEST.dbo.HPA_1 WITH (NOLOCK)" queryout D:\Gift_Voucher\HPA\test1.txt -T -c -t
When I run the above code for the first time it works fine and it creates the textfile test1.txt, but When new data is added in the table I want to create new textfile something like test2.txt and test3.txt and so on without changing the code in bcp. Is there anything we can do here?
Change your BAT file to replace the output file path with a command-line parameter:
bcp "select * from TEST.dbo.HPA_1 WITH (NOLOCK)" queryout "%1" -T -c -t
In the SSIS package, create a string variable for the desired output file path. Set the Execute Process Task Executable property to "cmd.exe". On the Expressions page, set the Arguments property to an expression that builds the command with the BAT file path plus the output file argument. The example below also encloses the values in quotes to handle whitespace in the paths:
Set the variable value to the output file path variable in your package prior to executing the task. This can be done in package code or set the value via SSIS configuration.
Note that you could accomplish the same functionality in a Data Flow task instead of shelling out to BCP. That leverages the native export capability of SSIS.
BCP? from Command Prompt or T-SQLxp_cmdshell?