Solution: Set the property RetainSameConnection on the Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task. Here is a sample SSIS package written in SSIS 2008 R2 that illustrates using temporary tables. Walkthrough: Create a stored procedure that will create a temporary table named … Read more
I’ve seen this issue before, it’s Excel that is the issue not SSIS. Excel samples the 1st few rows and then infers the data type even if you explicitly set it to text. What you need to do is put this into the Excel file connection string in the SSIS package. This instruction tells Excel … Read more
It seems like the 32-bit version of Excel was not installed. Remember that SSDT is a 32-bit IDE. Therefore, when data is access from SSDT the 32-bit data providers are used. When running the package outside of SSDT it runs in 64-bit mode (not always, but mostly) and uses the 64-bit data providers. Always keep … Read more
From Business Intelligence Studio: File->New Project->Integration Services Project Now in solution explorer there is a SSIS Packages folder, right click it and select “Add Existing Package”, and there will be a drop down that can be changed to File System, and the very bottom box allows you to browse to the file. Note that this … Read more
Summarized: INSTALL 32 bit version of Microsoft Access Database Engine 2010 Redistributable. Uninstall 64 bit version if previously installed. http://www.microsoft.com/en-us/download/details.aspx?id=13255 The Excel connection manager is trying to use the ACE OLE DB provider in order to access the Excel file when the version is above 2007 (xlsx). Although your box is 64-bit, you’re using SQL … Read more
It usually happens when Allow Nulls option is unchecked. Solution: Look at the name of the column for this error/warning. Go to SSMS and find the table Allow Null for that Column Save the table Rerun the SSIS Try these steps. It worked for me. See this link
In your Execute SQL Task, make sure SQLSourceType is set to Direct Input, then your SQL Statement is the name of the stored proc, with questionmarks for each paramter of the proc, like so: Click the parameter mapping in the left column and add each paramter from your stored proc and map it to your … Read more
I guess your problem may be related to the Express version. See at the related page the differences between Express and BimlStudio versions (Feature Comparison Chart under “Compare with BimlStudio” button): https://www.varigence.com/BimlExpress
I wouldn’t offer this answer except that you worked so hard to document it and it’s been upvoted with no answer after a month. So, here goes. Your only choices appear to be to change the data or change the tool. Probably, I am clearly doing something wrong and missing the obvious. Could someone please … Read more
To disable foreign key constraints: DECLARE @sql nvarchar(max) = N”; ;WITH x AS ( SELECT DISTINCT obj = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + ‘.’ + QUOTENAME(OBJECT_NAME(parent_object_id)) FROM sys.foreign_keys ) SELECT @sql += N’ALTER TABLE ‘ + obj + N’ NOCHECK CONSTRAINT ALL; ‘ FROM x; EXEC sys.sp_executesql @sql; To re-enable: DECLARE @sql nvarchar(max) = N”; ;WITH x AS … Read more