2

This issue occurred and I would like to know possible causes: During text file import the SSIS package created tables in the wrong database instead of writing in the correct tables in the correct database.

Details: There is an application which uses SSIS packages to import text files daily. Those file records are stored in tables in the application database and they are available until the next set of files is received, when the new records will replace them.

The records are also stored in another database where they are kept, with import date information.

The tables keeping the same information in the two databases have different names.

The SSIS package contains:
two database connections
a file connection
and a dataflow task with:
- one flat file Source
- one multicast
- two OLE DB destinations

Dataflow Task

Execution: dtexec

The package has been running for a year now.

There is NO table creation task. I connect the output of the multicast to already created tables in the database.

Issue: one time the SSIS package wrote the records in the storage database tables and, instead of writing in the application database tables, it created the application database tables in the storage database and wrote the records there.

What could have caused it?

Thanks in advance.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Aris Glykas
  • 21
  • 3
  • How do you excecute the package? dtexec, sql job? What about the connection manager, how you set the connection string?? Please add all the information you can (specially log information). – Nambu14 Mar 01 '17 at 12:19
  • Thank you for your reply! dtexec AppDB Connection string : Data Source=SERVER;Initial Catalog=ApplicationDatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS_Generic_Import-{6BCDBF3D-FED7-4C6C-92E6-72560EE95945}SERVER.ApplicationDatabase; StorageDB Connection string : Data Source=SERVER;Initial Catalog=StorageDatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS_Generic_Import_v08-{BC725C2E-A28F-4A4B-8C66-30A38C178E18}SERVER.StorageDatabase; File Connection string : Path variable + File Name – Aris Glykas Mar 01 '17 at 12:38
  • SSIS won't pick an arbitrary database. It doesn't create tables either, unless you explicitly add a SQL task to create them. It connects to the database that you tell it to connect. Pasting some connection strings as comments doesn't help - how many of them are there? Post the info in the question itself. Clearly identify the steps in the flow, which is the source, which are the targets, how do you multicast? Paste screenshots of both the control and the data flow. Clearly, you have a table creation task. Which connection does it use? – Panagiotis Kanavos Mar 01 '17 at 13:16
  • How is your package configured? dtsConfig file? SQL table? Could the configs have been changed during this one execution that went awry? – Mark Wojciechowicz Mar 01 '17 at 15:26
  • Is is possible that someone opened the package and changed the connection in there for some test purpose and did not change it back in time? – Inus C Mar 06 '17 at 08:18
  • @Inus C: Good thought, but, no, that is not possible. – Aris Glykas Mar 06 '17 at 12:58

0 Answers0