SSIS Flat File Connection Manager

  • I am trying to automate the move of data in .txt files to staging tables in Sql. I have been all over the internet and have read many questions and answers on Sql Server Central and have not found a solution.

    When going through a flat file connection manager you always have to reset the columns, or so it seems. Can anyone suggest a work around for this problem? Actually I don't want to touch the connection manager once it is set up.

  • jamesl 88576 (3/11/2013)


    I am trying to automate the move of data in .txt files to staging tables in Sql. I have been all over the internet and have read many questions and answers on Sql Server Central and have not found a solution.

    When going through a flat file connection manager you always have to reset the columns, or so it seems. Can anyone suggest a work around for this problem? Actually I don't want to touch the connection manager once it is set up.

    I am not sure what you mean by "always have to reset the columns." As long as the format of the incoming files are the same then you do not have to do anything except change where the connection manager is pointing, i.e. change the path to point to the new file. If the file format changes then yes, you have to change the Connection Manager to be able to process the new file format.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Sorry, I should have been a little more clear. And thank you very much for answering. Yes you are right you do not always have to reset the columns. I guess I am getting a little exasperated with this reset columns thing.

    I get .txt files from a lot of different companies that are supposed to be the same and many are. We get them pipe delimited with the same headers. Once in a while I have to edit a header to match exactly our requested format.

    Can you tell me what constitutes a format change? I will do some more research online concerning format change but if you have any links or insight I would appreciate it.

    I have been working with SSIS, if you total all my time, about 1 to 1.5 years but I am just now having to do some higher automaton. I have looked at possibly creating a file format with bcp and using sql server to do these uploads but I don't really want to go that route. I would prefer to keep it in SSIS because there are other things in SSIS I want to use in my final outcome.

  • Can you tell me what constitutes a format change?

    Pretty much any structural change to the file. One thing to keep in mind though is that you do not need to use the Reset Columns button. In fact, I never do. For minor tweaks after the Connection is in place I use the Advanced Page to add or remove columns, or change a data type or width. I find it much simpler than pressing Reset but your experience may differ.

    I have been working with SSIS, if you total all my time, about 1 to 1.5 years but I am just now having to do some higher automaton. I have looked at possibly creating a file format with bcp and using sql server to do these uploads but I don't really want to go that route. I would prefer to keep it in SSIS because there are other things in SSIS I want to use in my final outcome.

    I too prefer SSIS. I still use bcp for quick and dirty exports and imports where SSIS is not an option, or for archiving data to a file before dropping what I think are unused staging or archive tables (e.g. SomeCoreTable_20120125, I run across those a lot) but for every day use I prefer SSIS.

    For the record bcp is just as strict as SSIS when it comes to the incoming file format yet it is nowhere near as robust or flexible for what you are doing as SSIS so in my opinion it won't buy you much. I would create the set of SSIS packages needed to process the various file formats you have coming in and just deal with the fixups on the files themselves manually as you have been. Once you have an SSIS package for each flavor of file you receive you can start running the packages from the command line and passing in the locaiton of the file as a variable value, in case that helps you streamline the process from the command line to get closer to a bcp-like experience.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Once again thanks. That helps. I cannot use the Advanced Page because some times we get multiple files from the same client or we do some semiautomated editing that breaks the file down into multiple files because something is different on a row of data. I am using a foreach loop container with a lot of expressions to control things so at least I know now a little more about what to do and where to head. That foreach loop containter takes over the connection managers connections. So in order to reset the columns I have to browse to the file again. Looks like I am going to have to work with my customers to get the incoming files more standardized.

    Thanks again.

  • Looks like I am going to have to work with my customers to get the incoming files more standardized.

    Honestly, I think that is a great next step.

    Thanks again.

    Anytime.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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