SSIS Best practices
1. Readme.doc – explains the thorough purpose and functionality of the package.
2. Names for all tasks, objects, and connections should be self descriptive about the purpose. Never hard code specific database or server in names since it might change.
3. Modular structure of packages i.e. multiple number of packages is preferred over one big package to cover a big complex functionality.
4. SSIS has a significant amount of built-in functionality; use that instead of custom scripting.
5. Use configuration files to take the connection string or other properties which might change and export only configurable values.
6. Use SSIS built-in logging capabilities to log serious package error conditions and any additional information that may be useful for troubleshooting the package.
7. Use checkpoints in your packages i.e. the tasks need to be organized in a logical manner and if it makes sense, group them together via the Sequence Container to allow for the logical restart of the group if one of the tasks inside the container failed.
8. Store SSIS Configuration files in the designated folder given by DBA and it should be accessible from all the environments.
9. We should manage the configuration files to allow the same SSIS package to run in dev, test and prod environments.
10. Learn how to create a SSIS configuration file.
11. We should structure SSIS packages and configuration files with the goal of minimizing the number of different SSIS packages deployed, especially in cases where SSIS packages are very similar to one another except for some key properties, such as server and database names
12. Developers that need to connect to SSIS on a server will need to be granted membership to the “Distributed COM Users” group on that machine.
13. Learn how to deploy a package.
14. We can create SQL serve Jobs in SQL Agent to execute SSIS package.
15. Security
Protection Mechanism Details
Set protection level of packages Protection Level: ServerStorage
The protection afforded is based on SQL Server database roles. The protection level will rely on server storage and roles for access control. All packages saved in MSDB will use this protection level.
Save packages to MSDB Package Storage Location: MSDB
Saving packages to the msdb database will help provide security and the server, database, and table levels. Packages saved in MSDB will benefit from the integration service database level roles (db_ssisadmin, db_ssisltduser, db_ssisoperator). Any other options aside from saving to MSDB will be evaluated in a case by case basis and discussed with the database administrators and the information security office.
Save logs to MSDB Log Location: MSDB
Logs to MSDB. This enhances the availability of log data to tech supports outside of SQL Server. Other advanced logging options outside of SQL Server will be evaluated in a case by case basis and discussed with the database administrators and the information security office.
Save configuration in XML format Configuration Location: File system (XML format)
Ensure that configuration file has no sensitive data (e.g. connection strings) and the file and folder it is located is properly secured. Contact the appropriate database administrator and the Information Security Office for assistance for securing the folder.
Use of SQL Jobs in Package Execution Package Execution: through SQL Jobs
All packages should be executed from a SQL job. Packages executed other ways (e.g. Windows Scheduler) need to have a notification process established and should be an exception of the rule.
16. Monitoring folders for dropped files.
17. moving files from one file to another.
18. Copying data from an excel spread sheet into SQL Server.
19. Logging.
20. SSIS Naming conventions
companyname_application_taskDescription
Verify that the name and description of the task of component sufficiently describes it. Often times, the default name and description assigned to a task or component may not clearly or accurately indicate its purpose.
Prefix each task or component’s name with an identifier to describe the type of task or component it is.
[SSIS Package Name]_[Environment Name].dtsConfig
Sunday, August 30, 2009
Thursday, August 20, 2009
The Girl Next to me
Few things are destined to happen. And sometimes they are beyond our control. So I always go with the philosophy “Whatever happens, happen for good”.
Yesterday I was quite overloaded with work. “It is impossible to wrap up the pending work in next half an hour. I am definitely going to miss the bus” I said to myself. I gave up all the hope and decided to go home only after finishing the work left. I guess I could have completed the task if this thought wouldn’t have come in my mind. A human mind is like that only. The biggest obstacle lies only when we keep our eye off the goal. Somehow I was trying to keep myself focused on work but every now and then my eyes were frowning to the clock. People either give their best under pressure or break. The later part happened in my case. I got up from my seat and rushed towards the bus. I didn’t even think what I will say to my lead if he will ask tomorrow. My feet were moving steadily towards the bus.
I got into the bus but to my astonishment Not a single seat was vacant. I got down and took the other one. Even it was full. My destiny was riding me somewhere else. Finally I took the third one. To my relief I saw a seat vacant. I rushed towards it as if it was food and I was starving from years. I even didn’t bother to look if any other seat was vacant or who is sitting next to me. When I was going towards the seat I found Nishi in the next seat. Even she looked up at the same time and we had an eye contact. She got uncomfortable and so was I. I didn’t know what to do and in all this mess up I sat just next to her. I completely missed the vacant seat which was next to it. She had also noticed that. I was afraid what she must be thinking of me. She will take it as a deliberate act of mine. I noticed her face.she was getting pale. Totally embarrassed she was looking here and there.
I totally lost into my old memories of the AMEX days. We were in the same floor though different Projects.I was thoroughly disgusted with the idea of going into an ODC. I had heard a lot about ODC‘s being jail. No access to communicators, outlook etc. But soon I entered the ODC premises; all my ill feelings got vanished. I saw someone who was definitely not the most beautiful but the serenity of her face was extreme. I was spell bound to see such a wonderful creation of God. She was above par simple and sober.
Days followed on. Even I got busy in my project. But still I always find out ways of going near her cubicle to have a glance of her. Even a peep used to give me immense joy. My Gang in project was all sport. Soon they traced out my feelings for her. They all were very supportive throughout this one sided love saga. All the hiccups what was happening, even she was not a dumb, she sensed it all. When I sent friend request on IM, she declined it gracefully.One day while we were returning from lunch, she was reading newspaper all alone. “You won’t get a better than this chance to show your emotions” Sandy hissed. Rest supported him. Before I could say anything someone pushed me from behind towards her. I was totally in shock since she was sitting next to me. I mumbled “Hi! Anything new in the newspaper??” She was equally stunned. Before she could reply I said sorry and moved from there. I didn’t know why I behaved like that. I was in complete panic. I knew I messed up everything. The very next day I had to go to my home city. I was regretting why I did that foolishness. At least I could have waited till I will return from home. My stay at home could be much better if I would have taken the sweet memories of her with me. But it was other way round. When I returned back, my eyes were looking for her. But she was no more there. I asked my friends about her. Kash grinned “I have not seen her from last one week. She might have been released from the project”. All my regret went off. I was satisfied at least I spoke to her that day though one-sided again, but from so close. But I was unhappy from my heart. Days passed then months.
“Excuse me…Excuse me…Vikas...… Vikas Please give me way to go. I need to get down” This familiar voice wakened me up from my day dream. I said sorry and slightly moved from my seat. Gently she said thanks and got down at her stop.
I was cursing myself “you useless fellow…again you missed this chance…you were day dreaming when you should be talking to her...You are a big time loser…you understand Mr. vikas kumar..You are a big time loser…” I felt gloomy. But all of a sudden one thought passed by my mind. It took all the misery from my mind. I felt relieved. Hey she still remembers your name….In all those happiness I got down from the bus way behind my stop. But I was not regretting this time.
I rehearsed again “Whatever happens, happen for good”.
Yesterday I was quite overloaded with work. “It is impossible to wrap up the pending work in next half an hour. I am definitely going to miss the bus” I said to myself. I gave up all the hope and decided to go home only after finishing the work left. I guess I could have completed the task if this thought wouldn’t have come in my mind. A human mind is like that only. The biggest obstacle lies only when we keep our eye off the goal. Somehow I was trying to keep myself focused on work but every now and then my eyes were frowning to the clock. People either give their best under pressure or break. The later part happened in my case. I got up from my seat and rushed towards the bus. I didn’t even think what I will say to my lead if he will ask tomorrow. My feet were moving steadily towards the bus.
I got into the bus but to my astonishment Not a single seat was vacant. I got down and took the other one. Even it was full. My destiny was riding me somewhere else. Finally I took the third one. To my relief I saw a seat vacant. I rushed towards it as if it was food and I was starving from years. I even didn’t bother to look if any other seat was vacant or who is sitting next to me. When I was going towards the seat I found Nishi in the next seat. Even she looked up at the same time and we had an eye contact. She got uncomfortable and so was I. I didn’t know what to do and in all this mess up I sat just next to her. I completely missed the vacant seat which was next to it. She had also noticed that. I was afraid what she must be thinking of me. She will take it as a deliberate act of mine. I noticed her face.she was getting pale. Totally embarrassed she was looking here and there.
I totally lost into my old memories of the AMEX days. We were in the same floor though different Projects.I was thoroughly disgusted with the idea of going into an ODC. I had heard a lot about ODC‘s being jail. No access to communicators, outlook etc. But soon I entered the ODC premises; all my ill feelings got vanished. I saw someone who was definitely not the most beautiful but the serenity of her face was extreme. I was spell bound to see such a wonderful creation of God. She was above par simple and sober.
Days followed on. Even I got busy in my project. But still I always find out ways of going near her cubicle to have a glance of her. Even a peep used to give me immense joy. My Gang in project was all sport. Soon they traced out my feelings for her. They all were very supportive throughout this one sided love saga. All the hiccups what was happening, even she was not a dumb, she sensed it all. When I sent friend request on IM, she declined it gracefully.One day while we were returning from lunch, she was reading newspaper all alone. “You won’t get a better than this chance to show your emotions” Sandy hissed. Rest supported him. Before I could say anything someone pushed me from behind towards her. I was totally in shock since she was sitting next to me. I mumbled “Hi! Anything new in the newspaper??” She was equally stunned. Before she could reply I said sorry and moved from there. I didn’t know why I behaved like that. I was in complete panic. I knew I messed up everything. The very next day I had to go to my home city. I was regretting why I did that foolishness. At least I could have waited till I will return from home. My stay at home could be much better if I would have taken the sweet memories of her with me. But it was other way round. When I returned back, my eyes were looking for her. But she was no more there. I asked my friends about her. Kash grinned “I have not seen her from last one week. She might have been released from the project”. All my regret went off. I was satisfied at least I spoke to her that day though one-sided again, but from so close. But I was unhappy from my heart. Days passed then months.
“Excuse me…Excuse me…Vikas...… Vikas Please give me way to go. I need to get down” This familiar voice wakened me up from my day dream. I said sorry and slightly moved from my seat. Gently she said thanks and got down at her stop.
I was cursing myself “you useless fellow…again you missed this chance…you were day dreaming when you should be talking to her...You are a big time loser…you understand Mr. vikas kumar..You are a big time loser…” I felt gloomy. But all of a sudden one thought passed by my mind. It took all the misery from my mind. I felt relieved. Hey she still remembers your name….In all those happiness I got down from the bus way behind my stop. But I was not regretting this time.
I rehearsed again “Whatever happens, happen for good”.
Friday, August 7, 2009
Sometimes losing is everything.
Being Successful doesn't mean being satisfied...Success and Satisfaction are oxymoron at times. Sometimes only winning is important and not the way in which it is being achieved. Sacrifices lose value and the passion to win becomes the only objective. And so we die to win.
But, something happened to me recently, which forced me to believe that sometimes winning is not everything.
Sometimes losing is everything.
I had always wanted to start my own firm. I could never work under anyone. Because, for me I am the Boss and I can never take any orders from anyone. I don’t know when I developed this sort of feelings but it is there since I was a school going kid.
I always wanted to be at the supreme position of any organization and the easiest way was to start a firm of your own. But this was not easy at all, I had to make many sacrifices in life and here I am the chairperson of Groupwell advertising Ltd.
I was going through the emails of our clients and suddenly stuck on a mail from Mehta &Sons Ltd. Last week only we had given demo & quotation copy to them.
I thought it must be a confirmation mail from their side for the billing but to my disappointment it was rather a notification that they are giving their advertising rights to Shalini Advrt. Group. I was all stunned. We had already spent so much efforts and money into this project. I will not let it go like this.
I called up my legal consultant and asked him to look into this and to update me ASAP. In an hour I received a mail from Mrs Shalini Mathur, the managing director of our rival group. She had requested for a meeting with me. Generally I don’t entertain such mails but I didn’t know why I not only replied positively but also agreed to meet her in her company. Perhaps some force was driving me from behind.
In no time I reached her company entrance. I was about to go inside but suddenly I saw someone whom I can recognize even after 100 years. The receptionist was she…Farina…I was all amused. Oh my God after fourteen years I was seeing her. I never expected that I would see her as a receptionist. We were in the same class. She was probably the brightest student of my class and I always knew she would do something bigger in her life.
I could remember the last day when we met before I left for Mumbai for my higher education. Her eyes were moist and heart was crying. How I consoled her saying I wanted to grow, prosper do something extra ordinary. I need to focus on my career. Forget everything what was there between us. May be I could return but for now forget it completely. Moreover we are from different religions and I have neither the guts nor time to put this up in front of our parents. That very evening I came down to Mumbai to pursue higher education. After that everything changed and I forgot everything it as if no Farina was there in my life.
I called Mrs. Mathur and apologized that I am not feeling well so would meet her tomorrow.
I went to her with a heavy heart and greeted her. She was equally shocked to see me. She asked me the purpose of my visit but I gave an excuse. She took me to the cafeteria and we had coffee together. She used to be a sport in college days but here she seemed to be so sad. In an hour we talked very little, looked at each other and cried. She said she is still in contact with Rakshita who was our mutual friend. I took the contact number of Rakshita and promised that definitely I will call her up.
In the night I called Rakshita. Then I came to know how Farina fell in love with a Hindu boy. Then she got married with him against her parents wish. Both have been excluded from their family. He is still unemployed and often beats her up.
She is working somewhere as a receptionist since she couldn’t continue her education due to marriage.
I was feeling very sorry for her. I felt she missed my absence so much that she tried to fill it by Rakesh. And unfortunately he ruined her life. I knew I was responsible for it by some way or the other.
I called Mrs. Mathur in my office to sort out the things. She gave me No objection papers and pleaded me not to file any case against Mehta’s as this project is very important to them. This one can revive their ailing business and save from bankruptcy. I didn’t give her any words but assured for a discussion with my team.
It was very tough for me to decide what to do. All the hopes of my employees and their efforts were at stake. Above all my winning attitude was looking at me with jeer. On the other hand her job was at stake. I could offer her a job in my office but she will never accept I knew. It was the time for penance.
I signed on the NOC papers and sighed. Even after winning so many things I have never felt so relaxed in my whole life what I experienced after losing this one. This was an eternal satisfaction.
But, something happened to me recently, which forced me to believe that sometimes winning is not everything.
Sometimes losing is everything.
I had always wanted to start my own firm. I could never work under anyone. Because, for me I am the Boss and I can never take any orders from anyone. I don’t know when I developed this sort of feelings but it is there since I was a school going kid.
I always wanted to be at the supreme position of any organization and the easiest way was to start a firm of your own. But this was not easy at all, I had to make many sacrifices in life and here I am the chairperson of Groupwell advertising Ltd.
I was going through the emails of our clients and suddenly stuck on a mail from Mehta &Sons Ltd. Last week only we had given demo & quotation copy to them.
I thought it must be a confirmation mail from their side for the billing but to my disappointment it was rather a notification that they are giving their advertising rights to Shalini Advrt. Group. I was all stunned. We had already spent so much efforts and money into this project. I will not let it go like this.
I called up my legal consultant and asked him to look into this and to update me ASAP. In an hour I received a mail from Mrs Shalini Mathur, the managing director of our rival group. She had requested for a meeting with me. Generally I don’t entertain such mails but I didn’t know why I not only replied positively but also agreed to meet her in her company. Perhaps some force was driving me from behind.
In no time I reached her company entrance. I was about to go inside but suddenly I saw someone whom I can recognize even after 100 years. The receptionist was she…Farina…I was all amused. Oh my God after fourteen years I was seeing her. I never expected that I would see her as a receptionist. We were in the same class. She was probably the brightest student of my class and I always knew she would do something bigger in her life.
I could remember the last day when we met before I left for Mumbai for my higher education. Her eyes were moist and heart was crying. How I consoled her saying I wanted to grow, prosper do something extra ordinary. I need to focus on my career. Forget everything what was there between us. May be I could return but for now forget it completely. Moreover we are from different religions and I have neither the guts nor time to put this up in front of our parents. That very evening I came down to Mumbai to pursue higher education. After that everything changed and I forgot everything it as if no Farina was there in my life.
I called Mrs. Mathur and apologized that I am not feeling well so would meet her tomorrow.
I went to her with a heavy heart and greeted her. She was equally shocked to see me. She asked me the purpose of my visit but I gave an excuse. She took me to the cafeteria and we had coffee together. She used to be a sport in college days but here she seemed to be so sad. In an hour we talked very little, looked at each other and cried. She said she is still in contact with Rakshita who was our mutual friend. I took the contact number of Rakshita and promised that definitely I will call her up.
In the night I called Rakshita. Then I came to know how Farina fell in love with a Hindu boy. Then she got married with him against her parents wish. Both have been excluded from their family. He is still unemployed and often beats her up.
She is working somewhere as a receptionist since she couldn’t continue her education due to marriage.
I was feeling very sorry for her. I felt she missed my absence so much that she tried to fill it by Rakesh. And unfortunately he ruined her life. I knew I was responsible for it by some way or the other.
I called Mrs. Mathur in my office to sort out the things. She gave me No objection papers and pleaded me not to file any case against Mehta’s as this project is very important to them. This one can revive their ailing business and save from bankruptcy. I didn’t give her any words but assured for a discussion with my team.
It was very tough for me to decide what to do. All the hopes of my employees and their efforts were at stake. Above all my winning attitude was looking at me with jeer. On the other hand her job was at stake. I could offer her a job in my office but she will never accept I knew. It was the time for penance.
I signed on the NOC papers and sighed. Even after winning so many things I have never felt so relaxed in my whole life what I experienced after losing this one. This was an eternal satisfaction.
Thursday, August 6, 2009
Dealing with a Convoy in BizTalk
Biztalk is a messaging system which is based on traditional publish and subscribe approach. In few circumstances, we need to process messages in a way that the above approach cannot handle. To meet these special business requirements, convoys comes into the picture.
• Sequential convoy - Items that are in a order, one after another for example item 1 will finish and then item 2 will start and when it will finish item 3 will start. Once all completed an action will take place.
• Parallel convoy - Items that are in any order, but must all be present before something occurs. In this case all actions item 1, item 2 and item 3 will be processed parallely independently but once we get responses of all(the order doesnot matter),an action will take place.
Consideration for using convoy processing with a business process involves following these rules:
• The correlation set used on the Receive shapes may not contain more than three properties used for correlation. A correlation set is a list of properties with specific values that you use to route messages a specific business process. This is because these values identify and store at the database level. This has support for a maximum of three parameters.
• Parallel and sequential convoy may coexist in the same business process, but they may not share any correlation sets with each other. This is because each correlation set may only belong to one convoy.
• BizTalk Server 2004 does not support convoy processing when passing in an already initialized correlation set into a new orchestration using the start orchestration. This is because convoy sets handle at the database level, independent of already running orchestration instances.
Parallel Convoy is nothing but parallel shape with receive. So follwing rules applies to that:
a. If one of the tasks of a parallel activation has an active Receive as the first shape, all of the tasks of that parallel activation must have an active Receive as the first shape, and all of those Receive shapes must initialize at least one correlation.
b. If a particular correlation is initialized on more than one task of a parallel activation by a Receive shape, each of the concerned activating Receive shapes must initialize exactly the same correlation.
Sequential receive convoys can be additionally broken down into two sub-categories:
• Uniform sequential receive convoys
• Non-uniform sequential receive convoys
Uniform sequential receive message processing exists when many instances of a message is received and all must be handled by the same business process as in case of FILE Adapter if we want FIFO mechanism.
Non-Uniform Sequential Receives
In the non-uniform sequential receive design pattern, the receipt of two or more different types of messages occurs in a known order before the rest of the business process can continue. Definition of the order of the received messages occurs inside the business process, although BizTalk Server 2004 does not enforce the order other than for the first message.
Note: Convoys have the drawbacks of bottlenecks and zombies. So it should be only used when there is such a business requirement.
• Sequential convoy - Items that are in a order, one after another for example item 1 will finish and then item 2 will start and when it will finish item 3 will start. Once all completed an action will take place.
• Parallel convoy - Items that are in any order, but must all be present before something occurs. In this case all actions item 1, item 2 and item 3 will be processed parallely independently but once we get responses of all(the order doesnot matter),an action will take place.
Consideration for using convoy processing with a business process involves following these rules:
• The correlation set used on the Receive shapes may not contain more than three properties used for correlation. A correlation set is a list of properties with specific values that you use to route messages a specific business process. This is because these values identify and store at the database level. This has support for a maximum of three parameters.
• Parallel and sequential convoy may coexist in the same business process, but they may not share any correlation sets with each other. This is because each correlation set may only belong to one convoy.
• BizTalk Server 2004 does not support convoy processing when passing in an already initialized correlation set into a new orchestration using the start orchestration. This is because convoy sets handle at the database level, independent of already running orchestration instances.
Parallel Convoy is nothing but parallel shape with receive. So follwing rules applies to that:
a. If one of the tasks of a parallel activation has an active Receive as the first shape, all of the tasks of that parallel activation must have an active Receive as the first shape, and all of those Receive shapes must initialize at least one correlation.
b. If a particular correlation is initialized on more than one task of a parallel activation by a Receive shape, each of the concerned activating Receive shapes must initialize exactly the same correlation.
Sequential receive convoys can be additionally broken down into two sub-categories:
• Uniform sequential receive convoys
• Non-uniform sequential receive convoys
Uniform sequential receive message processing exists when many instances of a message is received and all must be handled by the same business process as in case of FILE Adapter if we want FIFO mechanism.
Non-Uniform Sequential Receives
In the non-uniform sequential receive design pattern, the receipt of two or more different types of messages occurs in a known order before the rest of the business process can continue. Definition of the order of the received messages occurs inside the business process, although BizTalk Server 2004 does not enforce the order other than for the first message.
Note: Convoys have the drawbacks of bottlenecks and zombies. So it should be only used when there is such a business requirement.
Tuesday, August 4, 2009
SQL Adapter & BizTalk
SQL Adapter & BizTalk
The SQL adapter retrieves an XML message from the BizTalk MessageBox and ultimately do operations in a remote SQL Server database, all based on the contents of the XML message. The SQL adapter enables BizTalk to poll a SQL Server database via a SQL query or stored procedure to retrieve rows as one or more XML messages. Conversely, the SQL adapter can be used to insert, update, or delete rows in SQL Server tables using the SQL updategrams or by invoking predefined stored procedures.
Major Concern while dealing with SQL adapter:
a) Same Record should not be fetched again and again:
Use Record status flag which is by default 0 means record is not yet picked.
Use data Range
b) Handle the Exceptions which may occur while doing operations into a SQL table.
SQL Adapter at Send side:
1. Updategram(Insert\Update\Delete)
a. Add Generated items Add Sql Adapter
i. conn string (Server Name,DBName, Port Name)
ii. Target Namespace (very imp)
iii. Request & response document root element name
iv. Select Updategram Select (Insert\Update\Delete)
This will create two artifacts in our solution. 1. Orchestration 2. Schema
The structure of the schema generated will be different for insert, update and delete.
The key concept while using Updategram is that we have a before and after section in the updategram message that SQL Server uses to figure out what to do with the data. For instance, having a record in the before but not in the after is equal to a Delete operation in SQL Server. Also, an after record with no before equals an Insert and a record in both the before and after results in an Update.
Again in admin console we need to give the connection string and other details while configuring the SQL Send Port.
Note: To modify multiple tables, we can run the wizard multiple times and then copy the generated schemas into a single shema. However this is achievable using updategrams but we prefer stored procedures for it.
2. Stored Procedure
Same as above except in point iv we select Stored Procedure, select the spname and generate.
This will also create two artifacts one orchestration and one schema.
Advantages using Stored Procedure over updategram:
1. SELECT\INSERT\UPDATE\DELETE possible.
2. Procedure can be called to return data from a DB.
3. We can have input params to the sp but could take the entire contents of the xml message and parse the contents of the message.
SQL Adapter at Receive side:
Steps are same as above in Stored Procedure.
Since queries in SQL returns rowsets, we need a mechanism to retrieve records in xml format.
FOR XML AUTO, XMLDATA, ELEMENTS is used for that.
For xml auto returns in xml format.
Xml data is required only while running the wizard to generate the schema for the resultset from the sp. While testing we should remove this clause.
IMPORTANT: BT uses the root element name and the namespace to route the inbound xml file. If both does not match to the xsd file created, it will give routing errors.
For XML Explicit gives developer greater level of control in the resulting XML structure that is returned to BizTalk.
Adapter Configurations:
1. Poll while data found: indicates whether to continue polling for new records in additional batches or whether to submit a single query result for the entire polling window.
2. Polling interval: Identifies how often to poll for records.
3. Polling unit of measure: Identifies the unit of measure for polling.
4. connection string
5. Document Root element name
6. document Target namespace
7. SQL command
8. URI : Identifies a unique identifier for the receive location. This value is autogenerated.
I think this is enough of SQL adapter basics as far as its connectivity to BizTalk engine is considered. Lately I have come across a very good article on the same at this:
http://blogs.msdn.com/richardbpi/archive/2005/06.aspx
The SQL adapter retrieves an XML message from the BizTalk MessageBox and ultimately do operations in a remote SQL Server database, all based on the contents of the XML message. The SQL adapter enables BizTalk to poll a SQL Server database via a SQL query or stored procedure to retrieve rows as one or more XML messages. Conversely, the SQL adapter can be used to insert, update, or delete rows in SQL Server tables using the SQL updategrams or by invoking predefined stored procedures.
Major Concern while dealing with SQL adapter:
a) Same Record should not be fetched again and again:
Use Record status flag which is by default 0 means record is not yet picked.
Use data Range
b) Handle the Exceptions which may occur while doing operations into a SQL table.
SQL Adapter at Send side:
1. Updategram(Insert\Update\Delete)
a. Add Generated items Add Sql Adapter
i. conn string (Server Name,DBName, Port Name)
ii. Target Namespace (very imp)
iii. Request & response document root element name
iv. Select Updategram Select (Insert\Update\Delete)
This will create two artifacts in our solution. 1. Orchestration 2. Schema
The structure of the schema generated will be different for insert, update and delete.
The key concept while using Updategram is that we have a before and after section in the updategram message that SQL Server uses to figure out what to do with the data. For instance, having a record in the before but not in the after is equal to a Delete operation in SQL Server. Also, an after record with no before equals an Insert and a record in both the before and after results in an Update.
Again in admin console we need to give the connection string and other details while configuring the SQL Send Port.
Note: To modify multiple tables, we can run the wizard multiple times and then copy the generated schemas into a single shema. However this is achievable using updategrams but we prefer stored procedures for it.
2. Stored Procedure
Same as above except in point iv we select Stored Procedure, select the spname and generate.
This will also create two artifacts one orchestration and one schema.
Advantages using Stored Procedure over updategram:
1. SELECT\INSERT\UPDATE\DELETE possible.
2. Procedure can be called to return data from a DB.
3. We can have input params to the sp but could take the entire contents of the xml message and parse the contents of the message.
SQL Adapter at Receive side:
Steps are same as above in Stored Procedure.
Since queries in SQL returns rowsets, we need a mechanism to retrieve records in xml format.
FOR XML AUTO, XMLDATA, ELEMENTS is used for that.
For xml auto returns in xml format.
Xml data is required only while running the wizard to generate the schema for the resultset from the sp. While testing we should remove this clause.
IMPORTANT: BT uses the root element name and the namespace to route the inbound xml file. If both does not match to the xsd file created, it will give routing errors.
For XML Explicit gives developer greater level of control in the resulting XML structure that is returned to BizTalk.
Adapter Configurations:
1. Poll while data found: indicates whether to continue polling for new records in additional batches or whether to submit a single query result for the entire polling window.
2. Polling interval: Identifies how often to poll for records.
3. Polling unit of measure: Identifies the unit of measure for polling.
4. connection string
5. Document Root element name
6. document Target namespace
7. SQL command
8. URI : Identifies a unique identifier for the receive location. This value is autogenerated.
I think this is enough of SQL adapter basics as far as its connectivity to BizTalk engine is considered. Lately I have come across a very good article on the same at this:
http://blogs.msdn.com/richardbpi/archive/2005/06.aspx
Sunday, August 2, 2009
A ROADIES LIFE
Life is a Roadies and it goes on. Only the players change. We meet with several people in the due course. Few faces remain with us for entire of our lives, few leave impact on us even after they leave and few change the way of our living. It’s a whirlpool of cycles. One new cycle starts from the one last ended.
Every year thousands of trainees come to Mysore, The Institution. I never knew that this place will lay a foundation of friendship which will go so far. Six strangers each with their own qualities were the part of one of the huge batches ever. The journey started in bunch of two’s Vasu and Gadoo, me and Deep, Sharma and Jaychi. Most probably I was the common link. The Hell down under was over and we all moved to Hyderabad, our next destination. Then I can never forget future plans of Gadoo to set up a own s/w firm where he projected himself and me as manager. Jaychi, Vasu and Deep will be the coding experts. And Sharma will bring tea for everyone .We always used to pull his leg. He was our favorite.
A major vote out and Gadoo, me and deep were thrown out of the Hostel but we were still in game. Niks entered with a wild card entry. On weekend’s parties at our flat and late night playing cards was the weekly routine. The gelling between me Deep and Gadoo was like sugar in a juice. The Compre heat was on and sun was at its peak but we always remained comfortable in each other’s company. And Remember the competition was on between me and Gadoo for the number of pranks we did. And believe me this was the sanjivani which always brought smile on our faces even in the gloomier moments. Whenever In my life I will think of those moments it will make my heart lighter and fill with joy. But every joy has to come to an end and it was again a time for the opt out.
One has to go and Gadoo opted himself out due to some of his other higher priorities. We were still super six but we always missed him. I learnt from Gadoo that how to get things done even in struggle time.
Then the next destination was Pune. Super six got engaged in their work, trying hard to lose their immunity. I witnessed two more wild entries Abhi and Pranab and we become entertaining Eight.
The highlights of Lilly for me will be definitely the cricket inside the house and the night we spent on the Water tank over the terrace watching the girl in next building. Of course the bike of Jaychi added an attraction to our house. But this just lasted for very brief as one more Opt out and he opted to go back to his home city. I learnt from Jaychi that how a person can be so down to earth even after getting such a genius mind.
We were sensational seven left and got shifted to a new place Vrindavan. A fully furnished house saw us playing cards till late nights. It witnessed several parties at Shivar. The glimpse of drunkards (me too...Just from the smell of liquor) will be always there in that house. It also witnessed Deepraj coming late from office and going early. Finally he lost his immunity and moved to London. I learnt from Deepraj that how dedicated you can be towards your work and remember work is worship.
Super six kept the momentum going. We played like anything let it be cricket, football or badminton. The intellectual discussions when Pranab was around were also a major highlight. But unfortunately he had to undergo a surgery and he had to leave the game in between with a hope if he can ever come back. I gained enormous intellectual and philosophical thoughts from Pranab.
But Funny fives were as enthusiastic as before. I and Niks always dream..”we will purchase this plot of land and 4 houses of four friends in each corner. And in center a glass house where we will be playing cards. Our wives would be frying snacks for us and all our funny dreams”.
Movies on weekends with Bookeazy were a major highlight of this period. Cricket in daytime and Dumsarraz in nights was a big time pass. But destiny took a U turn and two of us lost their immunities, Sharma and Nikesh. One got the much desired transfer to his home and the other much more desired onsite. I learnt from Nikesh and Sharma how to balance your work and personal life and exceed in them.
With the entry of Vikas Borse, the game moved on but not with the same energy. Weekends had become dull. I got engaged myself more in out of house affairs. Abhishek started coming late. Borse was as usual busy in his own world. I could understand the plight of Vasu.
All of a sudden three of us lost their immunities. Everyone doing what is best for him and automatically I become the winner of this Roadies, me all Immune. I learnt from Vasu,Abhi and Borse how honest one could be to his dreams.
I saw people coming and going. I learnt various things from each and every individual. Although this family no longer exists but I would say the memories will last long. I wave every one and wish them luck in their future endeavors. I am all alone now but now more stronger from inside,more philosphical and offcourse a better person...
Hopefully the dream of me and Niks will come true some day…..Aameen.
Every year thousands of trainees come to Mysore, The Institution. I never knew that this place will lay a foundation of friendship which will go so far. Six strangers each with their own qualities were the part of one of the huge batches ever. The journey started in bunch of two’s Vasu and Gadoo, me and Deep, Sharma and Jaychi. Most probably I was the common link. The Hell down under was over and we all moved to Hyderabad, our next destination. Then I can never forget future plans of Gadoo to set up a own s/w firm where he projected himself and me as manager. Jaychi, Vasu and Deep will be the coding experts. And Sharma will bring tea for everyone .We always used to pull his leg. He was our favorite.
A major vote out and Gadoo, me and deep were thrown out of the Hostel but we were still in game. Niks entered with a wild card entry. On weekend’s parties at our flat and late night playing cards was the weekly routine. The gelling between me Deep and Gadoo was like sugar in a juice. The Compre heat was on and sun was at its peak but we always remained comfortable in each other’s company. And Remember the competition was on between me and Gadoo for the number of pranks we did. And believe me this was the sanjivani which always brought smile on our faces even in the gloomier moments. Whenever In my life I will think of those moments it will make my heart lighter and fill with joy. But every joy has to come to an end and it was again a time for the opt out.
One has to go and Gadoo opted himself out due to some of his other higher priorities. We were still super six but we always missed him. I learnt from Gadoo that how to get things done even in struggle time.
Then the next destination was Pune. Super six got engaged in their work, trying hard to lose their immunity. I witnessed two more wild entries Abhi and Pranab and we become entertaining Eight.
The highlights of Lilly for me will be definitely the cricket inside the house and the night we spent on the Water tank over the terrace watching the girl in next building. Of course the bike of Jaychi added an attraction to our house. But this just lasted for very brief as one more Opt out and he opted to go back to his home city. I learnt from Jaychi that how a person can be so down to earth even after getting such a genius mind.
We were sensational seven left and got shifted to a new place Vrindavan. A fully furnished house saw us playing cards till late nights. It witnessed several parties at Shivar. The glimpse of drunkards (me too...Just from the smell of liquor) will be always there in that house. It also witnessed Deepraj coming late from office and going early. Finally he lost his immunity and moved to London. I learnt from Deepraj that how dedicated you can be towards your work and remember work is worship.
Super six kept the momentum going. We played like anything let it be cricket, football or badminton. The intellectual discussions when Pranab was around were also a major highlight. But unfortunately he had to undergo a surgery and he had to leave the game in between with a hope if he can ever come back. I gained enormous intellectual and philosophical thoughts from Pranab.
But Funny fives were as enthusiastic as before. I and Niks always dream..”we will purchase this plot of land and 4 houses of four friends in each corner. And in center a glass house where we will be playing cards. Our wives would be frying snacks for us and all our funny dreams”.
Movies on weekends with Bookeazy were a major highlight of this period. Cricket in daytime and Dumsarraz in nights was a big time pass. But destiny took a U turn and two of us lost their immunities, Sharma and Nikesh. One got the much desired transfer to his home and the other much more desired onsite. I learnt from Nikesh and Sharma how to balance your work and personal life and exceed in them.
With the entry of Vikas Borse, the game moved on but not with the same energy. Weekends had become dull. I got engaged myself more in out of house affairs. Abhishek started coming late. Borse was as usual busy in his own world. I could understand the plight of Vasu.
All of a sudden three of us lost their immunities. Everyone doing what is best for him and automatically I become the winner of this Roadies, me all Immune. I learnt from Vasu,Abhi and Borse how honest one could be to his dreams.
I saw people coming and going. I learnt various things from each and every individual. Although this family no longer exists but I would say the memories will last long. I wave every one and wish them luck in their future endeavors. I am all alone now but now more stronger from inside,more philosphical and offcourse a better person...
Hopefully the dream of me and Niks will come true some day…..Aameen.
Is Love Lethal?????????
Is Love Lethal?????????
I have heard people saying "Love is lethal", Love hurts and blah blah....... but it’s said all over the world that...God lies in love. Love is the most beautiful thing ever happened to us. So how can the most beautiful thing in world be lethal? How can it hurt someone?
I often become clueless. Then I found out something which made me realize...it’s not the love which is painful...but the feelings which supersede it are painful...
Love is as sweet as it is always....
Love to me should be unconditional. It should be free from all the selfishness except the one of finding his\her happiness in her\his happiness. I had seen such a love in their eyes for each other but they were emotional fools as they call themselves mere friends. As for me they were clearly made for each other.
I don’t know how they met but what I know is it was all destined.
Brendon Circle: A circle across which thousands of working professionals cross every day, below the hoarding of the MTV Roadies you could easily see a guy of 22 with a guitar in hands. Always busy in trying a new tune, he never even looks at the Guitar case kept for the collection of alms. But I often feel refreshing whenever I pass by that. I don’t know the reason but may be my lost love for music is the one which I killed in search of a living when I came out of my college. And this software industry buried it forever. So I see myself in him when I was of his age. He is striving hard to keep his dreams alive. I must say Human’s best asset is not that he can read write or think but is that he can dream and make it real.
And there she is Kunjal selling roses in the traffic. She is more beautiful than the roses in her hands. And her tinkling eyes convince you to buy one for your girlfriend. She does this every day before going to college not for living but for the company she gets of Himen even for a minute. Moreover she loves flowers. Her dream is to open a florist shop.
At the end of the day they go for a long walk. They discuss their dreams. They simply complement each other. But they were unknown to the love they have developed for each other. Days passed on.
One day as usual she was selling the roses. A descent personality Mr. Vikings what he calls himself came to her and offered her to buy all the roses since there was a function in his office. But for this she needs to join him till his office since alone he couldn’t handle all the flowers. But she refused politely. He insisted but she was rigid. He left the place wondering but he couldn’t help himself investigating the matter and he got to know the truth. It increased respect for the girl in his heart. One day he offered her to drop her to her college. On the way she told him everything about Himen. How great singer he is and what his inspirations are. She told him that he is her best friend and her happiness lies in his.
That person was no less. He instantly gave her idea to what she had to agree. He told her that he knows many of the corporate organizational heads. He can take permission from them and they can allow Himen to perform once in a month in their company premises. This will give him a platform to prove himself. She was very excited to hear it. She told Himen everything and convinced him.
Meanwhile she came closer to Mr. Vikings while Himen was practicing hard. He started his singing career from Pune from one company to another. He has become quite popular in corporate arena and was getting offers from other cities as well but he declined to all. Kunjal was finding herself at cloud nine. Again days passed on. One day Kunjal told Himen that she is getting married to Mr. Vikings this month 25th. He said he is quite happy for her and will cancel his entire schedule for that week. But very next morning he left for Hyderabad and had no contact with them.
The marriage day arrived. They got happily married. When they entered in the house they saw a gift lying there. She unwrapped it and was aghast to find an authority letter of a florist shop. It was on her name. It was from him. She cried “He must have spent all his earnings to buy that”. There was a piece of paper on which it was scribbled “ This is for you my eternal love…you were my inspiration for singing…I have left singing and now I sell roses in Bangalore streets…”.
She fell over from the stairs. Mr. Vikings took her to hospital but all in vain…she was dead…..but her memories are still with him…that piece of paper is still with him…He left his corporate job and continued the florist work……
“Excuse me…What is the price of this bouquet, sir?”…One man asked………….
It startled me of my dream. “Sorry”…I apologized…
“What is the price of this bouquet, sir?”…The man repeated………….
“Oh It is for Rs 250 only”…I replied. I sold that bouquet, closed the shop and left for my house with a heavy heart.
I have heard people saying "Love is lethal", Love hurts and blah blah....... but it’s said all over the world that...God lies in love. Love is the most beautiful thing ever happened to us. So how can the most beautiful thing in world be lethal? How can it hurt someone?
I often become clueless. Then I found out something which made me realize...it’s not the love which is painful...but the feelings which supersede it are painful...
Love is as sweet as it is always....
Love to me should be unconditional. It should be free from all the selfishness except the one of finding his\her happiness in her\his happiness. I had seen such a love in their eyes for each other but they were emotional fools as they call themselves mere friends. As for me they were clearly made for each other.
I don’t know how they met but what I know is it was all destined.
Brendon Circle: A circle across which thousands of working professionals cross every day, below the hoarding of the MTV Roadies you could easily see a guy of 22 with a guitar in hands. Always busy in trying a new tune, he never even looks at the Guitar case kept for the collection of alms. But I often feel refreshing whenever I pass by that. I don’t know the reason but may be my lost love for music is the one which I killed in search of a living when I came out of my college. And this software industry buried it forever. So I see myself in him when I was of his age. He is striving hard to keep his dreams alive. I must say Human’s best asset is not that he can read write or think but is that he can dream and make it real.
And there she is Kunjal selling roses in the traffic. She is more beautiful than the roses in her hands. And her tinkling eyes convince you to buy one for your girlfriend. She does this every day before going to college not for living but for the company she gets of Himen even for a minute. Moreover she loves flowers. Her dream is to open a florist shop.
At the end of the day they go for a long walk. They discuss their dreams. They simply complement each other. But they were unknown to the love they have developed for each other. Days passed on.
One day as usual she was selling the roses. A descent personality Mr. Vikings what he calls himself came to her and offered her to buy all the roses since there was a function in his office. But for this she needs to join him till his office since alone he couldn’t handle all the flowers. But she refused politely. He insisted but she was rigid. He left the place wondering but he couldn’t help himself investigating the matter and he got to know the truth. It increased respect for the girl in his heart. One day he offered her to drop her to her college. On the way she told him everything about Himen. How great singer he is and what his inspirations are. She told him that he is her best friend and her happiness lies in his.
That person was no less. He instantly gave her idea to what she had to agree. He told her that he knows many of the corporate organizational heads. He can take permission from them and they can allow Himen to perform once in a month in their company premises. This will give him a platform to prove himself. She was very excited to hear it. She told Himen everything and convinced him.
Meanwhile she came closer to Mr. Vikings while Himen was practicing hard. He started his singing career from Pune from one company to another. He has become quite popular in corporate arena and was getting offers from other cities as well but he declined to all. Kunjal was finding herself at cloud nine. Again days passed on. One day Kunjal told Himen that she is getting married to Mr. Vikings this month 25th. He said he is quite happy for her and will cancel his entire schedule for that week. But very next morning he left for Hyderabad and had no contact with them.
The marriage day arrived. They got happily married. When they entered in the house they saw a gift lying there. She unwrapped it and was aghast to find an authority letter of a florist shop. It was on her name. It was from him. She cried “He must have spent all his earnings to buy that”. There was a piece of paper on which it was scribbled “ This is for you my eternal love…you were my inspiration for singing…I have left singing and now I sell roses in Bangalore streets…”.
She fell over from the stairs. Mr. Vikings took her to hospital but all in vain…she was dead…..but her memories are still with him…that piece of paper is still with him…He left his corporate job and continued the florist work……
“Excuse me…What is the price of this bouquet, sir?”…One man asked………….
It startled me of my dream. “Sorry”…I apologized…
“What is the price of this bouquet, sir?”…The man repeated………….
“Oh It is for Rs 250 only”…I replied. I sold that bouquet, closed the shop and left for my house with a heavy heart.
Subscribe to:
Posts (Atom)