Since the original writing of my post:
The limit for file transfer has been raised to 50-GB. Given that change, when I went to repeat this process for a different subsidiary of the company I used this alternate solution without having to use SSIS.
- I downloaded the initial files and unzipped them,
- Copied them up to my S3 bucket in AWS.
- Ran this code to transfer them to the RDS server at D:\S3\
- BULK Inserted the data into staging.
- Normalized the Data
- Pushed it out to the production database
PowerShell Commands to move the files to S3
# Move the files to S3 bucket on AWS
aws s3 mv "D:\MyProject\ZIP\Files\" s3://my-bucket/ssis/ProjectFolder/FileDrop/ --recursive --profile MyAwsCliProfile
# List the files into a text file
aws s3 ls s3://my-bucket/ssis/ProjectFolder/FileDrop/ --recursive > D:\MyProject\ZIP\WeeklyList.csv --profile MyAwsCliProfile
# Copy List to S3 bucket on AWS
aws s3 cp "D:\MyProject\ZIP\WeeklyList.csv" s3://my-bucket/ssis/ProjectFolder/FileDrop/ --profile MyAwsCliProfile Then in SSMS I ran these commands to transfer the files from my S3 bucket to the RDS Server.
EXEC msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::my-bucket/ssis/MyDataDrop/FileDrop/MyFile1.csv', @rds_file_path='D:\S3\MyProject\MyFile1.csv, @overwrite_file=1;
EXEC msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::my-bucket/ssis/MyDataDrop/FileDrop/MyFile2.csv', @rds_file_path='D:\S3\MyProject\MyFile2.csv, @overwrite_file=1;
.... etc ... for other files.Next I staged the files in staging tables with the suffix _INIT using the BULK INSERT command.
BULK INSERT [dbo].[npidata_pfile_initial_stage]
FROM 'D:\S3\NPI\npidata_pfile_20050523-20260308.csv'
WITH (
FORMAT = 'CSV', -- MUST USE THIS in conjunction with FIELQUOTE
FIRSTROW = 2, -- Skips the header row if present
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a',
FIELDQUOTE = '"',
KEEPNULLS
);
select count(*) FROM [dbo].[npidata_pfile_initial_stage]
BULK INSERT [dbo].[endpoint_pfile_initial_stage]
FROM 'D:\S3\NPI\endpoint_pfile_20050523-20260308.csv'
WITH (
FORMAT = 'CSV', -- MUST USE THIS in conjunction with FIELQUOTE
FIRSTROW = 2, -- Skips the header row if present
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a',
FIELDQUOTE = '"',
KEEPNULLS
);
select count(*) FROM [dbo].[endpoint_pfile_initial_stage]
--- etc ----Then I extracted the flat records I wanted to keep.
INSERT INTO [dbo].[npidata_pfile_step2_INIT_stage]
([NPI], [Entity_Type_Code], [Replacement_NPI], [Employer_Identification_Number_EIN], [Provider_Organization_Name_Legal_Business_Name], [Provider_Last_Name_Legal_Name], [Provider_First_Name], [Provider_Middle_Name], [Provider_Name_Prefix_Text], [Provider_Name_Suffix_Text], [Provider_Credential_Text], [Provider_Other_Organization_Name], [Provider_Other_Organization_Name_Type_Code], [Provider_Other_Last_Name], [Provider_Other_First_Name], [Provider_Other_Middle_Name], [Provider_Other_Name_Prefix_Text], [Provider_Other_Name_Suffix_Text], [Provider_Other_Credential_Text], [Provider_Other_Last_Name_Type_Code], [Provider_First_Line_Business_Mailing_Address], [Provider_Second_Line_Business_Mailing_Address], [Provider_Business_Mailing_Address_City_Name], [Provider_Business_Mailing_Address_State_Name], [Provider_Business_Mailing_Address_Postal_Code], [Provider_Business_Mailing_Address_Country_Code_If_outside_U_S], [Provider_Business_Mailing_Address_Telephone_Number], [Provider_Business_Mailing_Address_Fax_Number], [Provider_First_Line_Business_Practice_Location_Address], [Provider_Second_Line_Business_Practice_Location_Address], [Provider_Business_Practice_Location_Address_City_Name], [Provider_Business_Practice_Location_Address_State_Name], [Provider_Business_Practice_Location_Address_Postal_Code], [Provider_Business_Practice_Location_Address_Country_Code_If_outside_U_S], [Provider_Business_Practice_Location_Address_Telephone_Number], [Provider_Business_Practice_Location_Address_Fax_Number], [Provider_Enumeration_Date], [Last_Update_Date], [NPI_Deactivation_Reason_Code], [NPI_Deactivation_Date], [NPI_Reactivation_Date], [Provider_Sex_Code], [Authorized_Official_Last_Name], [Authorized_Official_First_Name], [Authorized_Official_Middle_Name], [Authorized_Official_Title_or_Position], [Authorized_Official_Telephone_Number], [Is_Sole_Proprietor], [Is_Organization_Subpart], [Parent_Organization_LBN], [Parent_Organization_TIN], [Authorized_Official_Name_Prefix_Text], [Authorized_Official_Name_Suffix_Text], [Authorized_Official_Credential_Text], [Certification_Date],
[isActive], [updatedAt]
)
SELECT
CONVERT(bigint,NULLIF([NPI],'')),
CONVERT(int,NULLIF([Entity_Type_Code],'')),
CONVERT(bigint, NULLIF([Replacement_NPI],'')),
[Employer_Identification_Number_EIN], [Provider_Organization_Name_Legal_Business_Name], [Provider_Last_Name_Legal_Name], [Provider_First_Name], [Provider_Middle_Name], [Provider_Name_Prefix_Text], [Provider_Name_Suffix_Text], [Provider_Credential_Text], [Provider_Other_Organization_Name], [Provider_Other_Organization_Name_Type_Code], [Provider_Other_Last_Name], [Provider_Other_First_Name], [Provider_Other_Middle_Name], [Provider_Other_Name_Prefix_Text], [Provider_Other_Name_Suffix_Text], [Provider_Other_Credential_Text],
CONVERT(int, NULLIF([Provider_Other_Last_Name_Type_Code],'')),
[Provider_First_Line_Business_Mailing_Address], [Provider_Second_Line_Business_Mailing_Address], [Provider_Business_Mailing_Address_City_Name], [Provider_Business_Mailing_Address_State_Name], [Provider_Business_Mailing_Address_Postal_Code], [Provider_Business_Mailing_Address_Country_Code_If_outside_U_S], [Provider_Business_Mailing_Address_Telephone_Number], [Provider_Business_Mailing_Address_Fax_Number], [Provider_First_Line_Business_Practice_Location_Address], [Provider_Second_Line_Business_Practice_Location_Address], [Provider_Business_Practice_Location_Address_City_Name], [Provider_Business_Practice_Location_Address_State_Name], [Provider_Business_Practice_Location_Address_Postal_Code], [Provider_Business_Practice_Location_Address_Country_Code_If_outside_U_S], [Provider_Business_Practice_Location_Address_Telephone_Number], [Provider_Business_Practice_Location_Address_Fax_Number],
CONVERT(date,NULLIF([Provider_Enumeration_Date],'')),
CONVERT(date,NULLIF([Last_Update_Date],'')),
[NPI_Deactivation_Reason_Code],
CONVERT(date,NULLIF([NPI_Deactivation_Date],'')),
CONVERT(date,NULLIF([NPI_Reactivation_Date],'')),
[Provider_Sex_Code], [Authorized_Official_Last_Name], [Authorized_Official_First_Name], [Authorized_Official_Middle_Name], [Authorized_Official_Title_or_Position], [Authorized_Official_Telephone_Number], [Is_Sole_Proprietor], [Is_Organization_Subpart], [Parent_Organization_LBN], [Parent_Organization_TIN], [Authorized_Official_Name_Prefix_Text], [Authorized_Official_Name_Suffix_Text], [Authorized_Official_Credential_Text], [Certification_Date],
1, GETUTCDATE()
FROM [dbo].[npidata_pfile_initial_stage] -- [dbo].[npidata_pfile_week_stage] or [dbo].[npidata_pfile_initial_stage]
WHERE NPI is not null;
Then I split out the License Numbers into a relational table.
DECLARE @ordinalId VARCHAR(8) = 15;
DECLARE @tSql varchar(2000);
WHILE @ordinalId > 0
BEGIN
SELECT @tSql = CONCAT('INSERT INTO [dbo].[npidata_pfile_license_number_stage2_INIT]
( [NPI], [OrdinalId], [Provider_License_Number], [Provider_License_Number_State_Code],
[Healthcare_Provider_Taxonomy_Code], [Healthcare_Provider_Primary_Taxonomy_Switch],
[Healthcare_Provider_Taxonomy_Group],
[isActive], [updatedAt])
SELECT [NPI], ',@ordinalId,',
[Provider_License_Number_',@ordinalId,'],
[Provider_License_Number_State_Code_',@ordinalId,'],
[Healthcare_Provider_Taxonomy_Code_',@ordinalId,'],
[Healthcare_Provider_Primary_Taxonomy_Switch_',@ordinalId,'],
[Healthcare_Provider_Taxonomy_Group_',@ordinalId,'],
1, getutcdate()
FROM [dbo].[npidata_pfile_initial_stage]
WHERE LEN([Provider_License_Number_',@ordinalId,']) > 0 OR
LEN([Provider_License_Number_State_Code_',@ordinalId,']) > 0 OR
LEN([Healthcare_Provider_Taxonomy_Code_',@ordinalId,']) > 0 OR
LEN([Healthcare_Provider_Primary_Taxonomy_Switch_',@ordinalId,']) > 0 OR
LEN([Healthcare_Provider_Taxonomy_Group_',@ordinalId,']) > 0
;')
EXEC(@tSql);
SELECT @ordinalId = @ordinalId - 1;
END;
Repeat the above process for Other Identifiers.
DECLARE @ordinalId VARCHAR(8) = 50;
DECLARE @tSql varchar(2000);
WHILE @ordinalId > 0
BEGIN
SELECT @tSql = CONCAT('INSERT INTO [dbo].[npidata_pfile_other_identifier_stage2_INIT]
( [NPI], [OrdinalId],
[Other_Provider_Identifier], [Other_Provider_Identifier_Type_Code],
[Other_Provider_Identifier_State], [Other_Provider_Identifier_Issuer],
[isActive], [updatedAt])
SELECT [NPI], ',@ordinalId,',
[Other_Provider_Identifier_',@ordinalId,'],
[Other_Provider_Identifier_Type_Code_',@ordinalId,'],
[Other_Provider_Identifier_State_',@ordinalId,'],
[Other_Provider_Identifier_Issuer_',@ordinalId,'],
1, getutcdate()
FROM [dbo].[npidata_pfile_initial_stage]
WHERE LEN([Other_Provider_Identifier_',@ordinalId,']) > 0 OR
LEN([Other_Provider_Identifier_Type_Code_',@ordinalId,']) > 0 OR
LEN([Other_Provider_Identifier_State_',@ordinalId,']) > 0 OR
LEN([Other_Provider_Identifier_Issuer_',@ordinalId,']) > 0
;')
EXEC(@tSql);
SELECT @ordinalId = @ordinalId - 1;
END;
And after some quick verification and counts, I’m ready to transport the staged data to my Production database.
-- START LOG HERE
DECLARE @ParentAuditKey INT = 1;
INSERT INTO DimAudit ([ParentAuditKey], [TableName], [PkgName], [PkgVersionMajor], [ExecStartDT], [ExecStopDT], [InsertRowCnt], [SuccessfulProcessingInd])
VALUES(@ParentAuditKey, '[npidata_DeleteAllDataRePopulateProd]','SPROC: [npidata_DeleteAllDataRePopulateProd]', 1, getutcdate(), null, 0, 'N');
SELECT @ParentAuditKey = SCOPE_IDENTITY();
-- As a safety this makes sure we have at least a million rows staged to go.
IF (SELECT COUNT(*) FROM [dbo].[npidata_pfile_step2_INIT_stage]) < 1000000
BEGIN
SELECT 'YOU MISSED SOMETHING - MISSION ABORTED.'
UPDATE dbo.DimAudit
SET ExecStopDT = getutcdate(), [SuccessfulProcessingInd] = 'N'
WHERE AuditKey = @ParentAuditKey
-- END LOG HERE as ABORTED
END
ELSE
BEGIN
--------- MAIN NPI DATA MOVED
SET IDENTITY_INSERT [AdmedProvider].[dbo].[providers] ON;
INSERT INTO [AdmedProvider].[dbo].[providers]
([NPI], [Entity_Type_Code], [Replacement_NPI], [Employer_Identification_Number_EIN], [Provider_Organization_Name_Legal_Business_Name], [Provider_Last_Name_Legal_Name], [Provider_First_Name], [Provider_Middle_Name], [Provider_Name_Prefix_Text], [Provider_Name_Suffix_Text], [Provider_Credential_Text], [Provider_Other_Organization_Name], [Provider_Other_Organization_Name_Type_Code], [Provider_Other_Last_Name], [Provider_Other_First_Name], [Provider_Other_Middle_Name], [Provider_Other_Name_Prefix_Text], [Provider_Other_Name_Suffix_Text], [Provider_Other_Credential_Text], [Provider_Other_Last_Name_Type_Code], [Provider_First_Line_Business_Mailing_Address], [Provider_Second_Line_Business_Mailing_Address], [Provider_Business_Mailing_Address_City_Name], [Provider_Business_Mailing_Address_State_Name], [Provider_Business_Mailing_Address_Postal_Code], [Provider_Business_Mailing_Address_Country_Code_If_outside_U_S], [Provider_Business_Mailing_Address_Telephone_Number], [Provider_Business_Mailing_Address_Fax_Number], [Provider_First_Line_Business_Practice_Location_Address], [Provider_Second_Line_Business_Practice_Location_Address], [Provider_Business_Practice_Location_Address_City_Name], [Provider_Business_Practice_Location_Address_State_Name], [Provider_Business_Practice_Location_Address_Postal_Code], [Provider_Business_Practice_Location_Address_Country_Code_If_outside_U_S], [Provider_Business_Practice_Location_Address_Telephone_Number], [Provider_Business_Practice_Location_Address_Fax_Number], [Provider_Enumeration_Date], [Last_Update_Date], [NPI_Deactivation_Reason_Code], [NPI_Deactivation_Date], [NPI_Reactivation_Date], [Provider_Sex_Code], [Authorized_Official_Last_Name], [Authorized_Official_First_Name], [Authorized_Official_Middle_Name], [Authorized_Official_Title_or_Position], [Authorized_Official_Telephone_Number], [Is_Sole_Proprietor], [Is_Organization_Subpart], [Parent_Organization_LBN], [Parent_Organization_TIN], [Authorized_Official_Name_Prefix_Text], [Authorized_Official_Name_Suffix_Text], [Authorized_Official_Credential_Text], [Certification_Date], [Healthcare_Provider_Taxonomy_Code_Step_Completed], [Other_Provider_Identifier_Step_Completed], [Healthcare_Provider_Taxonomy_Group_Step_Completed], [isActive], [updatedAt], [IsUpdate])
SELECT
[NPI], [Entity_Type_Code], [Replacement_NPI], [Employer_Identification_Number_EIN], [Provider_Organization_Name_Legal_Business_Name], [Provider_Last_Name_Legal_Name], [Provider_First_Name], [Provider_Middle_Name], [Provider_Name_Prefix_Text], [Provider_Name_Suffix_Text], [Provider_Credential_Text], [Provider_Other_Organization_Name], [Provider_Other_Organization_Name_Type_Code], [Provider_Other_Last_Name], [Provider_Other_First_Name], [Provider_Other_Middle_Name], [Provider_Other_Name_Prefix_Text], [Provider_Other_Name_Suffix_Text], [Provider_Other_Credential_Text], [Provider_Other_Last_Name_Type_Code], [Provider_First_Line_Business_Mailing_Address], [Provider_Second_Line_Business_Mailing_Address], [Provider_Business_Mailing_Address_City_Name], [Provider_Business_Mailing_Address_State_Name], [Provider_Business_Mailing_Address_Postal_Code], [Provider_Business_Mailing_Address_Country_Code_If_outside_U_S], [Provider_Business_Mailing_Address_Telephone_Number], [Provider_Business_Mailing_Address_Fax_Number], [Provider_First_Line_Business_Practice_Location_Address], [Provider_Second_Line_Business_Practice_Location_Address], [Provider_Business_Practice_Location_Address_City_Name], [Provider_Business_Practice_Location_Address_State_Name], [Provider_Business_Practice_Location_Address_Postal_Code], [Provider_Business_Practice_Location_Address_Country_Code_If_outside_U_S], [Provider_Business_Practice_Location_Address_Telephone_Number], [Provider_Business_Practice_Location_Address_Fax_Number], [Provider_Enumeration_Date], [Last_Update_Date], [NPI_Deactivation_Reason_Code], [NPI_Deactivation_Date], [NPI_Reactivation_Date], [Provider_Sex_Code], [Authorized_Official_Last_Name], [Authorized_Official_First_Name], [Authorized_Official_Middle_Name], [Authorized_Official_Title_or_Position], [Authorized_Official_Telephone_Number], [Is_Sole_Proprietor], [Is_Organization_Subpart], [Parent_Organization_LBN], [Parent_Organization_TIN], [Authorized_Official_Name_Prefix_Text], [Authorized_Official_Name_Suffix_Text], [Authorized_Official_Credential_Text], [Certification_Date], [Healthcare_Provider_Taxonomy_Code_Step_Completed], [Other_Provider_Identifier_Step_Completed], [Healthcare_Provider_Taxonomy_Group_Step_Completed], [isActive], [updatedAt], [IsUpdate]
FROM [dbo].[npidata_pfile_step2_INIT_stage];
SET IDENTITY_INSERT [AdmedProvider].[dbo].[providers] OFF;
INSERT INTO DimAudit ([ParentAuditKey], [TableName], [PkgName], [PkgVersionMajor], [ExecStartDT], [ExecStopDT], [InsertRowCnt], [SuccessfulProcessingInd])
VALUES(@ParentAuditKey, '[npidata_pfile]','Data moved', 1, getutcdate(), getutcdate(), @@ROWCOUNT, 'Y');
--SELECT COUNT (*) FROM [AdmedProvider].[dbo].[providers] AS npidata_row_count;
---- CHILD BREAK OUT TABLES
INSERT INTO [AdmedProvider].[dbo].[provider_licenses]
([NPI], [OrdinalId], [Provider_License_Number], [Provider_License_Number_State_Code], [Healthcare_Provider_Taxonomy_Code], [Healthcare_Provider_Primary_Taxonomy_Switch], [Healthcare_Provider_Taxonomy_Group], [isActive], [updatedAt] )
SELECT
[NPI], [OrdinalId], [Provider_License_Number], [Provider_License_Number_State_Code], [Healthcare_Provider_Taxonomy_Code], [Healthcare_Provider_Primary_Taxonomy_Switch], [Healthcare_Provider_Taxonomy_Group], [isActive], [updatedAt]
FROM [dbo].[npidata_pfile_license_number_stage2_INIT];
INSERT INTO DimAudit ([ParentAuditKey], [TableName], [PkgName], [PkgVersionMajor], [ExecStartDT], [ExecStopDT], [InsertRowCnt], [SuccessfulProcessingInd])
VALUES(@ParentAuditKey, '[npidata_pfile_license_number]','Data moved', 1, getutcdate(), getutcdate(), @@ROWCOUNT, 'Y');
--SELECT COUNT (*) FROM [AdmedProvider].[dbo].[provider_licenses];
INSERT INTO [AdmedProvider].[dbo].[provider_other_identifiers]
( [NPI], [OrdinalId], [Other_Provider_Identifier], [Other_Provider_Identifier_Type_Code], [Other_Provider_Identifier_State], [Other_Provider_Identifier_Issuer], [isActive], [updatedAt] )
SELECT
[NPI], [OrdinalId], [Other_Provider_Identifier], [Other_Provider_Identifier_Type_Code], [Other_Provider_Identifier_State], [Other_Provider_Identifier_Issuer], [isActive], [updatedAt]
FROM [dbo].[npidata_pfile_other_identifier_stage2_INIT];
INSERT INTO DimAudit ([ParentAuditKey], [TableName], [PkgName], [PkgVersionMajor], [ExecStartDT], [ExecStopDT], [InsertRowCnt], [SuccessfulProcessingInd])
VALUES(@ParentAuditKey, '[npidata_pfile_other_identifier]','Data moved', 1, getutcdate(), getutcdate(), @@ROWCOUNT, 'Y');
--SELECT COUNT (*) FROM [AdmedProvider].[dbo].[provider_other_identifiers];
---- OTHER TABLES
INSERT INTO [AdmedProvider].[dbo].[provider_endpoints]
( [NPI], [Endpoint_Type], [Endpoint_Type_Description], [Endpoint], [Affiliation], [Endpoint_Description], [Affiliation_Legal_Business_Name], [Use_Code], [Use_Description], [Other_Use_Description], [Content_Type], [Content_Description], [Other_Content_Description], [Affiliation_Address_Line_One], [Affiliation_Address_Line_Two], [Affiliation_Address_City], [Affiliation_Address_State], [Affiliation_Address_Country], [Affiliation_Address_Postal_Code] )
SELECT
[NPI], [Endpoint_Type], [Endpoint_Type_Description], [Endpoint], [Affiliation], [Endpoint_Description], [Affiliation_Legal_Business_Name], [Use_Code], [Use_Description], [Other_Use_Description], [Content_Type], [Content_Description], [Other_Content_Description], [Affiliation_Address_Line_One], [Affiliation_Address_Line_Two], [Affiliation_Address_City], [Affiliation_Address_State], [Affiliation_Address_Country], [Affiliation_Address_Postal_Code]
FROM [dbo].[endpoint_pfile_initial_stage];
INSERT INTO DimAudit ([ParentAuditKey], [TableName], [PkgName], [PkgVersionMajor], [ExecStartDT], [ExecStopDT], [InsertRowCnt], [SuccessfulProcessingInd])
VALUES(@ParentAuditKey, '[endpoint_pfile]','Data moved', 1, getutcdate(), getutcdate(), @@ROWCOUNT, 'Y');
--SELECT COUNT (*) FROM [AdmedProvider].[dbo].[provider_endpoints];
INSERT INTO [AdmedProvider].[dbo].[provider_other_names]
( [NPI], [Provider_Other_Organization_Name], [Provider_Other_Organization_Name_Type_Code] )
SELECT
[NPI], [Provider_Other_Organization_Name], [Provider_Other_Organization_Name_Type_Code]
FROM [dbo].[othername_pfile_initial_stage];
INSERT INTO DimAudit ([ParentAuditKey], [TableName], [PkgName], [PkgVersionMajor], [ExecStartDT], [ExecStopDT], [InsertRowCnt], [SuccessfulProcessingInd])
VALUES(@ParentAuditKey, '[othername_pfile]','Data moved', 1, getutcdate(), getutcdate(), @@ROWCOUNT, 'Y');
--SELECT COUNT (*) FROM [AdmedProvider].[dbo].[provider_other_names];
INSERT INTO [AdmedProvider].[dbo].[provider_locations]
( [NPI], [Provider_Secondary_Practice_Location_Address_Address_Line_1], [Provider_Secondary_Practice_Location_Address_Address_Line_2], [Provider_Secondary_Practice_Location_Address_City_Name], [Provider_Secondary_Practice_Location_Address_State_Name], [Provider_Secondary_Practice_Location_Address_Postal_Code], [Provider_Secondary_Practice_Location_Address_Country_Code_If_outside_U_S], [Provider_Secondary_Practice_Location_Address_Telephone_Number], [Provider_Secondary_Practice_Location_Address_Telephone_Extension], [Provider_Practice_Location_Address_Fax_Number] )
SELECT
[NPI], [Provider_Secondary_Practice_Location_Address_Address_Line_1], [Provider_Secondary_Practice_Location_Address_Address_Line_2], [Provider_Secondary_Practice_Location_Address_City_Name], [Provider_Secondary_Practice_Location_Address_State_Name], [Provider_Secondary_Practice_Location_Address_Postal_Code], [Provider_Secondary_Practice_Location_Address_Country_Code_If_outside_U_S], [Provider_Secondary_Practice_Location_Address_Telephone_Number], [Provider_Secondary_Practice_Location_Address_Telephone_Extension], [Provider_Practice_Location_Address_Fax_Number]
FROM [dbo].[pl_pfile_initial_stage];
INSERT INTO DimAudit ([ParentAuditKey], [TableName], [PkgName], [PkgVersionMajor], [ExecStartDT], [ExecStopDT], [InsertRowCnt], [SuccessfulProcessingInd])
VALUES(@ParentAuditKey, '[pl_pfile]','Data moved', 1, getutcdate(), getutcdate(), @@ROWCOUNT, 'Y');
--SELECT COUNT (*) FROM [AdmedProvider].[dbo].[provider_locations];
INSERT INTO DimAudit ([ParentAuditKey], [TableName], [PkgName], [PkgVersionMajor], [ExecStartDT], [ExecStopDT], [InsertRowCnt], [SuccessfulProcessingInd])
VALUES(@ParentAuditKey, '[npidata_pfile]','Deactivation List Applied', 1, getutcdate(), getutcdate(), @@ROWCOUNT, 'Y');
-- END LOG HERE as SUCCESS
UPDATE dbo.DimAudit
SET ExecStopDT = getutcdate(), [SuccessfulProcessingInd] = 'Y'
WHERE AuditKey = @ParentAuditKey
END
And that ends moving the data for STEP ONE without having to create an SSIS package.