Download secured roll see (part 1) for how to download details
Import secure-roll text file into SQL server table
SBC means San Bernadino County, COV means City of Victorville
secured-roll text file( SB county) to secured-roll table to SBC_POD table to COV_POD table
1.backup previous table SBC_POD, COV_POD, Secured-roll table
1.1 create a new empty table SBC_POD_2023, COV_POD_2023, Secured-roll_2023
1.2 open old table schema
1.3 select all columns and click copy
1.4 go back to new table, must click first row first, then click paste button to paste all column here. If not click first row, directly paste will cause error.
1.5 click save button and type new table name respectively SBC_POD_2023, COV_POD_2023, Secured-roll_2023
1.6 click new query, type sql statement to copy all data from original table SBC_POD, COV_POD, Secured-roll to backup table respectly SBC_POD_2023, COV_POD_2023, Secured-roll_2023
2. empty data only, keep table schema, means keep table columns structure, but clear all data inside. For table SBC_POD, COV_POD,
3. Delete table secrued-roll, since I will re-create it later by import task.
3.1 reset object id for SBC_POD table back to 1
4. import secured-roll text file
4.1 import flat file
4.2 select flat file source and type new table name
4.3 click preview data
4.4 open backup table, secured-roll_2023 table, open design, show all column. Also click Modify Columns, must modify column name exactly same as backup table, such as remove "_" in column name, replace it with space key, allow null for all column, Data type do not need to match to original backtable.
4.5 click finish
4.6 failed eror
4.7 try to match data type as well
4.8 still getting error,
5 open text file in excel
5.1 use excel to open text file, set delimited
5.2 set delimiter as "~"
5.3
5.4 save as excel format
5.5 importa data
5.6 set excel as source,
5.7 choose Excel 2007-2010
5.8 error
6 download and install driver. 64-bit version of the 'Microsoft Access Database Engine 2010 Redistributable' that will allow you to use the 'Microsoft.ACE.OLEDB.12.0' provider is available here:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
Beause my office is 32-bit, so I install 32-bit.
7 select SQL as destination
8. error, missing driver
9. download OLE DB driver
https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver16
10.warning: user "sa" does not authorized to access OLE DB driver, so must change connection type from SQL server authentication to Windows authentication with my windows user name and password
11. test connection until you see success
