March 29, 2016 at 3:56 am
Hi,
I wondered if someone could point me in the right direction with something.
What we are trying to do is create an SSIS package which will have dynamic column mappings, why? well we would like to have a staging table which can be populated by multiple different provider types.
By that I mean imagine we have a staging table with 10 columns, provider 1 provides a file which populates 7 of the columns but they don't use the other columns but provider 2 uses a different 5 columns, and provider 3 they pass them all in.
So the solution needs to be something that says "oh its provider 1 so that column goes there and that one there etc" but another time goes "oh now its provider 2, so lets map that to there and that to there etc".
Now I'll be the first to admit SSIS is not my strongest point, I've done some digging around on the information super highway but not found any good(clear) example of how to do this.
I wondered if someone could point me in the right direction.
Any help would be appreciated. If you need more information then let me know.
Thanks,
Nic
P.S - Sorry if this post is in the wrong place, I looked around for an SSIS section but couldn't find it.
March 29, 2016 at 9:12 am
It's not really dynamic since you know which columns each provider needs to populate. This can easily be done via SSIS. You just need to create a separate Data Flow for each provider/file type.
March 29, 2016 at 9:39 am
Hi,
The problem is that we want to be able to map new files without having to change the SSIS but instead do it via data mapping tables.
Kind of like for provider 1 map column 1 to this and column 2 to that. So when provide 99 comes along we can just add the mapping rows in and run the packages.
Thanks,
Nic
March 29, 2016 at 10:53 am
You're going to have a difficult time with that in SSIS. SSIS is extremely metadata sensitive and must be mapped directly to both the source and target objects. There's quite a bit of dynamic things you can do inside of SSIS with loops, variables, and expressions, but one thing that must be 'manually', done each time is source-to-target mappings. SSIS is able to handle moving data through a data flow pipeline and take advantage of the memory buffers efficiently because we've told it all about the source and target at development time. Trying to make this dynamic goes against what a data flow is meant to do.
There are some third party tools that claim to be dynamic, but I've never used them so I can't recommend them. Outside of that, you'd have to do this in a script task or sql task in SSIS (both of which would negate the use of the pipeline & buffers because they would need one in the control flow as opposed to a data flow). You may be better off trying to do this using a stored procedure or bcp or somewhere besides SSIS.
March 29, 2016 at 11:05 am
I agree with John,however there is a third party tool from cozyrock that has various flavours of DFT tasks.
March 30, 2016 at 3:58 am
Hi,
Thanks for the replies, I think your right, doing this in a data flow task may be more trouble than its worth, I'm leaning now to instead having an SSIS package which calls procedures to do the import, or something BCP.
Either way fun times ahead for me.
Thanks,
Nic
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply