Month: September 2014

SSIS: Troubleshooting Error -1071607685

Ever have to work with the obscure SSIS error numbers? They can be a pain to understand. Luckily someone wrote a blog post that details how to look up and figure out what the error numbers actually mean – check it out at the link below if this is new to you:

http://blogs.msdn.com/b/helloworld/archive/2008/07/25/how-to-decipher-understand-ssis-error-code.aspx

After running an SSIS package for a migration of a single table from DB2 to SQL Server, I had an error file with 9984 rows that were not able to be migrated. Upon looking at the error file, it looked like every row had failed with an error of -1071607685.

Capture_errors

So following the instructions of the msdn blog post, I decided to look up the error message. I converted the error number to it’s Hex value (FFFFFFFFC020907B), and looked up this value in the dtsmsg.h file.

Capture

Hmmm… “DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE… No status is available”. Well that’s not very helpful!

To make matters even more confusing, when I manually tried to insert one of the failed rows, it successfully inserted with no errors. What the heck?! Time to figure this out.

Looking at the destination table structure, I saw there was a unique index on one of the columns. I decided to remove the unique index and give the load another try.  BOOM! All rows were inserted into the table with no errors.

So there were 9984 different rows that violated the unique constraint on the table?

  • The table structure on the source and destination were identical, so this didn’t sound right.
  • Plus, earlier I was able to manually insert one of the rows into the table, so I knew for a fact that ALL of these rows did not violate the unique constraint.

At this point, I was stumped. I couldn’t figure out what the problem seemed to be. SSIS gave me a vague error message and was logging rows to the error file that shouldn’t actually fail to insert.

Then, a co-worker reminded me about SSIS batch size and the fast load option. Suddenly it started to make sense.

I went back to the SSIS package and saw that DefaultBufferMaxRows was set to 10,000. So, SSIS was loading rows in batches of 10,000. This could explain why there were 9,984 rows in my error file. I changed DefaultBufferMaxRows to 1 on the Data Flow task.

Capture_buffer

Next, I changed the Data access mode on the OleDBDesination_SQL from “OpenRowset Using FastLoad” to “OpenRowset”.

Capture_rowset

Now SSIS should load the table one row at a time. I dropped and recreated the table (with the unique constraint enabled) to get ready for a fresh load. I started the SSIS package again. BOOM! Only ONE row was logged to the error file now. Hopefully this time it would give me something better than “No Status is Available”.

This time the error was -1071607683 (hex value FFFFFFFFC020907D) which translates to “DTS_E_OLEDBDESTINATIONADAPTERSTATIC_INTEGRITYVIOLATION… The data value violates integrity constraints.” MUCH more manageable now!

Capture_integrity

It turns out, the source DB2 database was Case Sensitive and the destination SQL Server database was Case Insensitive. So the duplicate value wasn’t actually a duplicate on the DB2 database, but WAS a duplicate on the SQL Server side.

I decided to go back and look at the original error file that had 9984 “error rows”. I found the one row that violated the unique constraint and it had the same -1071607685 error as every other row. There would have been no way to find the one bad row from the original error file.

When using large row batch sizes and the FastLoad option in SSIS, be aware of potentially having trouble with the error files. One bad row in a batch of 10,000 caused the entire batch to error out for me, and it took a little bit of work to uncover the actual problem.

Advertisements

Using Powershell to loop through a list of SQL Server Databases

I use Powershell quite a bit to automate routine tasks with SQL Server – mainly because Powershell makes it so simple to perform the same task on a given list of servers. With Powershell, we can execute a .sql script on many servers and save the output for later use. For example, say you need to script out and save database security for all production databases. We can do this with Powershell easily, and I’ll show you how.

Here is the sql script I will be using to script out database level security.

First of all, there needs to be an inventory or list of the databases that you will be querying. Here is an example of a SQL_DATABASES inventory table, and the query we will be using to get our list of databases. We will need both the server and database names.

CREATE TABLE [dbo].[SQL_DATABASES](
	[INSTANCE] [varchar](50) NOT NULL,
	[DATABASENAME] [varchar](100) NOT NULL,
	[REGION] [varchar](4) NOT NULL)
