tiffanyb (6/24/2013)
HiI currently have text files sent monthly that need to be imported into SQL server. I'm new to SSIS and have fumbled my way through importing each one using a data flow task for each file (flat file source and OLE DB Destination). Within my OLE DB Destination data flow, I've set the name of the table to match my import file, however, I'm wondering if there is a way to add the date of the file to the end of it. For example, one text file is called Branch. Is it possible to name the SQL table 'Branch_20130630' or something of the like?
Also, when I set the table name within the OLE DB Destination, is there an option that overwrites the table should it be there already? (in the case of incomplete data that would need to be sent/reloaded)
Thanks so much!
Tiffany
I'm not 100% sure I understand your questions, but here goes:
1) Is there is a way to add the date of the file to the end of it?
I presume that the file name changes every month and you don't want to keep editing the SSIS package to accommodate that?
2) Is there an option that overwrites the table should it be there already?
Are you creating a new table for every file that you import? This would usually be considered bad practice - better to put all of the data in the same table and include extra column(s) to help you identify the source of the data (eg, file name)
There is no such option, however, it's easy enough to achieve the same outcome. Before your dataflow task, add an ExecuteSQL task which does a truncate on the table.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.