Import csv into Cmdb

This article explains how to add records to a SQL table. Use this method whenever you need to do bulk updates.

Steps

• Backup the SCOMCmdb.dbo.Configuration table to a csv file
• Add new records to a csv file
• Import the csv file into the SCOMCmdb.dbo.Configuration table.

WARNING: This will delete all existing data in the table. See error below before continuing.

Backup data

This can be done with a wizard in SSMS but it's quicker running a query.

1. Run this query select * from SCOMCmdb.dbo.Configuration order by class, name
2. Right-click anywhere in the results and click Save Results As... and save it as a csv file.

Add new records to csv file

1. To avoid weirdness during the import, add the headers in if they are missing.
2. Open the csv file in Excel and append new records to the end. Add any other info you want to the columns then save it. You don't need headers.

Import data

1. In SSMS, right-click the SCOMCmdb database and select Tasks > Import Data...
2. Select Flat File Source and select the csv file.
3. Things can get weird here. Sometimes after selecting the file, the Code page field can change. In my lab it changed from 1252 (ANSI - Latin I) to 65001 (UTF-8). You need to change it back to 1252 (ANSI - Latin I) or the import fails.
4. Ignore the warning in yellow below; "Columns are not defined for this connection manager" and click Next.
5. You should see example rows in the Row/Column delimiter screen. Click Next.
6. On the Destination screen, select Microsoft OLE DB Driver for SQL Server and make sure you have the correct SQL Server and database selected. Click Next.
7. Click on the row under the Destination column and click the drop-down arrow and select [dbo].[Configuration]. This is the table I want my data to go in.
8. Click Edit Mappings... and select Delete rows in destination table.
9. Below in the Mappings section, double-check the Source and Destination columns match. If they don't, stop and make sure you have headers. When all good click OK.
10. Click on Preview... and confirm it looks good and click Next.
11. Click Next on the Save and Run Package... screen.
12. Click Finish and Close.
13. Confirm the new record has been added and the other records look good.

Errors

I have experienced these import errors:

1. When records had multiple double quotes in the comment field like this """"
2. When the comment field was longer than the column length in SQL.

*** Double check this because the table will get wiped ***