--------
SELECT INSTANCE
 ,DATABASENAME
FROM dbo.SQL_DATABASES
WHERE REGION = 'PROD' --or 'DEV' / 'TEST'

We will be using invoke-sqlcmd to make the connection to the inventory table. You can hit up that link to see the differences between sqlcmd and invoke-sqlcmd. Basically, we need to specify our -ServerInstance, -Database and -Query we will use to get the list of our production databases. Then, each Instance and Database pair will be put into the $databases variable.

# $databases grabs list of production databases 
# from the SQL_DATABASES table on your Database
# ----------------------------------------------
# NOTE* It is always best to test any process on a single dev or test server
# before trying it on ALL production databases!

$databases = invoke-sqlcmd -ServerInstance Server -Database Database `
-Query "SELECT INSTANCE ,DATABASENAME FROM Database.dbo.SQL_DATABASES WHERE REGION = 'PROD'"


Now that we’ve placed the list of Servers and databases into $databases, we need a way to loop through each database in that list. This is where the foreach loop comes in handy.

foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate
# through each pair of server / database.
$Inst = $database.INSTANCE #instance from the select query
$DBname = $database.DATABASENAME #databasename from the select query
#...

Next we will generate the file name for the output of the query for each database. The goal is to have a separate .sql file for each database with a file name format of “Instance_DatabaseName_security.sql”. I am using C:\DBA_SCRIPTS\DB_Permissions\ as the filepath for the output files. I’ve also included the syntax that could be used to do a Replace on named instances to remove the “\” for file names.

#...
#generate the output file name for each server/database pair
$filepath = "C:\DBA_SCRIPTS\DB_Permissions\"
$filename = $Inst +"_"+ $DBname +"_security.sql" 

# This line can be used if there are named instances in your environment.
#$filename = $filename.Replace("\","$") # Replaces all "\" with "$" so that instance name can be used in file names.

$outfile = ($filepath + $filename) #create out-file file name
#...

Now that we have the list of databases and have generated the output file name for each pair, we can execute our script on each database. For scripting database permissions, I use a variation of this script from S. Kusen. This next piece of powershell will connect to each individual server($Inst) and database($DBname) using invoke-sqlcmd, execute our .sql file (SQL_ScriptDBPermissions_V2.sql) to script out database security (using -InputFile), and output the results from each database into a separate .sql file at the $outfile path we created above.

#...
#connect to each instance\database and generate security script and output to files
invoke-sqlcmd -ServerInstance ${Inst} -Database ${DBname} -InputFIle "C:\DBA_SCRIPTS\DB_Permissions\SQL_ScriptDBPermissions_V2.sql" | out-file -filepath ($outfile)

} #end foreach loop

It’s as simple as that, and this is just one example of how powershell can easily be used to accomplish a task on many SQL Servers at once.

Here is the entire powershell script:

# NOTE* It is always best to test any process on a single dev or test server before trying it on ALL production databases!
# ----------------------------------------------
Import-Module SqlPs -DisableNameChecking #may only need this line for SQL 2012 +

# $databases grabs list of production databases from the SQL_DATABASES table on your Database
$databases = invoke-sqlcmd -ServerInstance Server -Database Database -Query "SELECT INSTANCE ,DATABASENAME FROM Database.dbo.SQL_DATABASES WHERE REGION = 'PROD'"


foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate through each pair of server / database.
$Inst = $database.INSTANCE #instance from the select query
$DBname = $database.DATABASENAME #databasename from the select query


#generate the output file name for each server/database pair
$filepath = "C:\DBA_SCRIPTS\DB_Permissions\"
$filename = $Inst +"_"+ $DBname +"_security.sql"
 
# This line can be used if there are named instances in your environment.
#$filename = $filename.Replace("\","$") # Replaces all "\" with "$" so that instance name can be used in file names.
 
$outfile = ($filepath + $filename) #create out-file file name


#connect to each instance\database and generate security script and output to files
invoke-sqlcmd -ServerInstance ${Inst} -Database ${DBname} -InputFIle "C:\DBA_SCRIPTS\DB_Permissions\SQL_ScriptDBPermissions_V2.sql" | out-file -filepath ($outfile)
 
} #end foreach loop