Trying to use output of Temp Table ISS SSIS PAckage

Question

Post reply

Trying to use output of Temp Table ISS SSIS PAckage

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 4, 2011 at 2:39 pm

#262329

SELECT Comp.CompanyName, LEFT(Comp.CompanyNumber, 6) AS [Account Number], Quote.QuoteID,
MIN(Comp.PhoneNumber) AS Phone, MIN(CoTyp.AgencyTypeDescription) AS Affinity,
MIN(MktTer.MarketingTerritoryID) AS Territory, MIN(Co.CountyName) AS County,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 1 THEN 1
ELSE 0
END AS PriorYear,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentYear,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentMonth,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousMonth,
CASE Datediff(week, SaleTransferredTSTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 2 THEN 1
ELSE 0
END AS TwoWeeksPrior,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 3 THEN 1
ELSE 0
END AS ThreeWeeksPrior
INTO #TXActivityTemp
FROM Quote AS Quote WITH (NOLOCK)
INNER JOIN Company AS Comp WITH (NOLOCK) ON Quote.CompanyID = Comp.CompanyID
FULL OUTER JOIN dbo.CompanyType AS CoTyp WITH (NOLOCK) ON Comp.CoTypeID = CoTyp.CoTypeID
FULL OUTER JOINCounty AS Co WITH (NOLOCK) ON Quote.CountyID = Co.CountyID
FULL OUTER JOIN COMarketingTerritory AS MktTer ON Quote.CompanyID = MktTer.COmpanyyID
WHERE (Quote.SaleTransferredTS >= '2010-01-01')
GROUP BY LEFT(Comp.CompanyNumber, 6), Comp.CompanyName, Quote.QuoteID,SaleTransferredTS
HAVING LEFT(Comp.CompanyNumber, 6) = '240002')
SELECT CompanyName, [Account Number], MIN(Phone) AS Phone,
MIN( Affinity) AS Affinity,MIN(Territory) AS Territory,
MIN(County) AS County,SUM(PriorYear)AS PriorYear, SUM(CurrentYear)AS CurrentYear,
SUM(CurrentMonth) AS CurrentMonth, SUM(PreviousMonth) AS PreviousMonth,SUM(CurrentWeek) AS CurrentWeek,
SUM(TwoWeeksPrior) AS TwoWeeksPrior, SUM(ThreeWeeksPrior) AS ThreeWeeksPrior
FROM #TXActivityTemp
GROUP BY [Account Number],CompanyName
Currently I have an OLEDB Data Source Where I insert into the temp table.
Then I created an OLEDB Command but I don't think that I'm doing the right thing.
I have a Data Conversion Task to convert the Columns to Unicode before the load into the OLEDB Excel Destination Object.
I set the following properties:
Set RetainSameConnection=TRUE on the connection manager and set DelayValidation=TRUE
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I'm not sure what you are trying to accomplish. Are you just trying to move the data into Excel?

Can't you just use the SQL Statement in the OLE DB Source and have the data available in the data flow?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Jack Corbett (11/4/2011)
I'm not sure what you are trying to accomplish. Are you just trying to move the data into Excel?
Can't you just use the SQL Statement in the OLE DB Source and have the data available in the data flow?

Can I do that?

The Source returns records from the SELECT INTO and then an Aggregate from the Temp Table.

Don't I need to separate the two?

I'm having issues mapping the Data, etc.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Yeah, SSIS might have issues with determining data types and such because of the temp table, but you never know until you try. There are some ways to "trick" SSIS into seeing the meta data. When I remember what they are I'll let you know

If this is more than a one-off you might consider making the temp table a permanent table that you load using an Execute SQL Task and then select from it in your source in your data flow.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Here's a post by Jamie Thompson about it, http://consultingblogs.emc.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Jack Corbett (11/4/2011)
Here's a post by Jamie Thompson about it, http://consultingblogs.emc.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

Thanks Jack.

That is an excellent article

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kramaswamy SSCoach Points: 18181 More actions · Answer 6

Why not just do this, as your source:

WITH cte AS

(

SELECT Comp.CompanyName, LEFT(Comp.CompanyNumber, 6) AS [Account Number], Quote.QuoteID,

MIN(Comp.PhoneNumber) AS Phone, MIN(CoTyp.AgencyTypeDescription) AS Affinity,

MIN(MktTer.MarketingTerritoryID) AS Territory, MIN(Co.CountyName) AS County,

CASE Datediff(yy, SaleTransferredTS, GetDate())

WHEN 1 THEN 1

ELSE 0

END AS PriorYear,

CASE Datediff(yy, SaleTransferredTS, GetDate())

WHEN 0 THEN 1

ELSE 0

END AS CurrentYear,

CASE Datediff(mm, SaleTransferredTS, GetDate())

WHEN 0 THEN 1

ELSE 0

END AS CurrentMonth,

CASE Datediff(mm, SaleTransferredTS, GetDate())

WHEN 0 THEN 1

ELSE 0

END AS PreviousMonth,

CASE Datediff(week, SaleTransferredTSTS, GetDate())

WHEN 0 THEN 1

ELSE 0

END AS CurrentWeek,

CASE Datediff(week, SaleTransferredTS, GetDate())

WHEN 0 THEN 1

ELSE 0

END AS PreviousWeek,

CASE Datediff(week, SaleTransferredTS, GetDate())

WHEN 2 THEN 1

ELSE 0

END AS TwoWeeksPrior,

CASE Datediff(week, SaleTransferredTS, GetDate())

WHEN 3 THEN 1

ELSE 0

END AS ThreeWeeksPrior

FROM Quote AS Quote WITH (NOLOCK)

INNER JOIN Company AS Comp WITH (NOLOCK) ON Quote.CompanyID = Comp.CompanyID

FULL OUTER JOIN dbo.CompanyType AS CoTyp WITH (NOLOCK) ON Comp.CoTypeID = CoTyp.CoTypeID

FULL OUTER JOINCounty AS Co WITH (NOLOCK) ON Quote.CountyID = Co.CountyID

FULL OUTER JOIN COMarketingTerritory AS MktTer ON Quote.CompanyID = MktTer.COmpanyyID

WHERE (Quote.SaleTransferredTS >= '2010-01-01')

GROUP BY LEFT(Comp.CompanyNumber, 6), Comp.CompanyName, Quote.QuoteID,SaleTransferredTS

HAVING LEFT(Comp.CompanyNumber, 6) = '240002')

)

SELECT CompanyName, [Account Number], MIN(Phone) AS Phone,

MIN( Affinity) AS Affinity,MIN(Territory) AS Territory,

MIN(County) AS County,SUM(PriorYear)AS PriorYear, SUM(CurrentYear)AS CurrentYear,

SUM(CurrentMonth) AS CurrentMonth, SUM(PreviousMonth) AS PreviousMonth,SUM(CurrentWeek) AS CurrentWeek,

SUM(TwoWeeksPrior) AS TwoWeeksPrior, SUM(ThreeWeeksPrior) AS ThreeWeeksPrior

FROM cte

GROUP BY [Account Number],CompanyName

And then you don't need to worry about temp tables

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

