SSIS – Using SSIS Variable within a SQL Script with Temp Tables – Error at Data Flow Task : No Column information was returned by the SQL Command

Below details what I was trying to do within SSIS and how I got it to work.

Overview

1. I was using a SQL Script which contained some temp
   tables which was used in the result set.
2. When I then put this into an OLE DB Source I would get the error shown below:
   1. 

1. I then tried to see if it would work when I created a SQL Command from Variable.
   1. And this would result in the same error as above.
2. If I did click on OK, I would then the following in the Columns window as shown below:
   1. 
3. So below is a solution which I found to work to get the data from a SQL Query that uses temp tables.

Example Data

For our solution below we are going to use the following sample data.

• The first
  variable that we are going to be using will be for a DayNumber
  • NOTE: This is because we want to go back in time, so we want to go back and loop through the past 10 days.
• The second
  variable that we are going to be using is our actual SQL Query.
  • So this will be very simply put with the following below:
  • 
  • NOTE: This is a very simple
    query and I want to just use this as an example. I am certain in most of your scenarios it will be a much more complex query that is giving you the above error within SSIS.

Solution

1. The first thing that you will need to do, is to create an ADO.NET Source.
2. Next you will need to ensure that you have two
   variables created
   1. The first
      variable that you want to pass into your SQL
      Statement
   2. The second
      variable is your SQL query.
3. As with our example above we created the following variables within SSIS
   1. 
   2. From the above you will see that we have created some additional
      variables as explained below:
      1. DayNumber is the day number variable that we want to pass to our SQL Query.
      2. StartNumber is the starting variable in our For Loop Container
      3. EndNumber is the ending variable in our For Loop Container
      4. Query_GetDayNumber is the query which is going to extract our Day Number and put this into a variable.
      5. Query_ExtractData is the SQL Query where we are going to extract our data including passing the variable.
4. Next we will create and configure our For Loop Container with the following:
   1. 
   2. NOTE: This is so that we can then know when to exit our of our For Loop Container
   3. NOTE II: Typically, you will have your StartNumber and EndNumber
      populated by an Execute SQL Task so that it can always be dynamic.
5. Next we will configure our variable for the Query_GetDayNumber so that it gets populated with the correct details as it loops
   through each time.
   1. Click on Variables, then where we have the Query_GetDayNumber
      click on the Ellipses button on the right
      hand side under
      Expression
      1. 
   2. This will then open the Expression
      Builder
      Window
   3. Now as with our example, we know that our
      number will always start at 1 and the DayNumber will always be the same as the StartNumber.
      1. NOTE: There will be situations where you will need to run an actual SQL Query against your data to extract the required information.
   4. And we put in the following:
      1. 
      2. NOTE: The reason for converting it to a string is because our StartNumber
         variable is defined as an Int32.
      3. You can then click on the Evaluate
         Expression to make sure that it is working.
   5. Then click Ok.
6. Next we will need to configure our variable for the Query_ExtractData, so that we can then pass our above variable within our SQL Query by doing the following:
   1. Click on Variables, then where we have the Query_ ExtractData
      click on the Ellipses button on the right
      hand side under
      Expression
      1. 
   2. Now we will put in our SQL Query from above, along with the variable as shown below:
   3. 
   4. NOTE: If you have a look at the above
      syntax you will see that we have put our variable
      DayNumber into our Expression.
      1. You can then click on the Evaluate Expression to make sure that it is working.
   5. Then click
      Ok.
7. Next create an Execute SQL Task and configure it with the following below so that it will be used to populate our DayNumber
   variable.
   1. Go into the Properties and configure the General
      page with the following below:
   2. 
   3. NOTE: A few quick things to note on the above:
      1. We have set the Result Set to Single Row
         1. This is to allow our variable to be returned into a result set.
      2. Our SQLSourceType has been set to Variable.
         1. This is so that every time the For Loop Container runs and loops through it will then get the new value.
      3. SourceVariable
         1. This has been selected from the drop down and will be our query which we created earlier.
         2. Which on each execution of the For Loop Container will go and get the values we require.
   4. Then click on the Result Set and put in the following:
      1. 
   5. Then click
      Ok.
   6. Now drag this into your For Loop Container
      
8. Next drag in a Data Flow Task and rename it to Extract Data
9. Then double
   click and go into your Data Flow Task
10. Now Drag in an ADO NET Source.
    1. Then ensure that you have not selected the ADO NET Source and have clicked on the sheet within SSIS, and go into the properties.
    2. Next to Expressions
       click on the Ellipses
       Button
    3. 
    4. Under Property click on the Drop Down and select the following as shown below
       1. 
    5. Then click on the Ellipses next to Expression.
    6. Now in the Expression Window put in the following variable as shown below:
       1. 
       2. NOTE: From the above you will see that this is our Variable query that we created and configured in step 6 above.
    7. Click on the Evaluate
       Expression and you will then see the actual query below in the Evaluated value:
       1. 
       2. NOTE: In the above it has gotten the number 12 from the default
          value in our Variable for DayNumber.
    8. Then click Ok.
    9. Now go back into your ADO NET Source.
       1. Ensure that you have got the correct connection to your data source.
       2. Then under Data access mode
          change this to SQL Command
       3. Then you should see your query populated below as with our example:
       4. 
       5. NOTE: It got this query
          information from our Expression, which in turn got the information from our variable.
    10. You can then click on Columns to see that the query
        runs and gets the required
        information.
    11. 
    12. Then click Ok.
11. Then the final step is to then link it to your destination table.
    1. NOTE: You can use either an OLE DB Destination or an ADO NET Destination.
12. Next make sure that you drag your Data flow task into your For Loop Container
    1. Then ensure that you place the Success
       Precedence
       Constraint
       between your Execute SQL Task and your Data Flow Task as shown below.
    2. 
13. Now you can run your SSIS package and it should work
    successfully.

This post was published to myfriendjoobs at 1:57:59 PM 8/5/2015

SSIS – Using SSIS Variable within a SQL Script with Temp Tables – Error at Data Flow Task : No Column information was returned by the SQL Command