I'm attempting to use a temporary SQL Server table ##temptable to preprocess imported data from flat .csv files.

I created the temporary table in the first step of the control flow, calling a stored procedure with 3 input parameters which determine the name of the table as well as the name of the existing table with which to "mimic". I'm creating the temp table with all varchar columns for various reasons, including to screen for "NAN" values and to strip data from the 1st row of a 4-row header (the column names are in the 2nd header row).

The next step in the control flow is a data flow task. I'd like eventually to use a variable for the target table, but I'm just trying to get this to work. When I try to specify the temporary table, it cannot be found, so I cannot setup the mappings.

I've tried both creating the temporary table in SSMS and creating it by executing the SQL task in the previous step of the control flow. Neither way lets me see the temp table. I've made sure the connection manager for the SQL connection is set with RetainSameConnection=True.

Is this something I'm "allowed" do? If so, what am I missing?

I'm attempting to use a temporary SQL Server table ##temptable to preprocess imported data from flat .csv files.

I created the temporary table in the first step of the control flow, calling a stored procedure with 3 input parameters which determine the name of the table as well as the name of the existing table with which to "mimic". I'm creating the temp table with all varchar columns for various reasons, including to screen for "NAN" values and to strip data from the 1st row of a 4-row header (the column names are in the 2nd header row).

The next step in the control flow is a data flow task. I'd like eventually to use a variable for the target table, but I'm just trying to get this to work. When I try to specify the temporary table, it cannot be found, so I cannot setup the mappings.

I've tried both creating the temporary table in SSMS and creating it by executing the SQL task in the previous step of the control flow. Neither way lets me see the temp table. I've made sure the connection manager for the SQL connection is set with RetainSameConnection=True.

Is this something I'm "allowed" do? If so, what am I missing?

• sql-server
• ssis
• temp-tables
• flat-file

• Are you really sure you need this temporary table? Usually all conditionally handling should also be possible to achieve in your data flow. If the amount of data isn't too large, you could consider using a ADO RecordSet to hold your temporary data. – Filburt Commented Mar 16 at 19:09
• 2 Temporary tables are just that; temporary. I suspect you aren't doing all this inside a transaction, and therefore your table has done what it was supposed to do; be temporary (and so no longer exists). – Thom A Commented Mar 16 at 19:27
• Also, beware that temporal tables can cause "interesting" collation issues (i.e. when the database/table/column collation is different than the server collation). I'd rather err on the side of caution in that area, i.e. use a database table. – Gert Arnold Commented Mar 16 at 20:35
• OK, the particular file I'm importing has 83 columns, the names of which are defined on the 2nd line of the 4-line header. The first line of the header has meta data that I need to capture, as well, and does not have 83 columns. In addition, many (up to 1/2) of the columns can have a value of "NAN", although the data type is FLOAT, so must be transformed. I've considered creating a "permanent" table of NVarChar fields to use as a buffer table. But, I have many other data streams of varying sizes that I need to import, and I don't relish having duplicate tables Any other ideas for me? – beantreeze Commented Mar 17 at 15:18

1 Answer 1

A temp table is temporary once transaction /connection is closed. Each SSIS container is its own transaction. Instead of using temp table you can create a stage table, use it, then drop it (basically a temp table).

Or if you need a temp table you need to update some settings so the connection uses the same connection across the different containers.

To do this you right click on your connection manager and go to Properties, then look for this:

Update this to retain same connection to True.

As you are using a temp table that does not exist at run time you will have to right click on each container in your package and change this setting:

Change Delay Validation to: True.

So the package does not try to validate/check if the table exists anywhere you are trying to use it until after the package executes (creating the temp table for example in first step). Then in second step (or anywhere you are using the temp table).

I think this should be all the settings you need to change.