June 16, 2009 at 11:13 am
We're getting closer...
From what I understand so far you're getting EDI messages from an automotive OEM. Those messages are stored in an EDI inbox folder.
The goal is to have the messages transferred to SQL Server, details extracted an then exported as flat files in Vantage format.
What I would do is to schedule a job at SQL Server to check for existing files in the folder. If files exist, import into SQL Server, transform it and immediately export it in the new format to the destination folder. Move the processed inbox files to a different folder.
I'd be surprised if EPICOR Vantage wouldn't have an option to automatically/periodically load available orders (if provided in the right format). You definitely should look into it...
Based on that, no human interface would be required. If still necessary, you could add some email notification if manual tasks are required.
Final result: no addtl. software required. If someone needs to supervise the process I'd add a Report using SSRS.
June 16, 2009 at 12:02 pm
lmu92 (6/16/2009)
We're getting closer...From what I understand so far you're getting EDI messages from an automotive OEM. Those messages are stored in an EDI inbox folder.
The goal is to have the messages transferred to SQL Server, details extracted an then exported as flat files in Vantage format.
What I would do is to schedule a job at SQL Server to check for existing files in the folder. If files exist, import into SQL Server, transform it and immediately export it in the new format to the destination folder. Move the processed inbox files to a different folder.
I'd be surprised if EPICOR Vantage wouldn't have an option to automatically/periodically load available orders (if provided in the right format). You definitely should look into it...
Based on that, no human interface would be required. If still necessary, you could add some email notification if manual tasks are required.
Final result: no addtl. software required. If someone needs to supervise the process I'd add a Report using SSRS.
I thank you for your advice. I would love to have no human interaction and to have SQL server perform everything but to my knowledge I don't think SQL server has the capability to do all. Some one can correct me if I am wrong:ermm:
What I did was I created a mapping using a mapping software and I generated an executable. This executable will be technically executed by the application that I am developing to map the EDI content to SQL server. I don't think SQL server has the ability to do the mapping itself, also a job does not have the ability to tell whether an EDI file is from GM or Chrysler or others. If I am understanding this wrong please explain to me in detail:-)
also our Vantage software does not have automatic loading. It is fairly old and we paid for the extended version of EDI part which allows us to import files manually.
June 16, 2009 at 1:48 pm
Is it possible to call the mapping software from a command line with input file and target file as parameter? Or start it from command line to process a batch of files stored a folder?
Afaik it's possible with Altova MapForce to start via command line...
The main goal should be to work as independent as possible from human interactions as stated before.
Your statement that SQL Server wouldn't be able to do all is basically correct, but SQL Server can "call for assistance" (e.g. by calling external programs for EDI mapping). Even if it would also be possible to write a proc that converts the EDI data... But why reinvent the wheel?
Regarding your question on how SQL Server would be able to tell the difference between a GM file or a Chrysler file:
The job could load the EDI file into a variable and check the string or text for occurrence of a certain pattern.
Instead of doing the required tasks using some T-SQL you could also run .NET code (stored as CLR) from SQL Server, started by a job or ServiceBroker task.
The tools available in SQL Server let you do almost everything to automate a process...
If complete automation is not an option you could send an email from SQL Server to notify somebody about arrival of new (transformed) orders (including some escalation levels after a certain time).
Over all, sounds like a fun project to work on!
June 18, 2009 at 7:24 am
lmu92 (6/16/2009)
Is it possible to call the mapping software from a command line with input file and target file as parameter? Or start it from command line to process a batch of files stored a folder?Afaik it's possible with Altova MapForce to start via command line...
The main goal should be to work as independent as possible from human interactions as stated before.
Your statement that SQL Server wouldn't be able to do all is basically correct, but SQL Server can "call for assistance" (e.g. by calling external programs for EDI mapping). Even if it would also be possible to write a proc that converts the EDI data... But why reinvent the wheel?
Regarding your question on how SQL Server would be able to tell the difference between a GM file or a Chrysler file:
The job could load the EDI file into a variable and check the string or text for occurrence of a certain pattern.
Instead of doing the required tasks using some T-SQL you could also run .NET code (stored as CLR) from SQL Server, started by a job or ServiceBroker task.
The tools available in SQL Server let you do almost everything to automate a process...
If complete automation is not an option you could send an email from SQL Server to notify somebody about arrival of new (transformed) orders (including some escalation levels after a certain time).
Over all, sounds like a fun project to work on!
This is a fun project indeed. Thanks for your clarity and advice. the mapping software or executable can be called through command line yes.
I would like to know:
- how can SQL server call another program for assistance? by using what mechanism?
- how can SQL server job load EDI file into a variable and check for a string?
We are using SQL server 2000 and I tried to enable CLR integration but it gave me an error saying that it is not available or part of a more advanced options. Is CLR integration only available for SQL server 2005 and above?:doze:
I found out that CLR Integration only available on SQL server 2005 and above? is that correct? is there a patch or an add-on to SQL server that allows me to use CLR Integration?:blink:
thanks
June 18, 2009 at 1:35 pm
To add CLR to SQL Server you need to have SS2005 and up.
If you have the option to go for SS2005 I strongly recommend to decide it BEFORE you start to work out the process details or even implementation since SQL2005 has a lot more options to choose from and is "a bit different" to use (e.g. DTS packages are replace by SSIS packages to name just one of the major changes - and upgrading those packages isn't always a "piece of cake"). Upgrading in the middle of that kind of a project won't be fun at all...
To run an external program: use xp_cmdshell.
Please look it up on BOL (BooksOnLine = Help file installed with SQL Server, accessible via {F1}) and/or check the following link for an example http://www.sqlservercentral.com/articles/Administration/xpcmdshell/141/.
When running xp_cmdshell on your server I strongly recommend you get familiar with the security issues involved with it (search for "xp_cmdshell security" in this forum).
I'm not saying that you'd run an "extreme risky process" (assuming your SQL Server won't have a connection to the internet) nor would I say "nothing to worry about". All I'm saying is: you need to know how to protect your system if you'll use xp_cmdshell.
To load flat files into SQL Server with SQL2000
http://www.sqlservercentral.com/articles/DTS/data_import_functionality_using/832/
The last article should get you an idea on how to handle file import including verification and scheduling. I think this article is a very good start and really descriptive.
If you don't feel comfortable with SQL Server you might want to consider a consultant to get you started or at least ask your boss for some external training. From my perspective doing a project like this as a "first contact with SQL Server" doesn't seem to be the most efficient way...
June 19, 2009 at 11:09 am
lmu92 (6/18/2009)
To add CLR to SQL Server you need to have SS2005 and up.If you have the option to go for SS2005 I strongly recommend to decide it BEFORE you start to work out the process details or even implementation since SQL2005 has a lot more options to choose from and is "a bit different" to use (e.g. DTS packages are replace by SSIS packages to name just one of the major changes - and upgrading those packages isn't always a "piece of cake"). Upgrading in the middle of that kind of a project won't be fun at all...
To run an external program: use xp_cmdshell.
Please look it up on BOL (BooksOnLine = Help file installed with SQL Server, accessible via {F1}) and/or check the following link for an example http://www.sqlservercentral.com/articles/Administration/xpcmdshell/141/.
When running xp_cmdshell on your server I strongly recommend you get familiar with the security issues involved with it (search for "xp_cmdshell security" in this forum).
I'm not saying that you'd run an "extreme risky process" (assuming your SQL Server won't have a connection to the internet) nor would I say "nothing to worry about". All I'm saying is: you need to know how to protect your system if you'll use xp_cmdshell.
To load flat files into SQL Server with SQL2000
http://www.sqlservercentral.com/articles/DTS/data_import_functionality_using/832/
The last article should get you an idea on how to handle file import including verification and scheduling. I think this article is a very good start and really descriptive.
If you don't feel comfortable with SQL Server you might want to consider a consultant to get you started or at least ask your boss for some external training. From my perspective doing a project like this as a "first contact with SQL Server" doesn't seem to be the most efficient way...
Thanks for your help and time.:-)
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply