SSIS advanced conversion task-to use a temporary table in a package, you must set the retainsameconnection attribute.

In the example of the previous import column, we create an actual table to store the file path. in the production environment, we may not create an actual physical table to store this information, instead, create a temporary table. Here is a small trick: drag and drop two Execute SQL tasks in control flow, one is to create a temporary table, the other is to destroy the temporary table, and execute this package, an error will occur, the prompt message in progress shows that the temporary table does not exist. The error message is as follows:

Error: 0xc002f210 at Execute SQL Task 1, execute SQL task: executing the query "Drop table # Table1" failed with the following error: "cannot drop the table '# table1 ', because it does not exist or you do not have permission. ". possible failure reasons: problems with the query, "resultset" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task 1

Obviously, even if the same connection object is used, the two Execute SQL tasks do not use the same connection. Check the properties of this OLE DB connection and you will notice that there is a regainsameconnection attribute, the default value is false. You must set it to true to meet your needs. 6-1

Figure 6-1

Each task uses this connection independently, but the temporary table can only be valid in one connection. When the connection is closed, the temporary table does not exist. Modify this attribute to true. All tasks use the same connection, so that no error occurs. This attribute setting is also important when you need to connect to the database in a cyclic task. It will avoid opening multiple times and close the connection.

This is the same as using temporary tables when writing SQL statements. Therefore, SSIS is easy to use as long as we are familiar with the concepts in SQL Server.