How to troubleshoot production package outside of SSISDB framework
Could anyone please answer the above question. This is recent interview question.
See also questions close to this topic
Predicting data with Python script in an SSIS package
I'm aware of Microsoft's inclusion of Python in their Machine Learning Services for SQL server, however this is only available for SQL Server 2017 and up, which is a requirement my servers do not currently meet.
With that being the case, I wanted to deploy my generate-predictions-with-trained-model pipeline entirely within SSIS, I.E:
- Grab data from my DB
- Pass it to a Python Script Data Flow Task which imports the trained model, generates the predictions and passes them on to the next Data Flow Task
- Write the predictions to the DB
Is there a way to do that entirely within SSIS, or will it be necessary to just use Execute Process Task to kick off the Python script and have that stored separately on the server somewhere?
Prevent deadlock in read-committed SELECT
I am extracting data from a business system supplied by a third party to use in reporting. I am using a single SELECT statement issued from an SSIS data flow task source component that joins across multiple tables in the source system to create the dataset I want. We are using the default read-committed isolation level.
To my surprise I regularly find this extraction query is deadlocking and being selected as the victim. I didn't think a SELECT in a read-committed transaction could do this, but according to this SO answer it is possible: Can a readcommitted isolation level ever result in a deadlock (Sql Server)?
Through the use of the trace flags 1204 and 12222 I've identified the conflicting statement, and the object and index in question. Essentially, the contention is over a data page in the primary key of one of the tables. I need to extract from this table using a join on its key (so I'm taking out an S lock), the conflicting statement is performing an INSERT and is requesting an IX lock on the index data page.
(Side note: the above SO talks about this issue occurring with non-clustered indexes, but this appears to be occurring in the clustered PK. At least, that is what I believe based on my interpretation of the deadlock information in the event log and the "associatedObjectId" property.)
Here are my constraints:
- The conflicting statement is in an encrypted stored procedure supplied by a third party as part of off-the-shelf software. There is no possibility of getting the plaintext code or having it changed.
- I don't want to use dirty-reads as I need my extracted data to maintain its integrity.
- It's not clear to me how or if restructuring my extract query could prevent this. The lock is on the PK of the table I'm most interested in, and I can't see any alternatives to using the PK.
- I don't mind my extract query being the victim as I prefer this over interrupting the operational use of the source system. However, this does cause the SSIS execution to fail, so if it must be this way I'd like a cleaner, more graceful way to handle this situation.
Can anyone suggestion ways to, preferably, prevent the deadlock, or if not, then handle the error better?
How to share encrypted data from SQL Server to MYSQL
I need to lift data from SQL Server using SSIS, some of the data need to be encrypted before writing to csv's... the data then needs to be decrypted and loaded into a MYSQL destination. I can switch on encryption on the SQL server side but i'm unsure how to pass the key/decryption logic to the MYSQL developers.
Tried writing the encrypted data (hashed) to csv but stuck!
Thanks a lot