SSMS - Running SSIS package via batch file - resulting table empty

Question

Currently I am building a database in SQL Server Management Studio (SSMS). Several data sources are imported in this database, via the import/export wizard. Upon completion of the import, not only am I running the import, I also save an SSIS package. This SSIS package is to schedule a weekly refresh of the data source. On my Windows Server 2012 R2 the Express edition is installed, therefore I have manually created several batch files that run every week (as scheduled via Task Scheduler). This works fine for most tables, however for some tables I encounter some strange (?) behaviour.

This is as follows: when creating the SSIS package via import/export wizard, and directly running the import, the table shows up correctly in the database. That is, with all column names and the thousands of rows it contains. The strange thing is that, when executing the SSIS package (via the batch file), the table is empty (column names are correct though). For some tables, I do not encounter this behaviour. For others, this behaviour is encountered all the time.

The batch script is as follows (quite straightforward): "C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /F "C:\Users\username\ssispackage.dtsx"

The batch file seems to run correctly at all times, as the table 'creation_date' changes when I run the batch file. Moreover, for all the tables that do correctly 'refresh', these same batch files do the job.

Some settings of the SSIS package:

• Data source: Oracle OLE DB provider
• Destination: SQL Server Native Client / MS OLE DB Provider for SQL Server (tried both)
• Data via query (as I am querying several tables from Oracle); query is parsing correctly
• Mappings: Create destination table & Drop and re-create destination table
• Dropping and re-creating is done, because the data source is rather small, and has some changes weekly/monthly (to some rows).

For most data sources imported (and refreshed weekly) via this method, the tables are correctly showing each week (simply dropping the previous table, and re-creating the source).

I hope someone can explain to me why this issue occurs. If some more information is needed from my side, please ask.

Thanks in advance!

UPDATE:

When looking at the log of the batch file, this is (part) of the output:

Source: ..... "SourceConnectionOLEDB"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: **"ORA-01005: null password given; logon denied".**
End Error
.....next error.... "SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. 

Thus, it seems that the password is not remembered/saved correctly in the SSIS package? This is strange though, as for most tables it does correctly store the password (as those do refresh correctly). When setting the properties of the data source, namely Oracle Provider for OLE DB, I select the option "Allow saving password". So it should store the password correctly?

Answer 1

Found the answer after all.. The .dtsx file that is saved (the SSIS package) contains the variables for the connection string, it shows that the Password (Sensitive="1") is there. But in the wizard, I did not select 'Save sensitive data with user key'. When selecting this option, an encryption string was added. Now the SSIS packages run well!