Using Temp tables in SSIS

[Originally posted by]: http://stackoverflow.com/questions/1579476/using-temp-tables-in-ssis

I am using a Temporary table in Stored Procedure in SQL Server. I am trying to use that SP in OLE DB Source Editor.

I can see the data output returned in the Query Builder that comes with Build Query button. But when I Click the Columns tab, I am getting the below error.

– TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Invalid object name ‘##Payment’.”.

Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

Does that mean I cant use temp tables in SP, if I want it to be consumed by SSIS

shareedit

For all the hassle involved, I think it is probably just not worth it. Create a real table in the db and truncate it before / after your load. If it’s for a datawarehouse it isn’t going to matter if you have an extra table or two. This gives you the design-time SSIS tools and means you don’t have to worry about the intracacies of temp tables.

If you want to keep things separate, then just create your SSIS temp tables in a separate schema. You can use permissions to make this schmema invisible to all other users.

CREATE SCHEMA [ssis_temp]

CREATE TABLE [ssis_temp].[tempTableName]
shareedit

You can use table variables instead of temporary tables . it will work

shareedit

SSIS Package not wanting to fetch metadata of temporary table

[Originally posted by]: http://stackoverflow.com/questions/18346484/ssis-package-not-wanting-to-fetch-metadata-of-temporary-table

I have an SSIS Package, which contains multiple flows.

Each flow is responsible for creating a “staging” table, which gets filled up after creation. These tables are global temporary tables.

I added 1 extra flow (I did not make the package) which does exactly as stated above, for another table. However, for some reason, the package fails intermittently on this flow, while it is exactly the same as others, besides some table names.

The error that keeps popping up:

Update – Insert Data Flow:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Unspecified error”. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “The metadata could not be determined because statement ‘select * from ‘##TmpMcsConfigurationDeviceHistory86B34BFD041A430E84CCACE78DA336A1” uses a temp table.”.

Creation expression:

"CREATE TABLE " + @[User::TmpMcsConfigurationDeviceHistory]  + " ([RecId] [bigint] NULL,[DataAreaID] [nvarchar](4) COLLATE database_default NULL,[Asset] [bigint] NULL,[Code] [nvarchar](255) COLLATE database_default NULL,[Configuration] [bigint],[StartdateTime] [datetime] NULL,[EndDateTime] [datetime] NULL)

Parsed expression (=evaluated):

CREATE TABLE ##TmpMcsConfigurationDeviceHistory764E56F088DC475C9CC747CC82B9E388 ([RecId] [bigint] NULL,[DataAreaID] [nvarchar](4) COLLATE database_default NULL,[Asset] [bigint] NULL,[Code] [nvarchar](255) COLLATE database_default NULL,[Configuration] [bigint],[StartdateTime] [datetime] NULL,[EndDateTime] [datetime] NULL)
shareedit

Using WITH RESULT SETS to explicitly define the metadata will allow SSIS to skip the sp_describe_first_result_set step and use the metadata that you define. The upside is that you can use this to get SSIS to execute SQL that contains a temporary table (for me, that performance helped a lot); the downside is, you have to manually maintain and update this if anything changes.

Query sample (stored procedure:)

EXEC ('dbo.MyStoredProcedure')
WITH RESULT SETS
(
    (
        MyIntegerColumn INT NOT NULL,
        MyTextColumn VARCHAR(50) NULL,
        MyOtherColumn BIT NULL
    )
)

Query sample (simple SQL:)

EXEC ('
    CREATE TABLE #a 
    (
        Col INT
    ) 
    INSERT INTO #a 
    (
        COL
    )
    SELECT 1 AS Col

    SELECT Col 
    FROM #a')
    WITH RESULT SETS ((Col INT NOT NULL))
)
shareedit

Stop SSIS Package execution in SSIS.Catalog

[Originally posted by]: https://sabigdata.wordpress.com/2015/02/12/stop-ssis-package-execution-in-ssis-catalog/

This article is about explaining how to cancel or stop a job that was executed directly from the SSIS.Catalog.

I executed a SSIS Package directly from the SSIS.Catalog where I subsequently had to cancel the process. My research lead to the following two options. You can choose which one will work for your situation.

Option 1

Information about the packages are stored in the SSISDB. To view the package information you can query the Catalog.Executions view in the SSISDB.

The following query will return all currently running packages in the SSIS.Catalog

select * from catalog.executions Where end_time is null

You need to use the execution_id found in the result set to stop the package using an internal SSISDB stored procedure.

All_Executions_Query_Results

To determine if you have the correct execution_id run the All_Executions report in the SSIS.Catalog, identify the correct ssis package that is executing and click on the Overviewhyperlink.

All_Executions

The drill down report will give you the execution_id of the currently running process.

All_Executios_Overview

The following T-SQL statement will stop the execution of the SSIS package.

Exec catalog.stop_operation  @operation_id =  11

Option 2

When you right click on the SSISDB in the Integration Services Catalogs click on Active Operations. A new window will open with a Stop button where you can cancel the specific ssis package that is executing.

ActiveOperations

Also see the Microsoft documentation related to this topic.

The value violated the schema’s constraint for the column(Cannot able to upload 4000 length data from Table to Excel 2007 using OLEDB Destination IN SSIS)

[Origianlly posted by]: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/de21545f-9733-4a86-bd54-3891fe08ce55/the-value-violated-the-schemas-constraint-for-the-columncannot-able-to-upload-4000-length-data?forum=sqlintegrationservices

Hi All,

I am not able to upload data into excel 2007. I have created package is SSIS and I have used OLEDB destination to connect Excel 2007 file. When I am trying to export data into excel 2007 its giving error like The Value voilated Constraint of the column. My table has one column URL with NVARCHAR(Max) Data Type. I have converted this column data type into NText data type using data conversion and from that conversion it will connect EXcel 2007 file.
The URL column contains minimum length of 2000 it means all rows are greater than 255 characters. I have verified the advaned tab of Excel 2007 destination connection it has taking DT_WSTR 255 by default. Can any one please help out how can we export data more than 255 characters in length. In my case there are no rows less than 255 character length.

Any Help?

Thanks
Satish


DataBase Designer

Tuesday, September 22, 2009 6:34 AM
Avatar of SatBI
Hi All,

After long time reserach I have found it doesnt possiable to move data from Table to Excel 2007 that having columns data more than 255 character. I have tried all the ways but not reached the result. But it will upload to excel 2003 with out any problem.

In simply When source column > 255 excel 2007 assigns data type DT_WSTR 255  no matter how your settings are, it results the transfermation fail.
When Source column > 255 ( in sample data first 8 rows) Excel 2003 assigns DT_Ntext, so the transfermation will succed.

In contarst excel 2007 doesn’t support DT_Ntext data type.

I did have used excel 2003 and make segregation my data into multiple sheets. I know its a ditry work, but i have no option.

As of I know SQL SERVER 2008 Integartion Services do support excel 2007.

Thanks,
Satish


SatBI

  • Marked as answer by SatBI Wednesday, September 23, 2009 12:44 PM
Wednesday, September 23, 2009 11:14 AM
Avatar of SatBI

I too ran into this limitation/bug or whatever. I searched the entire Internet to no avail. I did not want to use Excel 2003 as above.

My solution or hack is to “fake out” the Excel destination object by using an Excel file pre-filled with > 255 characters in the columns that may receive > 255 characters. The prefill data can be anything and only needs to be on row 2 assuming row 1 has headers.  You would also need to do the registry entry to change the sample data from 8 to 1 row. The Excel Destination columns then correctly set to Unicode text stream [DT_NTEXT] to allow SQL export of greater than 255 characters to those Excel column with no errors.  All my rectangles turn beautiful green when run in BIDS!

I simply hide row 2 so that the “pre-fill” data doesn’t show for my customers.  In case someone finds the hidden row I have used this text to explain: “Text fill to facilitate import to Excel column greater than 255 characters – ignore.” repeated enough to add up to >255.

Hope this helps someone.

Richard

  • Proposed as answer by Savorathon Thursday, October 15, 2015 2:21 PM
Saturday, May 16, 2015 2:37 AM
Avatar of SatBI

@Richnet31:

After spending many hours searching for solutions to this issue, I tried your “fake out” method and it worked great!  I’m glad you took the time to share your info.  Many many thanks.

One thing I should mention is that I didn’t need to edit the registry on my PC.  Just one sample row after the header was sufficient.

Cheers!

John K

  • Edited by Savorathon Thursday, October 15, 2015 2:25 PM
Thursday, October 15, 2015 2:25 PM

How to insert a cloumn with more than 255 characters to excel in SSIS

[Originally posted by]: http://stackoverflow.com/questions/20746230/how-to-insert-a-cloumn-with-more-than-255-characters-to-excel-in-ssis

I have a table column with length more than 255 characters and I need to insert this value into excel. When ever the column value exceeds 255 characters, I am getting “Excel Destination [15]: Truncation may occur due to inserting data from data flow column “Copy of Column 0” with a length of 500 to database column “column1” with a length of 255.” error at excel destination component.

Please help me to find a way to insert a column with more than 255 characters into an excel.

shareedit

I’d imagine you can overwrite the setting through the Advanced Editor in SSIS.

Right Click the Excel Destination > Show Advanced Editor > Input and Output Properties

Excel Destination Input > External Columns > Column 1 (Change Length to be 500).

This should stop the error.

shareedit

How To Set and Use Variables in SSIS Execute SQL Task

[Originally posted By]: http://dataqueen.unlimitedviz.com/2012/08/how-to-set-and-use-variables-in-ssis-execute-sql-task/

I couldn’t find any step-by-step resources on this so I thought I’d blog it.  This is done in SQL Server 2008 R2. In this scenario I will use a SQL Statement to populate an SSIS variable, and then use that variable in another SQL Statement.

Create your variables

If the variables window isn’t visible already, from the menu select View –> Other Windows –> Variables.

SNAGHTML1fb1c4da

This will bring up your variables window on the left hand side of your screen.  You should be able to tab between it and the Toolbox.  You may have to pin it to your screen first.

image

I have already created some variables in the screenshot above.  To create a variable, first decide on the scope of the variable, whether it is the entire package, a container or a specific task.  I normally scope all of my variables to the package, since you can’t see the variables in the window unless you have selected the scoped object.  I find it easier to manage variables having them all in one place.  I have not had any compelling reason not to do it this way, although I’m sure there are cases where you may want them scoped differently.

Before creating the variable you must select the item for it’s scope. In this case select the canvas of the Control Flow which will permit you to scope the variable to the entire package.  Then create the variable by clicking on the Add Variable button at the top of the Variables window.

image

Give your variable a Name, a Data Type and a Value.  In this case we are working with EndDate, and although you would want to set it as Date, there are some compatibility issues when using the Date data type for a variable and then pulling into a SQL parameter.  I have found it is best to set the date as a string and then manipulate it from there.  Set your EndDate attributes as shown, with the Scope being the name of your package.

image

Populate an SSIS variable using a SQL statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow.  Double click on it and set the following properties:

On the General tab:

  1. Set the ResultSet to Single row
  2. Select your Connection to the database where you will run your SQL Statement
  3. Enter your SQL Statement

image

On the Result Set tab:

  1. In the Result Name key in the name of the field being return in your SQL Statement.  In this case I named the field EndDate
  2. In the Variable Name select the user variable you created.

image

Use the Variable in a SQL Statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow. Double click on it and set the following properties:

On the General tab:

  1. Select your Connection to the database where you will run your SQL Statement
  2. Enter your SQL Statement, placing a ? where each variable is to be used.

image

In this case I need to add two Parameters to the Parameter Mapping tab, since I have used two question marks in my SQL Statement.  Even if you are using the same Variable multiple times, you still need to enter them separately on the Parameter Mapping tab, once for each time they appear in the SQL Statement.  This is the only way to identify which parameter is being used for which question mark.  Parameters are made much easier in 2012.

Here’s the tricky bit which isn’t at all self explanatory.

On the Parameter Mapping tab:

  1. Select the user variable in the Variable Name drop down which corresponded to the first variable (?) being used in your SQL Statement.
  2. Set the Data Type to NVARCHAR, since we are using a string variable. (This is the spot where the data type compatibility didn’t mesh, which is why we opted for string).
  3. Set the ParameterName to 0.  This indicates the order in which the parameter is appearing in the SQL statement.  You can enter them in the pane in any order you want, but the ParameterName must contain numbers starting from 0 to however many parameters you have minus one.  So if you have two parameters you must have one ParameterName = 0 and one ParameterName = 1.  You can reverse the order, but they must both exist or the task will fail.

image

Including the Date in a Filename in SSIS using an Expression

[Originally posted by]: http://www.sqlmatters.com/Articles/Including%20the%20Date%20in%20a%20Filename%20in%20SSIS%20using%20an%20Expression.aspx

When creating a file in SSIS it can be useful to incorporate a date and/or a time stamp into the name of the file, for instance :

   File_20121225.csv
   File25122012_1339.txt
   Output25Dec2012.csv
   Output_25Dec2012_2345.txt

Fortunately it’s fairly easy to achieve this using expressions. Expressions are a way of calculating a value based on various criteria. In this case I’ll use an expression to generate the file name for a file connection, based on the current date with the format YYYYMMDD.

To illustrate this I’ve created a very simple SSIS package that runs a SQL query (in my case this calculates the size of each database) and exports the results to a file. The package has a single Data Flow Task as follows :

Including the Date in a Filename in SSIS using an Expression image 1

Switching to the Data Flow tab you can see from the screenshot below that the task has an OLE DB Source which uses a SQL Server connection, and a Flat File Destination which uses a File Connection :

Including the Date in a Filename in SSIS using an Expression image 2

If I click on the Flat File Connection Manager (circled below) then the Properties window at the bottom right of the screen will show properties information for the connection :

Including the Date in a Filename in SSIS using an Expression image 3

The initial setting for the ConnectionString property can be seen in the screenshot above in the “Properties” window, but this can be customised using the Expressions property a bit lower down (circled above). Click on the 3 dots to the right and the “Property Expressions Editor” window will be displayed. Click on the Property column and a dropdown will display all the properties that can be customised using an expression. Select ConnectionString and then enter the expression directly into the Expression column on the right :

Property Expression Editor
The expression that I’ve used here is :

“C:\\DatabaseSizes_” + (DT_WSTR,4)DATEPART(“yyyy”,GetDate()) +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“mm”,GetDate()) ,2) +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“dd”,GetDate()),2) + “.csv”

This will generate a file name like : C:\DatabaseSizes_20120928.csv.

If you need to include the time in the filename as well then here is an alternative expression which adds the time in the format “hhmm” :

“C:\\DatabaseSizes_” + (DT_WSTR,4)DATEPART(“yyyy”,GetDate()) +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“mm”,GetDate()) ,2) +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“dd”,GetDate()),2) + “_” +
RIGHT(“0” + (DT_WSTR,2)DATEPART(“hh”,GetDate()),2)+
RIGHT(“0” + (DT_WSTR,2)DATEPART(“mi”,GetDate()),2) + “.csv”

This will generate a file name like : C:\DatabaseSizes_20120928_0913.csv.

Visual Studio also includes an Expression Builder which makes it easier to create and validate expressions. Click on the 3 dots to the right of the expression to display the “Expression Builder” window :
Expression Builder
You can enter the expression into the window or build it up using the various functions and variables. If you click the “Evaluate Expression” button it checks for any errors and determines the value of the expression – a very useful feature.