November 11, 2014 at 8:12 am
I have a series of flatfiles that I need to import into my database. These files have a name format prefix1_Entity. There are a series of tables already in the database with the name format prefix2_Entity and the data needs to end up in these tables one way or another. I'm currently doing them manually using Tasks >> Import and choosing the destination myself. I then drop the existing table and rename the imported table. At the moment this is ok because there's fewer than twenty flatfiles. I've got an SSIS package that I can use to bring the flatfiles across but I'm not sure how I'd go about going about the re-locating\re-naming of the tables using that. I've written a cursor (my first one :crying: ) to go through the files imported using the package and move the data. This also uses dynamic SQL (my first attempt) so I'm really not sure that my approach is the best way of doing it. To further muddy the waters the flatfiles may not have the same columns that the tables have so I've included something in the cursor to account for this. Does anybody have any suggestions about better ways of doing this or where to go to read up on the arcane depths of SSIS needed to do it all there?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
November 11, 2014 at 9:14 am
BWFC (11/11/2014)
I have a series of flatfiles that I need to import into my database. These files have a name format prefix1_Entity. There are a series of tables already in the database with the name format prefix2_Entity and the data needs to end up in these tables one way or another. I'm currently doing them manually using Tasks >> Import and choosing the destination myself. I then drop the existing table and rename the imported table. At the moment this is ok because there's fewer than twenty flatfiles. I've got an SSIS package that I can use to bring the flatfiles across but I'm not sure how I'd go about going about the re-locating\re-naming of the tables using that. I've written a cursor (my first one :crying: ) to go through the files imported using the package and move the data. This also uses dynamic SQL (my first attempt) so I'm really not sure that my approach is the best way of doing it. To further muddy the waters the flatfiles may not have the same columns that the tables have so I've included something in the cursor to account for this. Does anybody have any suggestions about better ways of doing this or where to go to read up on the arcane depths of SSIS needed to do it all there?
SSIS uses column source and destination mappings to resolve these issues for you. You really need to have a good read up on the data flow task and the various data flow source\destination objects.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 13, 2014 at 2:37 am
Thanks Perry, I spent most of yesterday looking into the column source and destination mappings as you suggested but I'm still struggling. From what I found, I got the feeling that importing into dynamic destinations wasn't particularly straightforward. I don't think my original post was clear about the destination tables either. The tables have totally different column structures from entity to entity and I think this really complicates things with the dynamic destinations. It's one thing when a flatfile and the corresponding table have the same number of columns and the same datatypes across these columns but only the column names differ but when entity 1 is squid and its associated columns and entity two is Lady Gaga and the associated columns it gets very difficult. I got the distinct impression that it wasn't something that SSIS does out of the box but can be done with script tasks.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply