You should declare your table variable outside of the WHILE loop, otherwise it will keep getting reinitialized.

Suggestion #1, please format your code in future. See how much easier it is to read ...

DECLARE @StartDateTime DATETIME = '2019-09-01T00:00:00';
DECLARE @EndDateTime DATETIME = '2019-09-01T23:59:59';
DECLARE @Location VARCHAR(50);
DECLARE...

Yes, that's the way to go.

Though having Development and Production on the same server makes me shudder.

Ok, I’m officially envious.

how well thongs must be organised at your place, if this kind of events come as a surprise for you?

i can hardly remember when things went...

TFS cannot deploy packages on its own.

You can use Visual Studio to create an ispac (by building the solution containing the packages) from whichever branch you are interested in, and...

When I am running the SP in SQL, its running absolutely fine and giving me message "1 row affected " in message window.

Note that I am already using "SET...

I'm pretty sure the answer will be no. As the contents of packages are encrypted inside SSISDB, there would be no way of interrogating them.

If you don't post your code, we can't find syntax errors in it.

Might fix them up going forward, but won't solve the immediate problem, which sounds like it has...

Are you talking about passing in the start and end dates to the master package as parameters?

If so, set them up as, erm, parameters in the master package.

And then, you...

Like this?

DROP TABLE IF EXISTS #temp_Samples;

CREATE TABLE #temp_Samples
(
    Guid UNIQUEIDENTIFIER NOT NULL
   ,LastDate DATETIME NOT NULL
);

INSERT #temp_Samples
VALUES
('D87BF755-44C2-415A-A241-1512409F52E3', '2019-07-10 20:09:32.000')
,('929DF755-44C2-415A-A241-1512409F52E3', '2019-09-17 20:19:01.000')
,('42A2F755-44C2-415A-A241-1512409F52E3', '2019-09-17...

• This reply was modified 6 years, 9 months ago by Phil Parkin. Reason: Changed MaxDate to MinDate ... made a bit more sense

Can you provide sample DDL and data, please? In the form of INSERT statements.

Advise what, exactly?

One option would be to write them out as 4 separate files then merge them or write them out with different targets to the same file and the first...

Can you provide sample DDL and data, please? In the form of INSERT statements.