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
How to automate downloading files from AWS server in SSIS
I need to download files from AWS server ,all files will be in zipped (.gz) files. I need to automate the process of downloading files from AWS server and store in one folder.
Can anyone please help with me on this.
Thanks in advance!!
Importing Flat file with repeating rows into SQL server
I have a very large Fixed length patient eligibility file that I would like to use SSIS to import into SQL. The problem is it's not a standard file, the first 15 columns made up of patient information (Name, Address etc). The next section (20 columns) are made up of the eligibility information for the above patient including history. This section repeats 100 times. I want to pull the first section into a patients table then import the next section (with up to 100 repeating rows for the same patient) into a child table.
I know it can be done in SQL, I'm doing that now but it's not very user friendly when it comes to someone else making changes in the future. The file is to large to do a bulk insert into one column.
Example File Format
FName: Bobby LName: Smith Addr1: 102 Elm St
Next section repeats 100 times
ElgStartdate: 01/01/2019 ElgEndDate: 02/01/2019 Type: SA
How do I tell SSIS that the next 20 columns in the are repeated 100 times.
Why does my SSIS package run in Visual Studio but fails validation on the server?
I have an SSIS Package that reads from two tables (on different instances), performs a Merge Join and the uses a Conditional Split to update one of the source tables. I have a single connection manger set up and use an linked server to access one of the tables on another instance. In the connection manager I use a login and a saved password with permissions to the databases and tables. It runs like a champ in Visual Studio. When I deploy it to my SQL Server 2016, it fails validation with the error "Update Customers:Error: Customer Data failed validation and returned error code 0xC020801B."
I have tried to move the linked server to it's own connection manager with a saved password. I also tried using Windows Authentication in the connection manager. These didn't work either. Also, when I search for the error code 0xC020801B nothing seems to come up.
The Package ProtectionLevel is EncryptSensitiveWithUserKey.
I changed the connection manager from a Project connection manager to a Package connection manager. I now get the error:
Source: "Microsoft SQL Server Native Client 11.0" HResult: 0x80040E4D Description: "Login failed for user 'integration'."
The user integration has access to the databases and the tables in question. I also tried to remove the login and use Windows Authentication. I added the server as a login with db_datareader and db_datawriter permissions on both databases. When I deploy and execute the package I get the error on the linked server connection:
Source: "Microsoft SQL Server Native Client 11.0" HResult: 0x80040E4D Description: "Login failed for user 'DOMAIN\INSTANCE_NAME$'."