Hi All,

I am working on a project where I need to integrate Microsoft CRM with an of
f-the-shelf product called Syspro, an Accounting application.

The Syspro data is housed in MSSQL (on the same SQL Server as CRM). My goal 
is to move the Customers and Sales related data from Syspro to CRM. Now, you
 can not just insert data into CRM tables so I need to use Biztalk Server 20
02 to accomplish the task.


Thus far I have documented the similar table/columns in each database where 
I want the integration to occur.

Can anybody give me some pointers for how to use Biztalk Server 2002 to inte
grate two SQL Databases?

I am new to Biztalk so any help would be appreciated.

Thanks,
Prasad

[ Post a follow-up to this message ]

Hi Prasad,

If you are new to Biztalk, start with BTS2004.  The prior version is an
entirely different product, and honestly, the newer one is better (cudos to
MS for continuous improvement).

You don't want to document the rows and columns in the database.  That
effort, while useful for testing, isn't useful for integration.

You want to document the places where your accounting system generates a
transaction, and create a "document" heirarchical structure that represents
all of the information about the transaction that is needed by MS CRM to
accept the data into their system.

I haven't used MS CRM, but I would hope that there's a web service or an API
where you can submit a single transaction that contains customer and sales
information, and MS CRM will accept the data, add the customer if they don't
already exist, and add or update the sales data.

If there's not one, you may have to write it.

Then, you install Biztalk.  Hook up a SQL Adapter as input and set it up to
pull in data that represents a new transaction that you want to push to CRM.
(You will do this in real time, one transaction at a time).

The SQL Adapter will give you data in XML format.  Now, translate that
format into a transaction that CRM will accept and post it to CRM.

All this is done in an orchestration within Biztalk.  An orchestration is
basically a graphical "flow-chart-like" language that allows you to describe
the process of handling your transaction, without using a line of
traditional programming code (at least, sometimes... :-) )

Does this help?
--- Nick

"Prasad" <Prasad@discussions.microsoft.com> wrote in message
news:11396454-01BB-4354-9936-F752DB75659D@microsoft.com...
> Hi All,
>
> I am working on a project where I need to integrate Microsoft CRM with an
off-the-shelf product called Syspro, an Accounting application.
>
> The Syspro data is housed in MSSQL (on the same SQL Server as CRM). My
goal is to move the Customers and Sales related data from Syspro to CRM.
Now, you can not just insert data into CRM tables so I need to use Biztalk
Server 2002 to accomplish the task.
>
> Thus far I have documented the similar table/columns in each database
where I want the integration to occur.
>
> Can anybody give me some pointers for how to use Biztalk Server 2002 to
integrate two SQL Databases?
>
> I am new to Biztalk so any help would be appreciated.
>
> Thanks,
> Prasad
>
>
>

[ Post a follow-up to this message ]

Hi Nick,

Thanks for the reply. I already have Biztalk Server 2002 and its not possibl
e to go for BTS 2004 unless it is a free upgrade. :-)

I have installed BTS 2002 SQL Adapter and I am trying to use it with two dum
my tables created in SQL database before jumping into Syspro-CRM integration
. I have created a job in SQL adapter which reads the contents of a table  i
n SQL database using SELECT
statement. I also have corresponding XML schema generated.

Now, my goal is to define an appropriate message port and channel in Biztalk
 server to accept this XML schema. I have been trying to do it but no luck s
o far. I am getting "Document Processing" error in the Operation Log.

Can you please give me some pointers regarding how to create a port and a ch
annel in BTS which will accept the XML schema generated by BTS SQL Adapter?

Thanks in advance,

Prasad
"Nick Malik" wrote:

> Hi Prasad,
>
> If you are new to Biztalk, start with BTS2004.  The prior version is an
> entirely different product, and honestly, the newer one is better (cudos t
o
> MS for continuous improvement).
>
> You don't want to document the rows and columns in the database.  That
> effort, while useful for testing, isn't useful for integration.
>
> You want to document the places where your accounting system generates a
> transaction, and create a "document" heirarchical structure that represent
s
> all of the information about the transaction that is needed by MS CRM to
> accept the data into their system.
>
> I haven't used MS CRM, but I would hope that there's a web service or an A
PI
> where you can submit a single transaction that contains customer and sales
> information, and MS CRM will accept the data, add the customer if they don
't
> already exist, and add or update the sales data.
>
> If there's not one, you may have to write it.
>
> Then, you install Biztalk.  Hook up a SQL Adapter as input and set it up t
o
> pull in data that represents a new transaction that you want to push to CR
M.
> (You will do this in real time, one transaction at a time).
>
> The SQL Adapter will give you data in XML format.  Now, translate that
> format into a transaction that CRM will accept and post it to CRM.
>
> All this is done in an orchestration within Biztalk.  An orchestration is
> basically a graphical "flow-chart-like" language that allows you to descri
be
> the process of handling your transaction, without using a line of
> traditional programming code (at least, sometimes... :-) )
>
> Does this help?
> --- Nick
>
> "Prasad" <Prasad@discussions.microsoft.com> wrote in message
> news:11396454-01BB-4354-9936-F752DB75659D@microsoft.com... 
> off-the-shelf product called Syspro, an Accounting application. 
> goal is to move the Customers and Sales related data from Syspro to CRM.
> Now, you can not just insert data into CRM tables so I need to use Biztalk
> Server 2002 to accomplish the task. 
> where I want the integration to occur. 
> integrate two SQL Databases? 
>
>
>

[ Post a follow-up to this message ]

Hi

Basically, you need these things:

1. Document specification for the input (SQL Adapter generates this
for you when you set up a new job.)

2. Document specification for the output (Look at the example in
C:\Program Files\Microsoft BizTalk Adapter for SQL
Server\Samples\AICSQLSample_INSERT.xml)

3. A map between these two documents (Use BizTalk mapper and draw the
correct lines)

4. a port for the channel that is using the AIC from the SQL Adapter.

5. A channel that accepts this input, has the map and has the correct
output. (First define the document definitions - and set up the AIC in
the "Advanced"-properties of the channel)

How long have you been working with BizTalk 2002? This knowledge will
make it easier for me to write exactly the information you need.

If you allready have all this setup, then try pasting the exact
errorstring from the eventlog.

Jan Eliasen

On Tue, 29 Jun 2004 10:54:02 -0700, Prasad
<Prasad@discussions.microsoft.com> wrote:

>Hi Nick,
>
>Thanks for the reply. I already have Biztalk Server 2002 and its not possib
le to go for BTS 2004 unless it is a free upgrade. :-)
>
>I have installed BTS 2002 SQL Adapter and I am trying to use it with two dummy tabl
es created in SQL database before jumping into Syspro-CRM integration. I have create
d a job in SQL adapter which reads the contents of a table  in SQL database using SE
LEC
T statement. I also have corresponding XML schema generated.[vbcol=seagreen]
>
>Now, my goal is to define an appropriate message port and channel in Biztal
k server to accept this XML schema. I have been trying to do it but no luck 
so far. I am getting "Document Processing" error in the Operation Log.
>
>Can you please give me some pointers regarding how to create a port and a c
hannel in BTS which will accept the XML schema generated by BTS SQL Adapter?
>
>Thanks in advance,
>
>Prasad
>"Nick Malik" wrote:
> 

--
Jan Eliasen, representing himself and not the company he works for.

[ Post a follow-up to this message ]

Hi Jan,

Thanks for your email. Heres the update:

While browsing BTS 2002 SQL Adapter Online Help, I came across Usage Scenari
o 1, which gives you a demo of how BTS can read and write from SQL tables. I
 tried that successfully and since it is very similar to what I am trying to
 do I decided to give it a
shot. This is what I have done so far:

1. I have created three SQL tables (StoreMaster, StoreTemp, and StoreDetails
) similar to three SQL tables in the demo(Order Details, StagingInventory, a
nd POrder)

2. I have created a trigger (tg_InsStoreMaster), which basically watches Sto
reMaster table for INSERT.

3. Whenever a new record is inserted in StoreMaster table, the trigger is ac
tivated, which in turn calls a stored procedure sp_PushData.

4. sp_PushData pushes newly inserted record into StoreTemp table.

5. I have created a job in BTS 2002 SQL Adapter which basically acts as a re
ceive function. It calls a stored procedure sp_StoreTempCheck to tranfer the
 available data in StoreTemp table to BTS 2002. I have the input document sp
ecification - StoreTemp.xml
, which was created automatically.

6. I have created the output document specification - StoreDetailsUpdate.xml
, which is based on the demo file - PurchaseOrderUpdate.xml. I have made all
 the necessary changes like adding/deleting records and/or fields.

7. I have created a map (StoreTemp to StoreDetailsUpdate.xml) which basicall
y relates the input specification to the output specification. The map is ba
sed on the demo file - StagingInventory to  PurchaseOrderUpdate.xml. Again, 
I have made necessary chang
es. For example, the sript functoid is now taking my stored procedure (sp_St
oreDetailsInsert) as a parameter instead of the demo stored procedure (sp_PO
rderInsert).

8. I have created a port for which, primary transport is AICSQL Pipeline. Th
ere is one channel associated to that port, which has proper input/output do
cument specifications as well as a map. It also uses AICSQL Pipeline as a pr
imary transport.

9. Finally, I have created proper routing to the job in BTS 2002 SQL Adapter
 using above channel.

Now, when I insert a new record in StoreMaster table, it gets pushed in Stor
eTemp table. When I start the job, the data gets transmitted to BTS Server f
rom StoreTemp table. Everything is fint until now.

But because of some reason that record is not being written in StoreDetails 
table. I am getting following errors:

an error occurred in BizTalk Server.

Details:
------------------------------
[0x80004005] Failed to parse the XML Document, please refer to the trace
 log for more information
[0x0159] The server encountered a transport error while processing the m
essaging port "Port to Biztalk_Testing StoreDetails", which uses a transport
 component with a ProgID of "AICSQL.SQLPipeline.1".

[0x012b] A transmission attempt failed.


I have checked everything carefully but couldnt find where exactly is the pr
oblem? Any suggestions?

Thanks for all your help,

Prasad


"Jan Eliasen" wrote:

> Hi
>
> Basically, you need these things:
>
> 1. Document specification for the input (SQL Adapter generates this
> for you when you set up a new job.)
>
> 2. Document specification for the output (Look at the example in
> C:\Program Files\Microsoft BizTalk Adapter for SQL
> Server\Samples\AICSQLSample_INSERT.xml)
>
> 3. A map between these two documents (Use BizTalk mapper and draw the
> correct lines)
>
> 4. a port for the channel that is using the AIC from the SQL Adapter.
>
> 5. A channel that accepts this input, has the map and has the correct
> output. (First define the document definitions - and set up the AIC in
> the "Advanced"-properties of the channel)
>
> How long have you been working with BizTalk 2002? This knowledge will
> make it easier for me to write exactly the information you need.
>
> If you allready have all this setup, then try pasting the exact
> errorstring from the eventlog.
>
> Jan Eliasen
>
> On Tue, 29 Jun 2004 10:54:02 -0700, Prasad
> <Prasad@discussions.microsoft.com> wrote:
> 
ECT statement. I also have corresponding XML schema generated.[vbcol=seagreen] 
>
> --
> Jan Eliasen, representing himself and not the company he works for.
>

[ Post a follow-up to this message ]

Hi

I have never tried the example you are talking about, so I do not know
what scripting functoid you are talking about. What I would suggest is
to try the Tracing-utility that comes with the BizTalk Adapter for SQL
Server. It can often tell you what went wrong.

Jan Eliasen

On Thu, 1 Jul 2004 13:33:01 -0700, Prasad
<Prasad@discussions.microsoft.com> wrote:

>Hi Jan,
>
>Thanks for your email. Heres the update:
>
>While browsing BTS 2002 SQL Adapter Online Help, I came across Usage Scenario 1, wh
ich gives you a demo of how BTS can read and write from SQL tables. I tried that suc
cessfully and since it is very similar to what I am trying to do I decided to give i
t a
shot. This is what I have done so far:
>
>1. I have created three SQL tables (StoreMaster, StoreTemp, and StoreDetail
s) similar to three SQL tables in the demo(Order Details, StagingInventory, 
and POrder)
>
>2. I have created a trigger (tg_InsStoreMaster), which basically watches St
oreMaster table for INSERT.
>
>3. Whenever a new record is inserted in StoreMaster table, the trigger is a
ctivated, which in turn calls a stored procedure sp_PushData.
>
>4. sp_PushData pushes newly inserted record into StoreTemp table.
>
>5. I have created a job in BTS 2002 SQL Adapter which basically acts as a receive f
unction. It calls a stored procedure sp_StoreTempCheck to tranfer the available data
 in StoreTemp table to BTS 2002. I have the input document specification - StoreTemp
.xm
l, which was created automatically.
>
>6. I have created the output document specification - StoreDetailsUpdate.xm
l, which is based on the demo file - PurchaseOrderUpdate.xml. I have made al
l the necessary changes like adding/deleting records and/or fields.
>
>7. I have created a map (StoreTemp to StoreDetailsUpdate.xml) which basically relat
es the input specification to the output specification. The map is based on the demo
 file - StagingInventory to  PurchaseOrderUpdate.xml. Again, I have made necessary c
