Post reply

Dynamically mapping to destination filename for flat file in SSIS Export SQL 2005

  • June 18, 2008 at 11:56 pm

    #154461

    Hi all.

    I suspect that there is a pretty easy answer for this, I'm just not so well-versed in SSIS to know how to do it, so I'm hoping someone out there has the answer.

    I have to process about 10 batch files to flat file .txt files each day from a couple tables in our SQL2005 Server. Each of these needs to have its own unique name when output, and I would like to include the filename for the flat file as a variable in the SSIS export package itself.

    I can run a query to get the name from the table I'm actually outputting from, so that part isn't hard, and I can see from the way this stuff works, I probably just have to assign this query result as a variable in the SSIS package, and then instruct the package to output to this destination. It will always be the same folder I will output to, so it will merely be the filename I have to change on the end of that path.

    Can anyone tell me how to accomplish this?

  • June 19, 2008 at 12:01 am

    #830895

    Does this article help?: http://www.sqlservercentral.com/articles/SSIS/63152/

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 19, 2008 at 12:10 am

    #830898

    Hi.

    I've already seen that article, and that was the original concept I was going for.

    I'm assuming I can setup a "filname" variable using this method, but then I'm not sure how to tell my SSIS Package to output to that variable as a path/filename once I have done that.

    I think perhaps I need to re-read this, as it looks like it does just that at the end, and I just wasn't quite getting all the way there.

    Thanks for pointing me back to it, I'll keep going down that path...

  • June 19, 2008 at 12:39 am

    #830913

    Hey Sharon,

    You are definitely on the right track. Use the concepts of this article.

    Basically, your filename variable you will add to the expressions section of the flatfile connection in the connection manager (property - connectionstring).

    Look at how the article approaches loops - your will require a for each ado enumerator, where you tie the outputs of your object variable to the filename variable.

    TADA

    ~PD

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

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