June 13, 2011 at 8:04 am
Hi,
I am working on a SSIS project. It is having several control flows as well as the data flows. But, for every change of a column (add/change/delete) in the database table I have to rebuild the complete project again. It happened several times. Is there any way to re adjust the project for the single change in the database. Thank you for your help.
June 13, 2011 at 8:21 am
I haven't tried, but maybe you could use a view for the source of your data.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2011 at 8:31 am
Thank you for the response. I am working on the existing database. Whenever the database developer add a new column, my project is giving errors and I have to redo all the steps. Please let me knwo if there is any other way for this issue.
June 13, 2011 at 8:35 am
Did you try my suggestion - if so, did it fail? What error did you get?
Otherwise, please explain your reasons for not wanting to try.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2011 at 9:16 am
As i have mentioned it is an existing database. I can't change at my end to a view. Thank you for your response.
June 13, 2011 at 6:45 pm
Well if the people modifying your database are changing column names/data types or deleting columns then you would need to update your SSIS package wherever that column is referenced. For example if someone deletes a column from a table you use in your package SSIS has no way of knowing how that column should be mapped in a dataflow.
June 14, 2011 at 6:40 am
To help prevent column additions from messing you up on the source side, you can specify the columns to pull in your select statement rather than running a SELECT * FROM <table>. This will help two-fold. 1) You don't have new columns showing up in the table messing up the metadata on your source connections. 2) It makes the package more efficient as it's only loading the data you want to move or transform, etc.
The above will help with deletions as well, unless the column being deleted was one that you were using.
I know this doesn't fully answer your question, but it may help reduce the number of times you have to rebuild.
June 14, 2011 at 11:30 am
I'm a little fuzzy why the tables are changing so much..
Usually it shouldn't require a rebuild, you might have to visit each component but it shouldn't make you rebuild.
I think you have a larger problem, lack of design work upfront, I'm thinking your people need to step back from the keyboards and think about the design before they actually CODE it. I have to admit I have been guilty of this as well and actually design what I want. Another common error is inconsistent use of names, such as FieldNameId is always an integer (or small int, or big int, etc.) type or inconsistent naming. That design time saves time overall because you are cutting out much of the rework that you have to do. You know, kind of like what you are reporting here..
CEWII
June 14, 2011 at 12:03 pm
Well, There is no way out unless you define and take some steps...
1) Use SQL Query with required fields only instead of table in source component
2) If there is a change going on in destination table, then you have to update references manually each time.
as far as rebuild concerns, that depend on what are you doing with SSIS project. Build is required before deploy 🙂
Thanks,
Nimesh
June 14, 2011 at 12:44 pm
You are right. Thank you for the advice.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply