04:46
Dynamics Consulting München
Handling Rejected Record Rows from Scribe
So you have a recently installed Scribe Version 7.0.1.15701 on a server. You’ve built some DTS jobs and everything looks to be importing and translating smoothly. THEN – one of your jobs throws up a report like this:
12,555 successful records loaded! 6 record rows failed. “Hmm? I wonder why those records failed?”, you ask yourself. - and so starts your exploration.
Enabling Rejected Rows in Scribe:
Before you can review the rejected rows, you must first tell Scribe that you want it to put the rejected records into a table. This is done by going to Run –> Edit Settings –> Rejected Source Rows (tab) –> Output Rejected Source Rows (checkbox). You can then tell Scribe where you want this “Reject Rows” table. Additionally, you can specify whether Scribe should create a new table for each job that generates “Reject Rows” – or if you want a new table generated. Generally speaking, you don't want a bunch of extra rejected rows tables for the same job, so I would recommend selecting the option to re-use the same table. I’ve defined it this way in the following screen shot from Scribe:
Querying Rejected Rows:
There can be any one of a 1,000 reasons why a particular row in a table fails to import. In my particular situation, the pick-list value that I had on my SELECTCASE statement turned out to be incorrectly numbered, which was throwing out the select 6 rows. But before I made that conclusion definitively, I wanted to see the data. For users of MS SQL Server 2005 or 2008, reviewing these rows is a fairly straight-forward process. Executing a query against a table in MS SQL Server is done through the Query Editor Window. One option is to Right-Click on the table and select Script Table As –>SELECT To –> New Query Editor Window, as depicted in the following:
Once you have the query set up appropriately in the Query Editor Window, simply select “Execute”. The rows of data can then be seen in the results tab below the Query Editor Window.
No Microsoft SQL Server Tools?:
My particular server where Scribe was located did not have MSSQL Server 2005 or 2008 tools installed – so I couldn’t run a simple SQL query against the REJECT_CONTACTMIGRATION table. Frustration set in. I didn’t have a free copy of Microsoft SQL Server 2005/2008 lying around. What can I do? Luckily, there was a solution.
As it turns out, Microsoft has a free product for just such a situation: Microsoft SQL Server Management Studio Express (MSSMSE). From the website (link): (Note, this link is for MSSMSE 2005. The 2008 version can be found here: link)
“Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services.”
Download the file appropriate for your install and MSSMSE will connect to the Scribe Internal DB automatically. You should see the following once you have installed the application and launch it:
In this case, it is connecting directly to the Scribe Internal database. Once connected, you can browse directly to the table you want to review:
Once you find the table you want to query, simply right-click on the table and select “New Query Editor Window”. This will populate the SQL Query Editor window with the appropriate SQL SELECT statement for the table you want. Execute the query and then you can view the rows in the table.
Once I identified the problem, I simply modified the DTS to include a filter for records with my reject record condition; re-ran the DTS; and my 6 lost records were loaded successfully! Victory!
Other Scribe topics can be viewed on Customer Effective’s Scribe Blog, located here.
Lastly, another topic on our forums enables you to create a workflow to surface Scribe errors and monitor them directly into CRM, which can be very helpful. This is located here.
Good luck with your CRM projects!
12,555 successful records loaded! 6 record rows failed. “Hmm? I wonder why those records failed?”, you ask yourself. - and so starts your exploration.
Enabling Rejected Rows in Scribe:
Before you can review the rejected rows, you must first tell Scribe that you want it to put the rejected records into a table. This is done by going to Run –> Edit Settings –> Rejected Source Rows (tab) –> Output Rejected Source Rows (checkbox). You can then tell Scribe where you want this “Reject Rows” table. Additionally, you can specify whether Scribe should create a new table for each job that generates “Reject Rows” – or if you want a new table generated. Generally speaking, you don't want a bunch of extra rejected rows tables for the same job, so I would recommend selecting the option to re-use the same table. I’ve defined it this way in the following screen shot from Scribe:
Querying Rejected Rows:
There can be any one of a 1,000 reasons why a particular row in a table fails to import. In my particular situation, the pick-list value that I had on my SELECTCASE statement turned out to be incorrectly numbered, which was throwing out the select 6 rows. But before I made that conclusion definitively, I wanted to see the data. For users of MS SQL Server 2005 or 2008, reviewing these rows is a fairly straight-forward process. Executing a query against a table in MS SQL Server is done through the Query Editor Window. One option is to Right-Click on the table and select Script Table As –>SELECT To –> New Query Editor Window, as depicted in the following:
Once you have the query set up appropriately in the Query Editor Window, simply select “Execute”. The rows of data can then be seen in the results tab below the Query Editor Window.
No Microsoft SQL Server Tools?:
My particular server where Scribe was located did not have MSSQL Server 2005 or 2008 tools installed – so I couldn’t run a simple SQL query against the REJECT_CONTACTMIGRATION table. Frustration set in. I didn’t have a free copy of Microsoft SQL Server 2005/2008 lying around. What can I do? Luckily, there was a solution.
As it turns out, Microsoft has a free product for just such a situation: Microsoft SQL Server Management Studio Express (MSSMSE). From the website (link): (Note, this link is for MSSMSE 2005. The 2008 version can be found here: link)
“Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services.”
Download the file appropriate for your install and MSSMSE will connect to the Scribe Internal DB automatically. You should see the following once you have installed the application and launch it:
In this case, it is connecting directly to the Scribe Internal database. Once connected, you can browse directly to the table you want to review:
Once you find the table you want to query, simply right-click on the table and select “New Query Editor Window”. This will populate the SQL Query Editor window with the appropriate SQL SELECT statement for the table you want. Execute the query and then you can view the rows in the table.
Once I identified the problem, I simply modified the DTS to include a filter for records with my reject record condition; re-ran the DTS; and my 6 lost records were loaded successfully! Victory!
Other Scribe topics can be viewed on Customer Effective’s Scribe Blog, located here.
Lastly, another topic on our forums enables you to create a workflow to surface Scribe errors and monitor them directly into CRM, which can be very helpful. This is located here.
Good luck with your CRM projects!
Posted in: CRM 2011 and Scribe
