May 1, 2020 at 2:21 am
Hi, i need a tool to use to take data from Table A and it maps to elements in a mapping table and then updates Table B
SSIS is able to complete this, but i am wondering what other tools you guys have used and are better than SSIS for SQL Server.
Thanks
May 1, 2020 at 7:07 am
T-SQL in a Stored Proc would be better than SSIS 🙂
May 1, 2020 at 4:09 pm
I think that really falls into an "it depends" category.
Based on your description, SSIS is likely going to be the best tool. I would advise against a stored procedure only because you are likely going to be using some dynamic SQL which can get more difficult to understand and introduces additional risk.
WHY I am thinking you will need dynamic SQL is you are mapping columns from table A to table B by using a mapping table. If it was just straight A->B without that mapping table, then a stored procedure would work and likely have a performance benefit. You might not need dynamic SQL (depending on how it works), but you may end up having a very complex stored procedure that is hard to maintain and hard to debug long term.
Other tools you could use (although may not be better) are things like Tibco Scribe, a custom built .NET application, powershell, etc.
My understanding of what you are doing is mapping column A from Table A to a customizable column in table B. Customized from the mapping table, which may even be looking at columns A, B and C from table A and based on the value, it may put that in column A, B OR C in table B. Now, if it is a consistent mapping (such as A maps to C every time) then dynamic SQL wouldn't be needed and you will likely get a good performance boost using a stored procedure.
Now, if it does need to do mapping and it is complex, you may get a performance boost by doing that mapping in a custom built .NET application. Powershell is not likely to improve performance over SSIS, but will allow you to offload it onto a less busy server. Depending on your license agreement, you likely have SSIS on the same box as your SQL instance(s), so using powershell OR a home-grown application would allow you to offload that to a new or quieter server and have less impact on the SQL instance(s).
One last consideration is are Table A, the mapping table, and Table B all in the same database? all on the same instance? If you have to go cross instance (or cross database) you will have a performance hit and will have to be more careful about database security.
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.
May 3, 2020 at 4:13 am
I was able to install visual studio and set up my first SSIS package to take data from table a and put into table b and it also wrote to a error table.
I will now see how i can capture the foreign key issues in a table and then do my mapping from control tables.
Not bad since i have never used VS before and it seems possible to use SSIS.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply