SSIS - Script task how to??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS - Script task how to???

Author

Topic

999baz
Starting Member

1 Post

Posted - 2009-10-22 : 02:04:45

Hi Any thoughts on how to achieve this in SSIS?

i have Monthly Upload summary table as shown via script below.
Each month Qtys loaded grouped by source (SHIP + ONSITE) are added to this table from a staging table.
An Aggragte tranform helps me do that in a dataflow task.

What i would like to do is check that the totals in the current months load are within +-10% of the previous upload for each source?(SHIP + ONSITE)
If they are not than the task should fail? thus the next contanier or task shouldn't proceed.

NOw i understand there are many ways to do this, 1) create my own temp table or destination table, use Lookup transform, conditional split etc

But i have never used scripting SSIS, so it would great if some one can advise me how to script this in visual basic.net and implement it using script task.+ any useful links/online resources which will help a complete novice like yourstruly (although i am familiar with OOB like Java,C++) to get a start on SQL VB.net scripting how it all works i would like to start from a novice/begginner & be in a position soon to expolre more advanced things we can do via Scripting +sql together with VB.net. BUt can't find good stuuf online to learn?



--===== Create the test table with 
CREATE TABLE [dbo].[Testing_Learn_SummaryTable](
        [RecordNo] [int] IDENTITY(1,1) NOT NULL,
        [Type] [varchar](15) NULL,
        [Date] [date] NULL,
        [Source] [varchar](10) NULL,
        [Status] [varchar](15) NULL,
        [Qty] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
--===== Insert the test data into the test table

INSERT INTO dbo.Testing_Learn_SummaryTable
            (Type,Date,Source,Status,Qty)

SELECT  'Stage',        cast('2009-08-19' as DATE),     'SHIP'  ,' ',150 UNION ALL
SELECT  'Stage',        cast('2009-08-19' as  date),    'ONSITE',' ',110 UNION ALL
SELECT  'Stage',        cast('2009-09-19' as DATE),     'SHIP'  ,' ',149 UNION ALL
SELECT  'Stage',        cast('2009-09-19' as DATE),     'ONSITE',' ',105 UNION ALL
SELECT  'Stage',        cast('2009-10-19' as date),     'SHIP', ' ',134 UNION ALL
SELECT  'Stage',        cast('2009-10-19' as date),     'ONSITE',' ',100

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [RecordNo]
      ,[Type]
      ,[Date]
      ,[Source]
      ,[Status]
      ,[Qty]
  FROM [ANZ].[dbo].[Testing_Learn_SummaryTable]

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-22 : 06:13:43

quote:

check that the totals in the current months load are within +-10% of the previous upload for each source?(SHIP + ONSITE)

I'd suggest you perform the tolerance check in a SQL Server object (e.g. stored procedure or function), using the Monthly Upload summary table and the staging table.

While it is always good to learn new techniques, this is not the best place to use a SSIS script task.

You can find some sample code here: http://www.codeplex.com/MSFTISProdSamples/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources