SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Flat File Connection Manager


SSIS Flat File Connection Manager

Author
Message
jamesl 88576
jamesl 88576
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 182
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39410 Visits: 14411
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
jamesl 88576
jamesl 88576
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 182
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39410 Visits: 14411
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
jamesl 88576
jamesl 88576
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 182
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39410 Visits: 14411
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search