Dynamic Column Names
-
I need to create a package that is quite dynamic in the most efficient manner I can, I can cope with most of the processing I need to do on the different data that will be in the files but I need to know if certain things can be done in SSIS, I've tried searching but I've so far found very little on the subject.
I want to:
1) Select a file and client name from a front end, the file and client name will then be fed into a variable and then use the variable file name in SSIS to process that file, the client name will tell me what input and output file type are to be used.
2) Use the Client variable to tell me which columns are input from a lookup table which will then map to that clients output file.
3) The processing and mapping of this data will depend on the input file so I will have a database that will hold date format information etc.
Now, the selecting file names and setting variables I can do, it's the using only one data source and destination with dynamic column definitions held in a database I don't know if it can be done or if I will just have to settle for a seperate data flow for each client/file type.
I know the seperate data flow for each file would be easier but it also means that if any changes are made that the SSIS package would need to be amended and re-deployed rather than just changing a mapping in a SQL database if say a file type or column name changed.
Ask me any questions about this if you are unsure, any suggestions would be great, I've only done a couple of things in SSIS before so am unsure about some parts of it and how flexible and how far I can take it so any pointers would be good, I've been watching some videos by Brian Knight and Tim Mitchell which were very useful which have answered most of my questions and I have Brians book so if either of you are on here, thanks for the videos.
Thanks
-
If you can use third-party components, I would suggest you check the commercial CozyRoc Data Flow Task Plus. It is enhanced to support dynamic data flows. You can specify a source and destination to be dynamic. Then you can use external mapping list to connect source to destination columns. Do not hesitate to contact us if you need our assistance.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Sorry, that's not really an option for us to buy in third party tools, we don't do enough SSIS development to warrent this, any other ideas anyone?
-
Has anyone got any other oppinions on this?
Thanks
BU69
-
Did you get a resolution. I also ran into same issue. Can't we create DFT and use Script component (Tranformation) between Source and Destination and Source and Destination will get the table name from variable.
-
If you want to use a dataflow component and you want it to be dynamic in terms of field names / types, you'll not find a way using the standard SSIS tools.
You may be able to write some complex code which builds your package dynamically (note that this is different from programmatically modifying a package at run-time), but IMO you'd be better off going with a third-party component, such as was mentioned by Cozyroc.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
viveksh47 (11/29/2009)
Did you get a resolution. I also ran into same issue. Can't we create DFT and use Script component (Tranformation) between Source and Destination and Source and Destination will get the table name from variable.No, only thr third party solution which isn't really acceptable for me as it's a small project with no budget so I'll just have to do it another way and have several processes set up for each different file type.
Thanks anyway
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply