DECLARE @SQL VARCHAR(100)
DECLARE curSQL CURSOR FOR
SELECT 'EXEC sp_change_users_login UPDATE_ONE, ' + name + ', ' + name
FROM sysusers
WHERE issqluser = 1 AND name NOT IN ('INFORMATION_SCHEMA', 'sys', 'guest', 'dbo')
OPEN curSQL
FETCH curSQL INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
print @SQL
EXEC (@SQL)
FETCH curSQL INTO @SQL
END
CLOSE curSQL
DEALLOCATE curSQL
Here are the pre-migration and post-migration steps:
Premigration Steps:
/* 1) run DBCC consistency checker */
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS
/* 1a) Normally we KILL as user connections for an outage but since the application servers were shutdown we didnt need to run SQL script to kill user connections */
/* 2) check for all the logins having access to FEWS database so that there are no permission issues after migration */
/* 3) Backup Database and make sure to click on "verify backup when finished" in options tab */
/* 4) After the backup finishes, copy to destination, restore from the destination */
/* 5) Modified the auto-growth increments of the database for all the secondary files to avoid the DB growing in baby steps and avoid performance issues */
Postmigration Steps:
-- check for orphaned users
exec sp_change_users_login 'report'
-- Some logins are orphaned, so we need to migrate the login from source server
select 'create login ' + quotename(sp.name) + ' with password = ' + CONVERT(nvarchar(max), sl.password_hash, 1) + ' HASHED'
from sys.server_principals sp
inner join
sys.sql_logins sl
on sp.principal_id = sl.principal_id
-- attach the Database user with the server login
exec sp_change_users_login 'UPDATE_ONE', 'sqllogin01', 'sqllogin01'
-- verify that the SQL login sqllogin01logins to the server without access issue.
-- check the port - since NOT listening to dynamic ports it is 1433
select local_tcp_port from sys.dm_exec_connections
where session_id = @@SPID
-- run DBCC on the destination for final confirmation
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS
I was executing an SSIS package and processing the data from the flat files downloaded from DoT. I had a set of a set of files to be downloaded from DoT and then processed into a table.
The process itself is simple where in we first use HTTP connection manager or C# script task to download the files and then process using MULTIFLATFILE connection manager in SSIS.
However the package failed multiple times. Couple of reasons:
1) Source flat files code page was different then ANSI 1252
Solution : use code page in the multifile connection manager as UTF 8
2) Failed with truncation error on multiple columns
Solution : change the typeguess property in the registry or use suggest more than 200 rows for predicting the datatypes of destination columns. Or as a last resort insert a dummy record more than 256 characters and then delete after ETL processing
3) OLEDB cgonnection manager failed for unicode source file
Solution: use ADO.NET connection manager
4) Truncation when inserting to SQL table using ADO.NET connection manager
The precise error was “[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is:
Received an invalid column length from the bcp client for colid 19.”
Solution : check the column 19 and it was inserting more than allocated for the column. Increasing the column data length fixed the issue.