kramaswamy (11/7/2011)
Why not just do this, as your source:
WITH cte AS
(
SELECT Comp.CompanyName, LEFT(Comp.CompanyNumber, 6) AS [Account Number], Quote.QuoteID,
MIN(Comp.PhoneNumber) AS Phone, MIN(CoTyp.AgencyTypeDescription) AS Affinity,
MIN(MktTer.MarketingTerritoryID) AS Territory, MIN(Co.CountyName) AS County,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 1 THEN 1
ELSE 0
END AS PriorYear,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentYear,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentMonth,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousMonth,
CASE Datediff(week, SaleTransferredTSTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 2 THEN 1
ELSE 0
END AS TwoWeeksPrior,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 3 THEN 1
ELSE 0
END AS ThreeWeeksPrior
FROM Quote AS Quote WITH (NOLOCK)
INNER JOIN Company AS Comp WITH (NOLOCK) ON Quote.CompanyID = Comp.CompanyID
FULL OUTER JOIN dbo.CompanyType AS CoTyp WITH (NOLOCK) ON Comp.CoTypeID = CoTyp.CoTypeID
FULL OUTER JOINCounty AS Co WITH (NOLOCK) ON Quote.CountyID = Co.CountyID
FULL OUTER JOIN COMarketingTerritory AS MktTer ON Quote.CompanyID = MktTer.COmpanyyID
WHERE (Quote.SaleTransferredTS >= '2010-01-01')
GROUP BY LEFT(Comp.CompanyNumber, 6), Comp.CompanyName, Quote.QuoteID,SaleTransferredTS
HAVING LEFT(Comp.CompanyNumber, 6) = '240002')
)
SELECT CompanyName, [Account Number], MIN(Phone) AS Phone,
MIN( Affinity) AS Affinity,MIN(Territory) AS Territory,
MIN(County) AS County,SUM(PriorYear)AS PriorYear, SUM(CurrentYear)AS CurrentYear,
SUM(CurrentMonth) AS CurrentMonth, SUM(PreviousMonth) AS PreviousMonth,SUM(CurrentWeek) AS CurrentWeek,
SUM(TwoWeeksPrior) AS TwoWeeksPrior, SUM(ThreeWeeksPrior) AS ThreeWeeksPrior
FROM cte
GROUP BY [Account Number],CompanyName
And then you don't need to worry about temp tables

Kramaswamy,

Sweet, I will give that a shot.

I'm glad that you came up with this because I'm struggling getting the temp table to work in SSIS.

Thanks again everyone for your help.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kramaswamy SSCoach Points: 18181 More actions · Answer 8

The last option, of course, if the cte isn't sufficient due to the size of the query or the need for indexes or something, is to create a real table, and then at the start of your process, truncate the table, populate it, and then use it for your output.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

It works perfectly, thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Edited: I spoke too soon, the problem is with my CASE Statements.

This is unfortunate but when I ran the SQL that included the Temp Table I thought that I was getting the right numbers.

I run it today and it is wrong nothing changed but perhaps, I'm mistaken.

I should get 3 Records For OCT 2011 & 2 for SEP 2011.

Could it have something to do with the way I'm doing the DateDiff in the CASE Statement?

Regarless when I run the following Statement:

SELECT Comp.CompName, LEFT(Comp.CompanyNumber, 6) AS [Account Number], Quote.QuoteID,

SalesTransferredTS

,Datediff(week, SalesTransferredTS, GetDate())

FROM tblQuote AS Quote WITH (NOLOCK)

INNER JOIN tblCompany AS Agcy WITH (NOLOCK)ON Quote.CompanyID = Comp.CompID

WHERE LEFT(Comp.CompamyNumber, 6)= 240002

AND CompanyTransferredTS >= '2011-01-01'

ORDER BY CompanyTransferredTS DESC

I get the following result:

CompanyNameAccount QuoteIDSalesTransferredTS WeekDifference

Joe's2400027114972011-10-134

Joe's2400027115042011-10-134

Joe's2400027115102011-10-134

Joe's2400027122702011-09-29 6

Joe's2400027030122011-09-05 9

Joe's2400027017632011-08-09 13

Joe's2400026947382011-08-02 14

Joe's2400026944002011-07-06 18

Joe's2400026900032011-06-24 20

Joe's2400026855012011-06-13 21

Joe's2400026867202011-06-07 22

Joe's2400026783872011-05-11 26

Joe's2400026682132011-04-26 28

Joe's2400026684382011-04-08 31

Joe's2400026517672011-03-05 36

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kramaswamy SSCoach Points: 18181 More actions · Answer 11

Honestly I don't really think you're giving enough information here for me to be able to answer that question.

What DateDiff in the case statement are you referring to? The one in the previous page? If so, there's like 8 of them, so which one is not producing the correct value?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 12

I did notice what appears to be errors in your case statements for PreviousMonth and PreviousWeek. You have the same CASE condition for PreviousMonth as CurrentMonth and the same CASE condition for PreviousWeek as CurrentWeek. That might be causing the issue.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

Yes, I had errors in my CASE Statement and I corrected them.

I noticed after making the post.

Sorry, i should have edited my post sooner.

I appreciate the help

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Trying to use output of Temp Table ISS SSIS PAckage

Cookies on SQLServerCentral