| Denzil Pinto님의 프로필TheBuzz사진블로그리스트 |  도구  도움말 |
|
6월 30일 How to use the SQL AdapterLets assume you need to pick up data from Sql Server on your receive port and generate and xml file and output it into a particular folder. In order to do that you will need to use the SQL adapter.
So open a new solution. I have upgraded my environment to Visual Studio 2005, BizTalk 2006 and SQL Server 2005. In visual Studio click on File > New > Project, select BizTalk Projects from the project types and Empty BizTalk Project.
Now lets move a little in SQL Server 2005 and create a new Table. Click on Start > All Programs > Sql Server 2005 > Sql Server Management Studio Of course you could do the same thing with Sql Server 2000. Since I updated my system I have now Sql Server 2005. Anyways If you click on Database it expands the node and you can see a list of all available databases. Right click and select New Database > Type in a new name and click OK.
Once you have your new database click on it to expand the node and right click on Tables and select new Table, You will see a grid in front of you. The cool thing about sql server 2005 is that it has a visual studio look to it. On the right hand side you will find properties for you table. In the middle you have a grid wherein you type in your column name, DataType and Allow Nulls and just at the bottom you have a column properties property grid. I woudl say it has a Microsoft Access look, if you have ever used it and tried to create new tables. On the left hand side you have the object explorer.
Now lets type in the names of the columns for this example i have three columns
OrderID varchar(50) No (-- Allow Nulls)
OrderDescription Varchar(100) No
Quantity int No
Click on Save in the toolbar and enter a name for my example I have entered Product. In order to enter data right click in the object explorer your table name and select open table. You have a grid in middle enter your temporary values there.
Now once that is done lets go back to our biztalk solution file. Right Click on the project file and select Add > Add Generated Items > Add Adapter Metadata. Click on Add. From the list provided select SQL and you should have the Sql Server pointing to your local sql server and the Database : BizTalkMgmtDB. Do not select a port as yet and click on Next. On the next screen which is the connection string click on Set.
Now 1. Select your Sql Server name from the drop down.
2. If you have a Sql Server login name or password enter that or select Use Windows NT Integrated Security. Again make sure it matches your sql server configuration that you have already setup. For this example I am selecting Use Windows NT Integrated Security.
3. Select the database from the drop down list and click Test Connection to make there is a valid connection.
Now click on OK. Once you have completed that Click on Next and you will reach the Schema Information screen.
In the Target namespace enter http://Orders. This is going to be our target namespace. Remember in publish/subscribe (article posted on this site) I mentioned something about how the messaging engine works, well we need the target namespace to allow our send port to subscribe to the right message type. Now for the port type select Receive port. Because in this example we are going to receive data from sql server and then create a xml file.
In the Document root element name I entered Orders, this is because my root node, click on next. Select "Select statement" and click on next and then in the space provided type in : select * from product for xml auto and then click on Next. After that click on Finish. You will now have a schema file generated for you.
Now lets go ahead and create a simple map. Right click on your project file and select add > add new items and click on Map Files and then select Map. Enter the name and then click on Add.
Click on Source Schema and then expand the Schema and then click on the schema which was just included in your project file. If you havent renamed it, it should be called SQLService.xsd. Click on OK. Once you have done that click on the destination schema and then click on the schema file again and then click on OK
Open your Toolbox if you dont have it, click on View > Toolbox and then click on Advanced Functoids. Click on the Mass copy and then drag and drop it onto the grid. Click on Product on the source schema and then drag and drop it to the Mass copy icon on the grid, do the same for the destination schema click on the product and drag and drop it onto Mass copy on the grid. Once that is done save your work by click on the Save button in your toolbar.
Now lets go ahead and create an orchestration. Right click on your project file and select Add > Add New item and click on Orchestration File and then select BizTalk Orchestration, enter a new name and click on Add.
In your orchestration designer, lets start and create our business process. so look at the toolbox click on Receive drag and drop it just under the green start button. Now if you look on your right hand side you will notice your orchestration view. If you dont have it open click on View > Other windows > Orchestration View.
Now in your orchestration view expand Port Types right click and select New One Way Port Type. You will have Port Type_1. Right click on it and select rename, or in the properties window click on identifier and enter a new name. Now if you expand PortType_1 or the name that you have just assigned it you will find Operation_1 click on identifier in the properties window or right click on it and select rename and enter a new name.
If you expand Operation_1 or the name you have just given it you will find Request. Now click on the Message Type and then expand Schemas and select SQLService (in my case), if you have renamed it select the schema that you just included in your project file. Leave the name as Request.
Now in your orchestration designer click on Port from the toolbox and drag and drop it onto the port surface(left hand side). You will have a get the Port Configuration Wizard, click on next, Enter a new name, and then click on next. On the select the port type select "Use an Existing Port Type" and from the Available Port Types: expand Current Project and select PortType_1 or the select the new one that you created, click on next.
Now for the Port direction of communication select "I'll always be receiving messages on this port", leave the port binding as specify later and click on next. Now click on Finish. Now if you look at your orchestration designer you will find a Port on the port surface and the receive_1 just under your start button. Now click on Receive_1 and in the properties window, go to the name and enter a new Name. Click on Operation and select the Request operation that you just created. In my case it is Port_1.Operation_1.Request. If you have renamed it, it will look different on your end. But once you have done it you will notice a line drawn from your port to your receive. But you will notice a red exclamation mark on your receive.
In your orchestration view. expand Messages and then right click and select new message. Click on Message_1 and in the properties window, go to identifier click it and enter a new name and then click on Message Type and expand schemas select SQLService (in mycase), if you have changed the name of the schema file in your project, it should reflect the changed schema file name.
Now that is done. click on your receive in the orchestration designer which is just underneath the green start button and in the properties window, click on Message and from the drop down select you new message that you just created. Just one more thing you will find a activate in your properties window for the receive select True. Since we are not receiving a physical file at our receive location we will need to instantiate this orchestration upon the receipt of a message. If you dont do that you will get an error when compiling.
Now lets go ahead and create a new message. In your orchestration view right click on Messages > New Message. Click on the new Message and in the properties window click on identifier and enter a new name, then click on Message Type and click on the ellipsis. Then expand schema and select the schema in your project file.
Now that is done we have to now Contruct a new message in our orchestration. What is contructing a new message: well it means to compose a new message. We need this inorder to send a message as a xml file, remember we are outputing an xml file to a folder. Click on the contruct message in your toolbox, drag and drop it underneath the receive. Now click on the contruct and in your properties window go to message constructed and from the dropdown select the second message you just created. The first message is used by the receive. The second message would be used by the contruct icon. Click on name and enter a new name.
Now click on the Transform in your toolbox and drag and drop it in your constuct message. Make sure to drag and drop the transfrom into the construct message not outside it. Now click on transform and in the properties window, go to Input message and click on the ellipsis. A transform configuration dialog box will open. Select Existing map and select the map that you created from the drop down.
Now look at the two lists the left hand side had Source click on it and in the Source_Transform list select your first message. Now click on Destination and in the source Transform list select the second message. click on ok. Once that has been done you will notice that the map name will contain the map name and the output message will have the second message that you created in the properties window. Click on Name and enter a new name.
Now let go ahead and create another port. We have a receive port, now we need a send port. so in your orchestration view click on the port types. Again right click on Port types and select new one way port type and then click on the new port and lets open properties. In the properties window you will find the identifier, enter a new name for your port type. Then expand your new port type and click on Operation. In the properties window click on identifier and enter a new identifier. Now click on Request. Go to name and name it as Send and Message Type select the schema you have included in your project, to do this click on the ellipsis and in the dialog box that appears, expand schema and select your schema.
Now lets go ahead and include a port in our orchestration. Click on Port in your toolbox and then drag and drop it on to the port surface. You can drop it on the left or the right. Now once you have done that you will have in front of you, the port configuration wizard. Click on next. Enter a new name for your send port and then click on next
Select use an existing port type and from the list select the second port type you created with the name send. Then click on next. Select I'll always be sending messages on this port and then leave the port binding as is and click on next. Click on Finish.
Now click on the Send in the toolbox and drag and drop it underneath your constuct message. Click on Send_1 and in the properties window go to the message and select the second message you created. Then click on name and enter a name. Click on Operation and then select the send operation in your drop down.
Now that is done we need to create a correlation set. A correlation set helps the orchestration runtime route messages to proper orchestration instance. So to create a correlation set, In your orchestration view right click on Correleation Types and select new correlation type. Click on the new correlation type and in the correleation properties click on the ellipsis then expand BTS and then click on Message Type and click on Add. Click on OK.
Now right click on Correlation Sets in your Orchestration View and select new correlation set. Click on the new correlation set and select correlation type and from the drop down list, select the new correlation type you just created. Now click on the receive image, the first one after the green button and in the properties window you will find initializing correlation. Click on it and from the drop down list select the new correlation set you just created. Now go to the Send image which is one before the red button and again click on it and in the properties window you will have Following correlation set click on it and from the drop down select the correlation set you just created. Now lets go ahead and compile your solution file.
Right click on your project file and select Build. Now lets deploy your solution. To deploy right click on your project file in your solution explorer and select deploy.
Now lets go ahead and create a receive port and receive location. So open your biztalk explorer, if you dont have it open. Then click on View > BizTalk Explorer. Now right click on Receive Ports and select Add Receive Port. Now select one-way port and click ok. Enter a new name and click ok. You will find receive location underneath your receive port Right click on Receive location and click on Add Receive Location. In the receive location properties, enter a new name.
Transport Type : select as SQL
Address (URI) : click on it and then click on the ellipsis and in the SQL Transport Properties put in the following value
Click on Connection String and again click on the ellipsis
Select your SQL Server name and then select the logon information or otherwise select use Windows NT
integrated security.
Select the database name from the drop down list and then click on Test connection and then click on OK
Once that is done then click on Document Root Element Name and enter Orders
Click on Document Target Namespace and enter http://Orders
Click on SQL Command and click on the ellipsis, From the Project drop down select your project file and from the Schema drop down select you schema file and then click on OK
Now Click on the Receive Handler and from the drop down select your BizTalkServerApplication.
Click on the Receive Pipeline and from the list select Microsoft.BizTalk.DefaultPipelines.XMLReceive
Now click on OK.
Now right click on the Send Ports in your BizTalk Explorer and select Add Send Port then select Static One-Way Port and click on OK. In the Static One-Way Send Port Properties enter the following.
Click on Transport Type and from the drop down select FILE.
Click on Address (URI) and then click on the ellipsis. In the destination folder type in or click on Browse and select the folder where you want the xml file to be dropped.
Leave the filename if you can think of one. Select the copy mode to Create New or any other that you wish and then click on ok.
Now click on the Send Folder below the Transport and then click on General, click on the Send Pipleline and from the drop down list select Microsoft.BizTalk.DefaultPipelines.xmlTransmit.
Now click on the Filter and Maps and then click on Filters. Now on the right hand side you will find a grid. Click on the Property and from the list select BTS.MessageType. The Operation should be == and in the Value click on it and type in http://Orders#Orders. Then click on Apply and click on OK to close.
Now lets expand the Orchestrations folder in your BizTalk Explorer, and you should find your project file.orchestration file. If you do not find it, click on the main node which should be your biztalk management database and click on refresh. Once you find your orchestration right click on your orchestration and select Bind, and now from the Port binding properties wndow. In the Inbound Ports click on Port_1 and from the drop down select your receive port and in port_2 from the drop down select your send port that you just created.
Select Host from the left hand side list and in the host select your biztalkserverapplication from the list and click on Apply and then OK.
And that should be it you have binded your Orchestration that is left is to start it. Right click on your orchestration again and you should have enlist. Click on it and wait until the process is done and your mouse pointer is back and then again right click on your orchestration and then click on start. It will start all the dependency which is your receive location, send ports, send port groups if you have any and your biztalk host. Make sure they are selected and then click on OK, and now you should go to your output folder and see the files. If you want to stop the process. Again just click on your orchestration from you biztalk explorer and select Stop and then unenlist.
Thats it
트랙백이 블로그의 트랙백 URL은 다음과 같습니다. http://denzilpinto.spaces.live.com/blog/cns!A2256C53CD2EC18E!252.trak 이 블로그를 참조하는 웹 로그
|
|
|
