This is a requirement, not a...

Use a query to get your data from SQL Server into SSIS and use CAST in the query to get the BLOB into string format

DROP TABLE IF...

Please provide your sample data in the form of CREATE TABLE/INSERT statements.  Also provide your desired results,  based on the sample data.

I have two unique keys in one table and I want to write a query that connects me to two unique keys and displays them in one record. Right...

That is a lot of data. Have you looked at the execution plan? Are the underlying tables appropriately keyed and indexed?

That made it more complicated than I had expected and I had to adopt a different method, unfortunately containing cascading CTEs. Does this get you close?

WITH BaseData1
AS...

Which column, or columns, of data should be considered when generating the Sales = 0 rows?

Well done on providing the sample data, though please use one or more code blocks next time.  In future, please use temp tables (avoids others having to create and drop...

OK, I see the problem. The query logic is such that the generation of a notional Sales = 0 row depends on column 'Data', not on EmpId.

In the case where...

Are you saying that the line in blue is incorrect?

Yes there is.

SQLPackage /Action:Publish, with appropriate parameters.

Set the CreateNewDatabase flag to false.

That looks trivial and is something which I suggest you attempt yourself.

Just add the columns to the BaseData CTE and then to the SELECT ... FROM BaseData.

Please read this article. The FORMAT() function performs very badly.

• This reply was modified 4 years ago by Phil Parkin. Reason: Fix typo

Like this?

SELECT t.VID
      ,t.OrdDate
      ,t.Prg1
      ,NewCol = (CASE
    ...

Your query does not parse (it has two WHERE clauses). But no matter, for (current) YTD, use something like

WHERE SomeDate >= DATEFROMPARTS(YEAR(GETDATE()),1,1) AND SomeDate <= GETDATE()