July 27, 2016 at 9:10 pm
I am completely new to SSIS and have been experiencing an impasse. I am trying to successfully execute a package containing
17 tasks, I've resolved numerous errors, however, there is an error based on a FK constraint that I've not been able to resolve. Ive read a few articles on dropping and re-creating constraints but I feel they are complex, for a more advanced application, or are over my head from a familiarity standpoint. My question is, for a simple package and from an visual task aspect, how can I implement a SSIS feature in the specific task that will account for this error type.
Below is a One Drive link to a screenshot of my solution
https://1drv.ms/f/s!Ai9bJ2dV87SLgfwGrYuaLBK3qZgVjw
_____________________________________________________________________
As soon as you see something, you already start to intellectualize it. As soon as you intellectualize something, it is no longer what you saw. Suzuki-roshi
July 27, 2016 at 9:17 pm
The simplest way is normally to update the related (foreign key) tables, making certain that the values exists before attempting the insert / update of the other tables which have foreign key constraints.
😎
July 27, 2016 at 9:50 pm
Ok but through SSIS right? Is this via adding an execution task? Where between the source and destination object within the task to you place such a execution task?
_____________________________________________________________________
As soon as you see something, you already start to intellectualize it. As soon as you intellectualize something, it is no longer what you saw. Suzuki-roshi
July 27, 2016 at 10:52 pm
vega805 (7/27/2016)
Ok but through SSIS right? Is this via adding an execution task? Where between the source and destination object within the task to you place such a execution task?
First I have few questions;
1) What is the data source?
2) What is the destination db type (OLTP / DW)?
3) Is SSIS running on an SQL Server Instance?
Preferably one would use staging area/tables which greatly simplify the import process (ELT rather than ETL) by shifting the DB logic into the SQL Server rather than performing such operations within SSIS. SSIS is a great tool for pumping data and for process execution, it is less than mediocre for relational database logic.
Slight side note, if your SSIS package has many tasks, think of simplifying it by separating those into several packages and execute those from a master package, much easier to maintain. My normal approach is to have a master package which creates and executes each individual task package on the fly reading the logic from configuration tables.
😎
July 28, 2016 at 10:16 am
Thank you for the followup Eirikur.
1) The data sources are separate OLTP databases on the same network, when I can successfully execute locally I will hand off the package to another network with different db credentials and will thus need to modify the connection manager.
2) The destination db is also OLTP
3) I don't believe the SSIS is running on a SQL Server instance, I'm not sure how I would validate that. Regardless, when I pass it off I believe it will run standalone. If it would help and it is NOT currently running on an instance I could change the strategy, I'm not sure how I would run against an instance.
I wonder since the FK constraint issue is distinct to 1 of 17 tasks, that it may be best to manage within SSIS, I dont want to over-complicate this right now - there's a potential that the need/requirement will expand where I may need to run SSIS on a MSSQL instance and become more "industrial", but Ill have a lot more insight at that time.
The second image in the below OneDrive directory illustrated the specific object Im having issue with.
https://1drv.ms/f/s!Ai9bJ2dV87SLgfwGrYuaLBK3qZgVjw
Separating tasks into several packages, though provoking... I can see the benefits. Once I get this to run I think I want to enhance the project by doing that.
_____________________________________________________________________
As soon as you see something, you already start to intellectualize it. As soon as you intellectualize something, it is no longer what you saw. Suzuki-roshi
July 28, 2016 at 10:45 am
Since the constraint is due to TRUNCATE, you could remove the constraint while you run the package and then add it back.
Alternative is to do an incremental load on your FK constraint table (Acct) instead of truncate and load. There is a stairway to SSIS explaining the incremental load http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/75331/%5B/url%5D
July 28, 2016 at 1:41 pm
vega805 (7/27/2016)
Ok but through SSIS right? Is this via adding an execution task? Where between the source and destination object within the task to you place such a execution task?
Your package can contain multiple dataflows.
Or you can have multiple packages, one for each dataflow, and run them one after another from a master control package.
These multiple dataflows should run in logical order, such that the tables which are populated first are the ones whose values are the target of FK constraints.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply