1

I have an SSIS package that creates to multiple pairs of files to be used as import sources for our ERP system. My problem is that in each pair, one has to start with the string HDR_ and other with LNE_.

While the remainder of the file names is not prescribed, but both HDR_ and LNE_ MUST end with the same string. I have tried using a string variable using the system time, but this will change between generating the HDR_ and the LNE_. Even when using only hours and seconds, there is a high likelihood of a mismatch.

Does someone have a solution?

Hadi
  • 36,233
  • 13
  • 65
  • 124
SQL-GBH
  • 47
  • 2
  • Can you give an example of what the output file name should look like? It seems that you could just use the date (e.g. HDR_20170105.txt) but I'm not sure if I am understanding the naming convention needed. – tonyd Jan 05 '18 at 21:06
  • See the approach via https://stackoverflow.com/a/29921654/181965 – billinkc Jan 05 '18 at 21:07
  • The example name you gave is correct. The issue is that the package will be generating multiple packages per day, and therefore overwrite could occur if I only use yearmonthday. By extending the file name to HDR_2017010504251234.txt) with the hours and minutes and seconds, the HDR_ and LNE_ files won't repeat, but may not get the same string. – SQL-GBH Jan 05 '18 at 21:12
  • One thought would be to use something from the source tables that uniquely identifies that set of files and append it to your filename. For example, if the output files generated each have 100 rows of data and there is an integer primary key from 1 to 100 then your file names would be HDR_20180105_1_100.txt and LNE_20180105_1_100.txt. With a primary key, you probably wouldn't need the date. – tonyd Jan 05 '18 at 21:37
  • I could use the order number of the first order in the source file if I can assign the result of a SQL query to a variable. Is that possible? – SQL-GBH Jan 05 '18 at 22:00
  • Problem Solved: I created a SQL task that queried the table for a value that will not repeat in subsequent data sets. This value was saved to a variable which was then embedded in the output file names. This eliminated the issue of the changing data and time. Thanks for your help. – SQL-GBH Jan 05 '18 at 23:02

0 Answers0