han
ges. For example, the sript functoid is now taking my stored procedure (sp_StoreDetailsInser
t) as a parameter instead of the demo stored procedure (sp_POrderInsert).[vbcol=seagreen]
>
>8. I have created a port for which, primary transport is AICSQL Pipeline. T
here is one channel associated to that port, which has proper input/output d
ocument specifications as well as a map. It also uses AICSQL Pipeline as a p
rimary transport.
>
>9. Finally, I have created proper routing to the job in BTS 2002 SQL Adapte
r using above channel.
>
>Now, when I insert a new record in StoreMaster table, it gets pushed in Sto
reTemp table. When I start the job, the data gets transmitted to BTS Server 
from StoreTemp table. Everything is fint until now.
>
>But because of some reason that record is not being written in StoreDetails
 table. I am getting following errors:
>
>an error occurred in BizTalk Server.
>
> Details:
> ------------------------------
> [0x80004005] Failed to parse the XML Document, please refer to the tra
ce log for more information
> [0x0159] The server encountered a transport error while processing the
 messaging port "Port to Biztalk_Testing StoreDetails", which uses a transpo
rt component with a ProgID of "AICSQL.SQLPipeline.1".
>
> [0x012b] A transmission attempt failed.
>
>
>I have checked everything carefully but couldnt find where exactly is the p
roblem? Any suggestions?
>
>Thanks for all your help,
>
>Prasad
>
>
>"Jan Eliasen" wrote:
> 
LECT statement. I also have corresponding XML schema generated.[vbcol=seagreen] 

--
Jan Eliasen, representing himself and not the company he works for.

[ Post a follow-up to this message ]

Hi Jan,

Thanks for your reply. As you know, I am getting following error while I am 
using BTS 2002 SQL Adapter and BTS 2002 to write data from one SQL table to 
the another:

Details:
------------------------------
[0x80004005] Failed to parse the XML Document, please refer to the trace
 log for more information
[0x0159] The server encountered a transport error while processing the m
essaging port "Port to Biztalk_Testing StoreDetails", which uses a transport
 component with a ProgID of "AICSQL.SQLPipeline.1".

[0x012a] All retry transmissions failed.

[0x80004005] Unspecified error

[0x0156] The server could not finish processing messaging port "Port to 
Biztalk_Testing StoreDetails".

[0x1730] Suspended Queue ID: "{22C14CAA-B6D0-490C-B2BB-A7AE106955A1
}"

[0x80004005] Unspecified erro


I tried to find more information about this error and this is what I conclud
ed. You can tell me whether this is right or not:

Setting up AIC involves three steps:
1. Build the component
2. Register the component
3. Deploy the component in Biztalk

So in other words, one has to write her "own" AIC component in order to use 
it in her application. Is that right? Right now, I am just using "AICSQL SQL
Pipeline" component which is already available for "Application Integration"
 primary transport.

So do I have to write my own AIC component? And if yes, can you give me some
 pointers on how to do that?

Thanks,
Prasad

"Jan Eliasen" wrote:

> Hi
>
> I have never tried the example you are talking about, so I do not know
> what scripting functoid you are talking about. What I would suggest is
> to try the Tracing-utility that comes with the BizTalk Adapter for SQL
> Server. It can often tell you what went wrong.
>
> Jan Eliasen
>
> On Thu, 1 Jul 2004 13:33:01 -0700, Prasad
> <Prasad@discussions.microsoft.com> wrote:
> 
a shot. This is what I have done so far:[vbcol=seagreen] 
xml, which was created automatically.[vbcol=seagreen] 
anges. For example, the sript functoid is now taking my stored procedure (sp_StoreDetailsIns
ert) as a parameter instead of the demo stored procedure (sp_POrderInsert).[vbcol=seagreen] 
SELECT statement. I also have corresponding XML schema generated.[vbcol=seagreen] 
>
> --
> Jan Eliasen, representing himself and not the company he works for.
>

[ Post a follow-up to this message ]

Hi

An AIC is a program BizTalk can celiver a document to in stead og the
normal File, HTTP, SMTP etc.

Yes, you can program your own AIC, but the BizTalk Adapter for SQL
Server IS an AIC, which you can use.

It is programmed, it is build, it is registered and it is deployed.

Under "Start" => "Programs" => "Microsoft BizTalk Adapter Trace
Utility" you will find a trace-utility, that will log all activity in
the SQLAIC. Try it, and maybe you can figure out what is worng. If you
can't, then show us the tracelog here in the newsgroup.

Jan Eliasen

On Fri, 2 Jul 2004 09:50:01 -0700, Prasad
<Prasad@discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Jan,
>
>Thanks for your reply. As you know, I am getting following error while I am
 using BTS 2002 SQL Adapter and BTS 2002 to write data from one SQL table to
 the another:
