December 4, 2014 at 9:47 am
I'm an SSIS newbie but I'm not new to SQL Server/SSRS, etc. I found a couple of answers to my initial question about whether I have to create a separate flat file connection manager for each of the files (I do). I was excited at first when I found the MSDN page describing the multiflatfile connection type but I soon realized that it's just for multiple files with same format.
My goal is to automate importing multiple flat files of different formats into the corresponding tables in the database. If I set up the 90 connection managers, 90 flat file sources, and 90 OLE DB destinations they will all run in one package. I've started intensively studying to bring myself up to speed on SSIS but, because I'd like to move along my setup of this package, I'm hoping I can get some advice as to whether there's a better way to do this.
If after all I do need to set up the 90, if you were me, would you just make 90 copies of the first data flow and then adjust for each different flat file and database table or would you just create each one de novo so as not to run into hinky problems? I ask because I started doing it the copying way but I seemed to run into mysterious (to me) problems that took me a long time to straighten out. For one thing, it seems hard to change a column(field) length after it's already set up. It keeps changing back to whtever it was when I set up the original data flow.
Also, are there any drawbacks to putting all 9o0 in the same package?
Thanks for reading this lengthy post.
P.S. one of the reasons I had initially thought that one connection manager could be used for all is because of my experience with SSRS where you hjave one data source with the possibility of multiple datasets using the same data source.
December 4, 2014 at 11:27 am
If you're really getting 90 files all in different formats then yes you would need to create 90 different flat file connects, how else is SSIS supposed to know how to parse each file type?
Now if you're getting groups of files in the same format you can use a for each container to parse all the files of the same type and pass them through a data flow to the target.
You wouldn't necessarily need to make a separate OLE DB connection for each file but you would need one for each different DB you needed to connect to and you'd need a separate dataflow for each file type.
As far as putting them all in one package that just sounds like a nightmare to try to maintain and manage if there's a logical way to break them out into separate packages.
December 4, 2014 at 3:13 pm
Thanks, I appreciate your guidance. I just wanted to make sure I didn't go ahead and set the 90 up laboriously when there might be a more elegant way. I realized I had to "tell" SSIS what all the data types and column names are regardless of method but thought there might be a way to avoid all the connection managers, flat files source and OLEDB destinations. Actually I got on a roll today and got them all done so it wasn't as bad as I thought it would be.
I'm looking forward to learning more about SSIS as I go ( so I can do a lot more than just move data from flat files to database tables) using some web courses, books, and the 31 days of SSIS by Jason Strate of StrateSQL.
December 5, 2014 at 1:56 pm
pharmkittie (12/4/2014)
Also, are there any drawbacks to putting all 9o0 in the same package?
Definately. It's called Production Failures. 😉
If one of those flat files fouls up, you don't have independent restarts of a particular job after you get a new copy from wherever it came from.
In general, I only put different files into the same package when they are reliant on each other and I need assurance that a (for example) foreign key table is filled before a data table. That's not because SSIS can't happily multi-task and process a huge load of files, but because inevitably at 2 in the morning I want to be able to walk my admins through:
1) Get new file from client and restart the job.
instead of:
1) RDC to the machine, fire up BIDS/VS
2) Open the package
3) Select all, disable all.
4) Which file again? Alright, find these 5 objects and enable them.
5) Get new file from client
6) Manually run package with changes.
7) Close file without saving so you don't blow up the next guy.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 5, 2014 at 3:57 pm
Great point. We are a small operation so far so it would be me solving the problem at 2AM. As this is my first use of SSIS, I was just experimenting so I could rerun all over and over to debug but, when I got it working I truncated all the tables and then did a final run. Next step is to set up the keys so duplicates won't be imported and define relationships between some of the tables to group tasks in a package or run solo if not related. I'll be doing transformations, ftping the files, etc. as I develop this to be a fully automated system.
Because the IT team had been doing almost everything by hand using Excel and Access (no VBA or macros even) I thought I would get this going to show them how to take the manual drudgery out of their job. They'd never heard of SSIS so I have some time to learn as I go to make a data warehouse with nightly automatic report data refresh.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply