asked on

View in SSIS package takes long time to run

Hello All Experts;

I have a problem executing a view in an SSIS package. Doing some testing I created a new package with only one Data Flow Task and in that DFT I have one Data Flow a OLE DB Source and that source is the view. Very simple, but here’s the rub. As soon as I select the view in the OLE DB Source Editor things go into slow motion. It may take up to a minute to do a right click and if I execute the DFT it takes 30 minutes to run. In SSM it only takes about 2 seconds to run. The view is below:

USE [ButterNut]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Eligible_Seeds_vw]
AS
SELECT DISTINCT TOP (100) PERCENT
temp_l.LetterC,
temp_l.Status,
temp_l.lsr_so,
temp_l.MailFreq,
temp_l.Clients,
temp_l.ClientCd,
temp_l.[Bus Unit],
slc.seed_LetterC_id,
sb.seed_bizname_ID,
slc.last_seed_date,
sb.business_name,
sb.address1,
sb.address2,
sb.city,
sb.state,
sb.zipcode,
sb.zip_4,
sb.country,
sb.mobilenumber,
sb.company,
sb.seed_type_group,
CASE
      WHEN temp_l.lsr_so = 1 THEN 1
      WHEN temp_l.mailfreq = 'One-Time' THEN 2
      WHEN temp_l.mailfreq = 'Monthly' THEN 3
      WHEN temp_l.mailfreq = 'Bi-Weekly' THEN 4
      WHEN temp_l.mailfreq = 'Weekly' THEN 5
      WHEN temp_l.mailfreq = 'Daily' THEN 6
END AS seed_order
FROM dbo.MailM AS temp_l
INNER JOIN dbo.Seed_LetterCs AS slc
ON temp_l.LetterC = slc.LetterC
INNER JOIN dbo.Seed_Clients AS seed_c
ON temp_l.Clients = seed_c.client_name
AND temp_l.ClientCd = seed_c.client_code
AND temp_l.[Bus Unit] = seed_c.business_unit
AND slc.seed_client_id = seed_c.seed_client_id
inner join ButterNut.dbo.iMaster as im
on temp_l.LetterC = im.mailLetterC
INNER JOIN dbo.Seed_BizNames AS sb
ON seed_c.seed_bizname_id = sb.seed_bizname_ID
AND sb.active_date <= GETDATE()
AND ISNULL(sb.expiration_date, '2222-01-01') > GETDATE()
AND
(
      ISNULL(temp_l.lsr_so, 0) = 0
      AND sb.seed_type_group = 'A'
      AND GETDATE() >= DATEADD(DD, 90, slc.last_seed_date) OR temp_l.lsr_so = 1
      AND sb.seed_type_group IN ('A', 'B')
)
AND temp_l.Status = 'ACTIVE'
and im.LetterCstatus='ACTIVE'
--and temp_l.LetterC in (select distinct mailLetterC from ButterNut.dbo.iMaster)
ORDER BY temp_l.lsr_so DESC, temp_l.Status, temp_l.LetterC

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[32] 4[29] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "temp_l"
Begin Extent =
Top = 6
Left = 38
Bottom = 125
Right = 201
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "slc"
Begin Extent =
Top = 6
Left = 239
Bottom = 125
Right = 420
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "seed_c"
Begin Extent =
Top = 6
Left = 458
Bottom = 125
Right = 629
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "sb"
Begin Extent =
Top = 6
Left = 667
Bottom = 125
Right = 841
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 23
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Eligible_Seeds_vw'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1_1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[32] 4[29] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "temp_l"
Begin Extent =
Top = 6
Left = 38
Bottom = 125
Right = 201
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "slc"
Begin Extent =
Top = 6
Left = 239
Bottom = 125
Right = 420
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "seed_c"
Begin Extent =
Top = 6
Left = 458
Bottom = 125
Right = 629
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "sb"
Begin Extent =
Top = 6
Left = 667
Bottom = 125
Right = 841
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 23
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Eligible_Seeds_vw'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Eligible_Seeds_vw'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2_1', @value=N' Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Eligible_Seeds_vw'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Eligible_Seeds_vw'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount_1', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Eligible_Seeds_vw'
GO

edtechdba

It may be worth your time to check out this forum post:

SSIS OLE DB source connection it too slow connecting to SQL Server database
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/335b3a34-c9dd-45ce-a7af-21237dc8b88d/ssis-ole-db-source-connection-it-too-slow-connecting-to-sql-server-database

"Please try to switch to Offline Mode (in SSIS menu) to avoid connecting to external data sources (where most of the time is spent). Only basic (local) validation is performed, which is usually very fast."

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

wdbates

ASKER

Hello edtechdba;
The client is using this package in Debug mode so opening in offline is not going to work. But thanks for your input.

acperkins;
I have removed most of the junk, but the view was running in about 1 second as it was. The strange thing about this is that sometimes it runs in about 5 minutes and other in about 30 minutes. Also while editing the package I receive the too common message that Visual Studio is busy.

I going to remove the view and write the query in the Execute SQL Task and see what happens.

Will keep you informed.

edtechdba

How about setting the DelayValidation property in the Data Flow Task to True?

Here's a ref for changing this property value:
http://sqlmag.com/sql-server-integration-services/5-tips-developing-sql-server-integration-services-packages

wdbates

ASKER

acperkins did a great job with detail explanations.