>
> Details:
>------------------------------
> [0x80004005] Failed to parse the XML Document, please refer to the tra
ce log for more information
> [0x0159] The server encountered a transport error while processing the
 messaging port "Port to Biztalk_Testing StoreDetails", which uses a transpo
rt component with a ProgID of "AICSQL.SQLPipeline.1".
>
> [0x012a] All retry transmissions failed.
>
> [0x80004005] Unspecified error
>
> [0x0156] The server could not finish processing messaging port "Port t
o Biztalk_Testing StoreDetails".
>
> [0x1730] Suspended Queue ID: "{22C14CAA-B6D0-490C-B2BB-A7AE106955
A1}"
>
> [0x80004005] Unspecified erro
>
>
>I tried to find more information about this error and this is what I conclu
ded. You can tell me whether this is right or not:
>
>Setting up AIC involves three steps:
>1. Build the component
>2. Register the component
>3. Deploy the component in Biztalk
>
>So in other words, one has to write her "own" AIC component in order to use
 it in her application. Is that right? Right now, I am just using "AICSQL SQ
LPipeline" component which is already available for "Application Integration
" primary transport.
>
>So do I have to write my own AIC component? And if yes, can you give me som
e pointers on how to do that?
>
>Thanks,
>Prasad
>
>"Jan Eliasen" wrote:
> 
t a shot. This is what I have done so far:[vbcol=seagreen] 
.xml, which was created automatically.[vbcol=seagreen] 
hanges. For example, the sript functoid is now taking my stored procedure (sp_StoreDetailsIn
sert) as a parameter instead of the demo stored procedure (sp_POrderInsert).[vbcol=seagreen]
 
SELECT statement. I also have corresponding XML schema generated.[vbcol=seagreen] 

--
Jan Eliasen, representing himself and not the company he works for.

[ Post a follow-up to this message ]

Hi Jan,

I got it. There was some problem in AICSQLPipeline and I fixed it. Now, I ca
n transmit the data from SQL to Biztalk, process it, and write it back into 
SQL. Thanks for all your help.

I'll come back with more questions on the actual project that I am working o
n - Syspro-CRM Integration.

Prasad

"Jan Eliasen" wrote:

> Hi
>
> An AIC is a program BizTalk can celiver a document to in stead og the
> normal File, HTTP, SMTP etc.
>
> Yes, you can program your own AIC, but the BizTalk Adapter for SQL
> Server IS an AIC, which you can use.
>
> It is programmed, it is build, it is registered and it is deployed.
>
> Under "Start" => "Programs" => "Microsoft BizTalk Adapter Trace
> Utility" you will find a trace-utility, that will log all activity in
> the SQLAIC. Try it, and maybe you can figure out what is worng. If you
> can't, then show us the tracelog here in the newsgroup.
>
> Jan Eliasen
>
> On Fri, 2 Jul 2004 09:50:01 -0700, Prasad
> <Prasad@discussions.microsoft.com> wrote:
> 
it a shot. This is what I have done so far:[vbcol=seagreen] 
mp.xml, which was created automatically.[vbcol=seagreen] 
changes. For example, the sript functoid is now taking my stored procedure (sp_StoreDetailsI
nsert) as a parameter instead of the demo stored procedure (sp_POrderInsert).[vbcol=seagreen
] 
ng SELECT statement. I also have corresponding XML schema generated.[vbcol=seagreen] 
>
> --
> Jan Eliasen, representing himself and not the company he works for.
>

[ Post a follow-up to this message ]

Hi

Glad you got it working

Feel free to ask questions anytime.

Jan Eliasen

On Mon, 5 Jul 2004 08:19:02 -0700, Prasad
<Prasad@discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Jan,
>
>I got it. There was some problem in AICSQLPipeline and I fixed it. Now, I c
an transmit the data from SQL to Biztalk, process it, and write it back into
 SQL. Thanks for all your help.
>
>I'll come back with more questions on the actual project that I am working 
on - Syspro-CRM Integration.
>
>Prasad
>
>"Jan Eliasen" wrote:
> 
e it a shot. This is what I have done so far:[vbcol=seagreen] 
emp.xml, which was created automatically.[vbcol=seagreen] 
y changes. For example, the sript functoid is now taking my stored procedure (sp_StoreDetail
sInsert) as a parameter instead of the demo stored procedure (sp_POrderInsert).[vbcol=seagre
en] 
ing SELECT statement. I also have corresponding XML schema generated.[vbcol=seagreen] 

--
Jan Eliasen, representing himself and not the company he works for.

[ Post a follow-up to this message ]