Why use log files, when you can log natively to SSISDB, as described here? Much easier, in my opinion, to keep all of the logging info in one place.

What is the requirement exactly?

Is it as simple as replacing an asterisk with five zeros, or do you want something which fills out the numeric part with leading zeros, to...

As suggested above, this is a generic error. Improving the error-checking/reporting code in your package is a very good idea.

Also, if you check the All Executions report for the package,...

What sort of security...

One option is using SQLCMD mode

USE DB1;
:r C:\SqlScript\YourLargeScript.sql
GO
USE DB2;
:r C:\SqlScript\YourLargeScript.sql
GO
USE DB3;
:r C:\SqlScript\YourLargeScript.sql
GO

(Example stolen from here.)

--Deleted

• This reply was modified 5 years, 9 months ago by Phil Parkin.

I shouldnt have a problem joining the tables but my main concern was when i get to the end and write the where filter clause do I have to...

Try using this query to get a count of rows in a table. It's super fast:

SELECT TotRows = SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('{schema}.{tablename}')
    ...

With >13,000 points, you should know how to format code snippets for presentation to others.

Here is one way. I switched things around a bit and used a temp table to show a working query:

DROP TABLE IF EXISTS #tmp;

CREATE TABLE #tmp
(
 ...

Well done on getting it working ... and if you don't need the time component, that's an even better solution than what you had originally planned.

The solution without STRING_AGG is much less elegant. Here is one way:

WITH ErrData1
AS (SELECT t1.tran_no,
           t1.ref_no,
 ...

WITH errs
AS (SELECT td.tran_no
          ,ref_no = STRING_AGG(td.ref_no, ',')
    FROM
    (
  ...

I can get these results, can you explain what is wrong with them? I am trying to follow the conditions in your code, and this is what I get so...

What does this 'overnight refresh' process actually do?