September 14, 2016 at 8:31 am
i created ssis package that load data from source to destination.
i have 4 columns in source and destination.
now they are adding new column every month.
i want my ssis package to add this functionality automatically,without changing my package automatically.
how to do it in ssis
September 14, 2016 at 8:46 am
coool_sweet (9/14/2016)
i created ssis package that load data from source to destination.i have 4 columns in source and destination.
now they are adding new column every month.
i want my ssis package to add this functionality automatically,without changing my package automatically.
how to do it in ssis
Adding new columns to a table every month sounds like a bad design decision. Any chance you can go back and suggest a better approach to monthly data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2016 at 8:49 am
SSIS can't dynamically pick up new columns. You have to add the column manually and update the package. there's no other option.
AS above, why are you adding a new column every month? Will that happen for ever? In 10 years from now, will you have 240 more columns? This is not a solution you should be pursuing if so.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 14, 2016 at 8:58 am
is there any dynamic data flow approach ?
example if they are updating every month. but every 2 months.
what should i do
September 14, 2016 at 9:03 am
coool_sweet (9/14/2016)
is there any dynamic data flow approach ?example if they are updating every month. but every 2 months.
what should i do
You can't. When the table changes, you need to change the mapping. Changing tables on a regular basis is usually a sign of a lack of proper normalization which causes issues like this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2016 at 9:23 am
coool_sweet (9/14/2016)
what should i do
Normalise the data. You should never be added an extra field onto a table each time you have a new value. You should have two tables.
If, for example, you currently have a table with fields: "CustomerID, CustomerName, Purchase1, Purchase2, Purchase3, Purchase4, Purchase5". What happens if they purchase 6 items? If you have to add a new column, then the above happens. It's extremely bad practice.
Instead, you should have two tables. One with the fields "Customer ID, CustomerName" and another with the fields "PurchaseID, Purchase, CustomerID". Then you add a new entry in the purchase table, with every purchase. You can cater for effectively infinite customer purchases that way. YOu just need to join the tables in your SQL.
Your solution isn't to find a solution to cater for the extra column each month, it's to fix the data you have to work effectively. That may mean changing the way your system works, but it should really programmed in that way in the first place.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 16, 2016 at 11:54 am
I am interested in seeing what the columns actually are. Can you post this? I suspect you are not satisfying 3rd normal form in your database tables.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply