Post reply

Trigger based on .cmd file

  • January 5, 2012 at 2:06 pm

    #254294

    Dear Experts,

    I am working on a BI project in which data base is SQL 2008 and the front had is a SAP BI tool. In order to automate the data flow, I have created a SSIS package. But to further automate this processes, the client has asked me to create a trigger. This trigger will identify the new data in Data Source (Flat File) and then trigger the SSIS package.

    Client want me to design a trigger based on .cmd file. Now I am not familiar with .cmd file. Kindly guide me on following

    1 - What is .cmd file.

    2 - What script/command I need to write to trigger the package

    3 - Where do in need to save this file

    4 - Any additional information required for me to complete this task.

    Regards,

    Rahul

  • January 6, 2012 at 6:48 am

    #1429599

    Can anyone help me on this one.

    Thanks,

    Rahul

  • January 6, 2012 at 7:21 am

    #1429616

    well, first, a .cmd file is just the renamed verison of a .bat file...something you might typically call via command line / xp_CmdShell (if that is even enabled)

    that .cmd file is probably being used, per your description to run an executable which will perform some data massaging or something.

    a good rule of thumb is that you NEVER call an outside process within a trigger...the trigger will wait and block inserts/updates until that outside process completes. If that process, say pops up a messagebox (which can never be clicked via a command line) or otherwise errors, the trigger will either hang on the process to complete until you kill it, or maybe even rollback the insert/update, and you are left wondering why the data got lost.

    did i mention to never call an outside process from within a trigger?

    in this case,what you want to do is create a service broker which performs that .cmd call, and call the service broekr within the trigger instead.

    the service broker is asyncronous...so it returns immediately, and the work is done outside of the trigger context.

    judt to be sure, did i mention to never call an outside process from within a trigger?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply