Hello All, I have a question relating to the variables in SSIS. I have an existing etl package with data flow components. One of the column is System_id to identify the Source System, the package is pulling the data from. I plan to create a component which will validate the data based on the Rules stored in the database. These rules will be accessed based on the System_id + Target combination which needs to be set by the user at design time when the component is dropped. In design time, i want to access this System_id field for the Custom data flow component so that i can access the database table and display the available Targets for this System_id which the user can set so that i can run the rules against this data. I understand that i cannot access the value of the data flow at design time. My question is there other way, where i can set the variable value and access it at design time ? Please let me know thanks rk

did you created a derived column transformation based on this variable and fill it in table? if yes, you can simply use DATA VIEWER to see records which exists in that data path in runtime. for this, right click on every data path you want ( green or red arrow between different components in data flow) and select data viewers, then add a grid with default setting. http://www.rad.pasfu.com

Sounds to me like you want to build a custom task with its own design-time dialogs. I haven't attempted this myself, have only heard that some degree of custom tasks is supported, but if you want a custom setup dialog as part of it you may have to do some research, at least, to figure out how to accomplish it. Josh

You can definintely read variables in a custom component at design time, however: I'm confused by your reference to both variables and "fields" - are you still referring to variables when you say fields? Or are you referring to data flow columns or table columns? You start out by wanting to know if you can read variables, but then at the end, you talk about setting variables - which is it? It's unusual for a custom component to require the existence of specifically-named variables in a package. It's much more common (and robust, and flexible) to have the custom component present properties that the package designer can populate at design-time from the variables they have available. Even if the existence of those variables is mandated by your ETL framework (your "best practice") - it's still advisable not to require specifically named variables. It's traditional in data flow component development to "delay" the validation steps until the user has had an opportunity to set up the component as they want to. Is it not possible to present the user with a UI that requests these two properties, then have your component fetch what information it needs to? You can fairly easily code a custom UI for your component so you can architect your component editor as a "step by step" process. Talk to me now on

Thanks everyone for your replies. Todd, i want to build a custom component which will be dropped into the existing packages before data goes to the Target/destination. This component will apply the rules that would be defined for the Target fields. When this component will be put in the package, i believe the user will need to 1) Enter the database name this data is going - (i prefer populating it and the user selecting it instead of typing it in) 2) Enter the table name where the data is going We do have a system_id for all our packages which is part of the data pipeline. What we plan to do is based on the System_id we wanted to populate these values for the user. My question is can we have a one-time custom UI component on the Custom Data flow component which can take these values? and also can these values be changed in the future? Please let me know...if you can point me to any articles that would be great...thanks again for your response.

You should be able to do that - but again, I'd caution relying on that variable being there. What you should be able to do is retrieve the value of that variable in your editor, populating the lists that the user has available to choose your other settings (db name, table). This UI doesn't need to be "one-time". You should be able to use the same UI for the first edit, and all subsequent edits, shouldn't you? If you want to see what custom UIs look like, there are tons of samples on CodePlex. Check out the SSIS Community Tasks and Components project on CodePlex for a pretty complete listing. Talk to me now on