What should be...

No I haven't change anything in SSIS...

• This reply was modified 1 year, 11 months ago by  Jobs90312.
• This reply was modified 1 year, 11 months ago by  Jobs90312.

Something like this?

WITH GroupedNational_Identity AS (
SELECT uniqueid, natid_type, STRING_AGG(natid_num,'|') AS natid_num, active, individual_list_id_fk
FROM national_identity_test
GROUP BY uniqueid, natid_type, active, individual_list_id_fk
)
SELECT 'CNIC' as ID_TYPE_01,
 MAX(CASE WHEN NatID_Type =...

Something like this?

WITH GroupedNational_Identity AS (
SELECT uniqueid, natid_type, STRING_AGG(natid_num,'|') AS natid_num, active, individual_list_id_fk
FROM national_identity_test
GROUP BY uniqueid, natid_type, active, individual_list_id_fk
)
SELECT 'CNIC' as ID_TYPE_01,
 MAX(CASE WHEN NatID_Type =...

Use ISNULL

SELECT ISNULL(
(SELECT 'CNIC' as ID_TYPE_01,
       MAX(CASE WHEN NatID_Type = 'CNIC' THEN NatID_Num END) AS ID_NUMBER_01,
   'PASSPORT' as...

Not sure about SSIS specifically but in T-SQL the following seems to be equivalent afaik and unless it can be proven otherwise 🙂

select case when '1900-01-01'='1900-01-01'...

• This reply was modified 2 years ago by  Jobs90312.

This should work for you

SELECT 'CNIC' as ID_TYPE_01,
       MAX(CASE WHEN NatID_Type = 'CNIC' THEN NatID_Num END) AS ID_NUMBER_01,
  ...

If I run your code I get

Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@listid".

Is it possible to have some sample data...

• This reply was modified 2 years ago by  Jobs90312.
• This reply was modified 2 years ago by  Jobs90312.

Thank you for this but is there any video out there actually explaining all of these options ?

Start with the original source system - either implement daily full backups with frequent transaction log backups, or switch the database recovery model to simple and only schedule daily...

You can't "restore" the mdf file on its own. You can attach it though. Here's an example.

By the way, sounds like the database is in FULL recovery,...

You can't "restore" the mdf file on its own. You can attach it though. Here's an example.

By the way, sounds like the database is in FULL recovery, but...

The log file is an important part of the database as it is used to both roll forward and roll back on recovery. (ie When a transaction commits it...

Can you please tell me what...

What I am trying to do with above Update query is that I am updating value of Individual_Id of the those records whose Ind_PK_Value_Updated is null (basically...