October 24, 2019 at 10:04 pm
The (admittedly obscure!) intention of my post was not to criticize your response, but to highlight the fact that in making things dynamic, SSIS slides out of the equation. I could have been a lot clearer, apologies.
hahaha... I misread it as "Your reply is getting off topic", which is something I try not to do, but have been known to.
And I think you COULD still use SSIS and have dynamic SQL in there, but it would be a nightmare to maintain. I dislike using direct T-SQL in SSIS as the configuration window is such a pain to use (among other issues). If I HAVE to use T-SQL in SSIS, I write it in SSMS and once I have it fine tuned and "beautified" (ie proper tabs, square brackets around objects, properly named aliases and such), then copy-paste into SSIS. But then something changes and that code needs modifying, it is incredibly difficult to modify from within SSIS and I end up copy-pasting back to SSMS.
I think if you NEEDED to do dynamic SQL in SSIS, I'd recommend doing it in a stored procedure; but then I'd call that directly from a SQL job and take SSIS out of the equation as you said. Just because you can do it in SSIS, doesn't mean you should...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 24, 2019 at 10:13 pm
Phil Parkin wrote:The (admittedly obscure!) intention of my post was not to criticize your response, but to highlight the fact that in making things dynamic, SSIS slides out of the equation. I could have been a lot clearer, apologies.
hahaha... I misread it as "Your reply is getting off topic", which is something I try not to do, but have been known to.
And I think you COULD still use SSIS and have dynamic SQL in there, but it would be a nightmare to maintain. I dislike using direct T-SQL in SSIS as the configuration window is such a pain to use (among other issues). If I HAVE to use T-SQL in SSIS, I write it in SSMS and once I have it fine tuned and "beautified" (ie proper tabs, square brackets around objects, properly named aliases and such), then copy-paste into SSIS. But then something changes and that code needs modifying, it is incredibly difficult to modify from within SSIS and I end up copy-pasting back to SSMS.
I think if you NEEDED to do dynamic SQL in SSIS, I'd recommend doing it in a stored procedure; but then I'd call that directly from a SQL job and take SSIS out of the equation as you said. Just because you can do it in SSIS, doesn't mean you should...
Any T-SQL more complicated that TRUNCATE TABLE, or SELECT col1, col2 FROM table does not belong in SSIS, in my opinion. Create procs and call those instead. My drivers are different from yours, however:
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
October 30, 2019 at 8:13 pm
Interestingly, no one has mentioned SQL Server ""replication". It CAN handle schema changes and copy stuff over to a different database or even such on a different server. The question is thus, just what, exactly, needs to be done? As long as you aren't expecting to ever try to put identically named objects from more than one source database into the same target database, you should be okay. It is also a lot easier to manage the replication if you simply have one copy of each source database as the target for a given source database's replication.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply