Hi, I have a question regarding SSIS 2008 and how it handles views. I hope the answer for this question will be a quick "Get a hotfix" heh. I am experiencing a very odd behavior with setting up a View as a data-source in OLE DB. I have set up a a very simple package (for experimenting) with a Data Flow Task that has only two components, Data-Source and Data-Destination. I have set the data-source to a view and the destination to a table in the same database. Note: I am developing the package on the server itself. Issues: While trying to preview data-source I get an error, "Query Timeout expired (Microsoft SQL Server Native Client 10.0) When executing the package Validation, Pre-Execute phases are complete but no rows are passing through the buffer. I have waited over 12 hours before stop the job. Checking the monitoring verified that IO was on 1. And that no object was locked , which may have caused the package to run so long or not to retrieve data. When connecting to SSMS and doing export of data from the view - it also retrieves no data and the process seems to be stuck. Additional data: The view holds 30,000 rows. When selecting the view using SSMS it takes 11 seconds. When using "SELECT * INTO [...] FROM [Source(View)]" the query takes 20 seconds to run. and the destination table is created and filled with data. SQL information: Microsoft SQL Server Management Studio 10.0.2531.0 Microsoft Analysis Services Client Tools 10.0.1600.22 Microsoft Data Access Components (MDAC) 6.1.7600.16385 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 8.0.7600.16385 Microsoft .NET Framework 2.0.50727.4952 Operating System 6.1.7600 VSTO information: Microsoft Visual Studio 2008 Version 9.0.30729.1 SP Microsoft .NET Framework Version 3.5 SP1 Installed Edition: IDE Standard Hotfix for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB945282) KB945282 This hotfix is for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU. If you later install a more recent service pack, this hotfix will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/945282. Hotfix for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB946040) KB946040 This hotfix is for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU. If you later install a more recent service pack, this hotfix will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/946040. Hotfix for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB946308) KB946308 This hotfix is for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU. If you later install a more recent service pack, this hotfix will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/946308. Hotfix for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB946344) KB946344 This hotfix is for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU. If you later install a more recent service pack, this hotfix will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/946344. Hotfix for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB946581) KB946581 This hotfix is for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU. If you later install a more recent service pack, this hotfix will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/946581. Hotfix for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB947173) KB947173 This hotfix is for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU. If you later install a more recent service pack, this hotfix will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/947173. Hotfix for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB947540) KB947540 This hotfix is for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU. If you later install a more recent service pack, this hotfix will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/947540. Hotfix for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB947789) KB947789 This hotfix is for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU. If you later install a more recent service pack, this hotfix will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/947789. Security Update for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB972222) KB972222 This security update is for Microsoft Visual Studio 2008 Shell (integrated mode) - ENU. If you later install a more recent service pack, this security update will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/972222. SQL Server Analysis Services Microsoft SQL Server Analysis Services Designer Version 10.0.2531.0 SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 10.0.2531.0 ((Katmai_PCU_Main).090329-1015 ) SQL Server Reporting Services Microsoft SQL Server Reporting Services Designers Version 10.0.2531.0 I am clueless! plz help.

I would try the following: add a clustered index on the primary key in the destination table choose "table or view - fast load" on the Data Destination component to set "rows per batch" to 250-500 on the Data Destination component. Sergei

Hi Tzur, Have you tried the following? 1. In the OLE DB Data source, under "Data Access mode", Select "SQL command". 2. In the "SQL command text", write the query "SELECT col1, col2, col3, col4 FROM View_Name". 3. Run the preview and see if you can get the data. Regards Ramesh

Hi Ramesh, This is what I am doing now. I changed into a SQL command and used the View query. Running other that mode executes the package in less than 20 seconds. but when working against the view itself, it can take hours (possibly days, But I don't want to even try that!). I can't relay on using the SQL command - even tho it helps in the immediate situation I am in- because I don't want to open the DTSX every time the query WHERE changes. there is definitely something wrong in the SQL server, but we have no idea what.

Hi Tzur, The fastest way is yes, to use SQL Task SELECT * FROM VIEW, also try to use the Data Migration Wizard (DTSWizard.exe) and see if the performance is as slow.Arthur My Blog

Tzur, Ramesh was refering to the Data access mode in the OLE DB source. This can be set as below: A table or view. A table or view specified in a variable. The results of an SQL statement. The query can be a parameterized query. The results of an SQL statement stored in a variable. Instead of using "A table or view" choose the option "The results of a SQL statement" and set the sql statement to "SELECT col1, col2 ... FROM View_Name" rather than using the view definition which points to the underlying table. This should peform better as when in table/view access mode, SSIS will interogate the database to determine the number of columns and names each time it connects which when used in a view can be expensive. Similarly you should not use "SELECT * FRom View_Name" as it does the same thing.

Tzur, Ramesh was refering to the Data access mode in the OLE DB source. This can be set as below: A table or view. A table or view specified in a variable. The results of an SQL statement. The query can be a parameterized query. The results of an SQL statement stored in a variable. Instead of using "A table or view" choose the option "The results of a SQL statement" and set the sql statement to "SELECT col1, col2 ... FROM View_Name" rather than using the view definition which points to the underlying table. This should peform better as when in table/view access mode, SSIS will interogate the database to determine the number of columns and names each time it connects which when used in a view can be expensive. Similarly you should not use "SELECT * FRom View_Name" as it does the same thing.

Hi Karl, Yeah I know that was Ramesh meant. That is also what I eventually did. I still find it frustrating. Sergei, I tried limiting the rows per batch and it didn't work. Anyway, I'll keep using the "SELECT Col1, Col2 ... FROM View_Name" .