Post reply

SSIS Recommendation for Data Transfer

  • June 8, 2015 at 11:41 am

    #297589

    Hello,

    I have been asked to create a routine that will send data created by a SQL Query to a Shared location filer. I have a couple of thoughts for doing this with SSIS, however, I am thinking there might be a way to do this that is more standard.

    I want to generate this data one-time per week and overwrite the prior file.

    Thanks

  • June 8, 2015 at 11:46 am

    #1803761

    If I were doing this, where I already had SSIS packages running, I wouldn't waste my time looking for other possibilities. I'd do it in SSIS.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 8, 2015 at 12:03 pm

    #1803771

    Was your question about whether to use SSIS or not? Or was it about which technique to use within SSIS?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 8, 2015 at 12:07 pm

    #1803772

    Thanks Phil,

    I was just responding with that. I was wondering if anyone had thoughts with regard to the best method using SSIS.

  • June 9, 2015 at 12:48 am

    #1803879

    mbrady5 (6/8/2015)

    Thanks Phil,

    I was just responding with that. I was wondering if anyone had thoughts with regard to the best method using SSIS.

    I'm not sure what a 'shared location filer' is. If you mean a network share somewhere, here is how I would do it. I make no claim that it is the best, but it does the job.

    1) Create a stored proc which outputs the results you need.

    2) Create an SSIS package and add a dataflow.

    3) The DF source should be the stored proc.

    4) The DF destination should be a flat file on the SSIS server.

    5) The final task is a script task or file system task to copy the file from the SSIS server to the network share and, optionally, to an archive folder somewhere (if you don't trust the people who will be accessing the file on the network share :-))

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 9, 2015 at 7:35 am

    #1803949

    Thanks. This is what I was thinking and your response just confirmed.

Viewing 6 posts - 1 through 5 (of 5 total)

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