August 9, 2009 at 9:25 am
I am trying to copy some rows from a table that the columns are not know during developement time.
I used a table 'Tranfer SQL Server Object Task' to synchronze the table structure.
Also I could create a statement that loads the new fields at runtime.
The problem is how to map the unknown columns to a destination task.
Does anybody know the way to do it ?
Any help would be appreciated.
August 10, 2009 at 6:38 am
I hate to be the bearer of bad news, but once you go down the road of what effectively amounts to "dynamic SQL", almost everything you do with the results that are created, must, of necessity, be handled by dynamic SQL. This means that SSIS tasks which rely on a specific object, are not usable, as they can't operate without the details.
There can sometimes be alternatives, if you can live with some potentially very difficult to live with limitations. I've seen folks create what I'll call a "generic table", where the fields are named along the lines of CHARFLD1, CHARFL2, VCHARFLD1, VCHARFLD2, DTFLD1, DTFLD2, INTFLD1, INTFL2, etc., etc...
There are several potentially VERT difficult problems - one, how does one consistently map a given input field to a given output field. In many cases, this approach becomes so unwieldy that it's not worth bothering with. Another approach often tried is to create a table with EVERY possible field that could ever end up in the results, and the default value for all fields is NULL. Even that approach can be painful, and either of these has serious potential for performance problems under any sizable number of records, and additionally, there's the problem of what indexes to create. That can be a nightmare all to itself because there could be scenarios where the table needs a clustered index on one field for one scenario, and on another field for a 2nd scenario. These are the reasons why it's usually more practical to do everything necessary within the dynamic SQL.
Of course, if it's possible to avoid the dynamic SQL in the first place, that would be the ideal, but it seems that's not likely given your input so far. If you can provide a lot more detail about what you're trying to accomplish, there's always a chance that dynamic SQL might not be necessary - no guarantees, of course.
Do try to be detailed and completely inclusive with info about what the ultimate objective is. Things we don't know about your specific situation could easily send us in the wrong direction.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply