Thought this would be useful to some and for me to reference on future projects.
DECLARE @SEARCHSTRING VARCHAR(255)
SELECT @SEARCHSTRING = 'some string'
SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
end as [Object Type]
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR')
AND sysobjects.category = 0
I found this neat (and free) trash destination for SSIS. It’s really great as a development aid. It allows you to quickly terminate a data flow path, and does not require any configuration. It will consume the rows without any side effects, and prevents warnings or errors you may otherwise receive when executing the data flow.
You can read more and download it from here
I was working on an SSIS project to dump data from an Excel spreadsheet into a database table. However, the Excel file happened to be a beautifully formatted, colorful, presentation of charts and graphs, subtotal rows and all sorts of interesting images. The actual data I needed to dump didn’t even start until about 15 rows down and 6 rows across. My mind started wandering on some custom component that would skip rows, strip subtotal columns and basically rewrite the entire spreadsheet. But SSIS had better plans.
Thank goodness for the OpenRowset() property on the Excel Datasource component. This property allows you to specify the range to be considered by the datasource in the format Sheet1$B15:Z2000. By specifying a range, I was able to ignore all the titles and data spread all over the sheet and concentrate just on my data range. I thought skipping rows and ignoring headers / titles was going to add considerable hours to this project. I was wrong.
The next challenge was getting rid of the subtotal rows. Subtotal rows were distinguished by a cell with the word “RESULT” and an empty cell next to it. I added a Conditional Split using the ISNULL([expression]) to separate “real data rows” from “subtotal rows”. After this, the import was straightforward into a OLEDB Destination.
I had been working on a simple ETL SSIS package to simply pull data from a file, parse it, and insert it into some tables on a SQL 2005 box. In my dev and testing environment the package ran extremely fast… under a couple of seconds. But as soon as I added the package to our QA environment and schedule the package to be executed as a step on a SQL Agent job, the package took up 4-5 minutes to execute.
I knew the data loading and extracting could not be causing the problem. When I looked at what services were running on the box, I noticed SSIS was not running. I tried to start it and it errored out with “Service could not be started in timely fashion”. However, the package still ran (slow, granted, but it still executed). I finally found some insite into the problem from this KB article on Microsoft: http://support.microsoft.com/?kbid=918644 .
Apparently, SSIS needs to ping this host http://crl.microsoft.com/ in order to start correctly. Instead of applying the hotfix, I just added the line 127.0.0.1 crl.microsoft.com to the host file on the box. I attempted to start the SSIS service again and “voila”, it was running. The package is executing within a respectable time range once again.