How to create the file needed by PowerShell for the daily mustdump update

1. This tutorial assumes you have SQL Server Management Studio installed on your computer. Launch SQL Server Management Studio. Contact Jeremy Canfield if you need the server, user name and password. Connect to SQL Server.
2. Expand Databases, right-click SBRC, select tasks -> Import. A pop-up box will appear.
3. Change Data Source to Flat File source
4. In the File Name field, enter S:\\SBRC\\SQL\\automationjobs\\mustdump.txt
5. Change data source from Delimited to Fixed width
6. On the left, select Columns. Tip: maximize this screen. Drag the red dot to the end of what makes a row, or key in the row width. For the MUST dump, the row width is 258.
7. Still in Columns, just to the left of each row header, left-click to add a black dot separator. For example, left-click at width 83 to add a separator.
   • 83 USER_ID
   • 81 USER_EMAIL
   • 164 USER_ROLE
   • 180 BPN_ID
   • 191 CAN_ID
   • 203 MATERIAL_ID
   • 228 STATUS
   • 239 SUB_ORDER
8. On the left, select Advanced, and click the USER_ID cell. Delete the two spaces preceding text USER_ID, and add two spaces after the text USER_ID
9. Preview should verify the structure is correct
10. Select Next.
11. Change Destination to SQL Server Native Client 11.0. Also, ensure the correct database is selected in the Database drop-down. Select Next.
12. Bullet SQL Server user name and password, and enter the user name and password, and click Next. Contact jeremy.canfield@pb.com if you need the user name and password.
13. click the Edit Mappings button and bullet delete rows in destination table. This clears all of the data from the previous upload. Click Ok.
14. Remove the bullet from run immediately
15. Bullet save SSIS package
16. Bullet File system
17. click the Next button
18. Give the package a name, such as mustdump_core_ftp_update
19. Ensure the File name has path S:\\SBRC\\SQL\\automationjobs\\mustdump_core_ftp_update.dtsx.
20. Click Finish.

How to create the file needed by PowerShell for the daily mustdump updates from Ritas database

1. This tutorial assumes you have SQL Server Management Studio installed on your computer. Launch SQL Server Management Studio. Contact Jeremy Canfield if you need the server, user name and password. Connect to SQL Server.
2. Expand Databases, right-click SBRC, select tasks -> Import. A pop-up box will appear.
3. Change Data Source to Microsoft Excel
4. In the Excel file path field, enter S:\\SBRC\\SQL\\automationjobs\\mustdump1.xlsx and click the Next button
5. Change Destination to SQL Server Native Client 11.0. Also, ensure the correct database is selected in the Database drop-down. Select Next.
6. Bullet SQL Server user name and password, and enter the user name and password, and click Next.
7. Bullet Copy data from one or more tables or views and click the Next button
8. Under Destination, remove the 1$ from [dbo].[mustdump1$], so that it becomes [dbo].[mustdump]
9. click the Edit Mappings button and match the Source and Destination options. Also bullet append rows in destination table. Click Ok.
10. Remove the bullet from run immediately
11. Bullet save SSIS package
12. Bullet File system
13. Select Encrypt sensitive data with user key
14. click the Next button
15. Give the package a name, such as rita_db_ftp_mustdump1_update
16. Ensure the File name has path S:\\SBRC\\SQL\\automationjobs\ita_db_ftp_mustdump1_update.dtsx.
17. Click Finish.

How to create the file needed by PowerShell for the daily mustdump updates from Amys Failed Files

1. This tutorial assumes you have SQL Server Management Studio installed on your computer. Launch SQL Server Management Studio. Contact Jeremy Canfield if you need the server, user name and password. Connect to SQL Server.
2. Expand Databases, right-click SBRC, select tasks -> Import. A pop-up box will appear.
3. Change Data Source to Microsoft Excel
4. In the Excel file path field, enter S:\\SBRC\\SQL\\automationjobs\\failedfile.xlsx and click the Next button
5. Change Destination to SQL Server Native Client 11.0. Also, ensure the correct database is selected in the Database drop-down. Select Next.
6. Bullet SQL Server user name and password, and enter the user name and password, and click Next.
7. Bullet Copy data from one or more tables or views and click the Next button
8. Under Destination, remove the 1$ from [dbo].[mustdump1$], so that it becomes [dbo].[mustdump]
9. click the Edit Mappings button and match the Source and Destination options. Also bullet append rows in destination table. Click Ok.
10. Remove the bullet from run immediately
11. Bullet save SSIS package
12. Bullet File system
13. Select Encrypt sensitive data with user key
14. click the Next button
15. Give the package a name, such as amy_failed_file
16. Ensure the File name has path S:\\SBRC\\SQL\\automationjobs\\amy_failed_file.dtsx.
17. Click Finish.

Getting the daily .TXT and .XLSX files

1. Go to the FTP where the pbSmartPostage dump files are stored: ftp://ftpworld.ct.pb.com/pbSmartPostage/MUST_dump/. Right-click the latest TXT file, and select Save As and save the TXT file to S:\\SBRC\\SQL\\automationjobs\\ as dumpfile.txt.
2. Sign into adminer at http://isccresources.pb.com/sbrc/sql/adminer-4.2.1.php.
   • System = MS SQL
   • Username = contact jeremy.canfield@pb.com for the user name
   • Password = contact jeremy.canfield@pb.com for the password
   • Database = SBRC
   In Adminer, click on the link select just to the left of the dbo.mustdump1 table. Scroll to the bottom of the page and check-mark Whole Results. Change the drop-down to Save and CSV; and click on the Export button. This will download a file named mustdump1.csv to your Downloads folder in Windows. In the Downloads folder in Windows, open the CSV file, copy the data, and replace the data in the file at S:\\SBRC\\SQL\\automationjobs\\ as mustdump1.xlsx
    
3. At S:\\SBRC\\SQL\\automationjobs will be a CSV file with Failed File in the title. Open the CSV file, and copy the CAN to failedfile.xlsx at S:\\SBRC\\SQL\\automationjobs\\ as failedfile.xlsx.

Right-click the latest .TXT and .XSLX files and ensure the last-modified date is today.