SSIS export with funky header - 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 2005 Forums

SSIS and Import/Export (2005)

SSIS export with funky header

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2010-04-30 : 16:00:18

I am needing to set up an SSIS package that exports a pretty simple query. Simple enough.

The part I need help on, is that I need the end result to have a weird header. The place I'm sending this document needs to have in row 1 of the .CSV file the following items:

email address, A numeric code, the word "Replenishment"

Simple enough, right? I could account for this in my SQL source query. HOWEVER, after this column is when the actual data will begin. In row 2 are found the actual column headers for the data columns. There are then 5 data columns: Column1, Column2, Column3, Column4, Column5

The actual data that goes into those columns follows thereafter. Its the unique combination of needing these two headers that's giving me trouble. I hope I'm posting in the right place. I guess I could post in Transact-SQL as well.

Can anyone offer any help?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 04:28:13

is it just a matter appending the hardcoded headers as a separate query first before your actual data like

SELECT 'emailaddress' as col1,'code value' as col2,'Replenishment' AS col3
UNION ALL
your actual select query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benking9987
Posting Yak Master

124 Posts

Posted - 2010-05-03 : 14:52:39

Thanks visakh16. I'll look into this and let you know how it goes. You might have just made me a very happy man!

benking9987
Posting Yak Master

124 Posts

Posted - 2010-05-03 : 16:08:06

I tried the following SQL and received an error:


SELECT 'email' as col1,'codevalue' as col2,'Replenishment' AS col3
UNION ALL
SELECT Master_Child.OS_Child AS vendor_sku, (CASE when OS_Active='0' then 'N' else 'Y' end) AS Replenishable, (Case when LeadTime is Null then '30' else LeadTime end) AS lead_days, CONVERT(VARCHAR(10), GETDATE()+183, 101) AS landing_month, '0' AS quantity
FROM Master_Child
WHERE (((Master_Child.OS_Child) Is Not Null));

The error reads:

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I've never used a UNION ALL statement, so I'm unsure how to troubleshoot. Any ideas?

benking9987
Posting Yak Master

124 Posts

Posted - 2010-05-03 : 16:19:14

I actually think I'm going to use a temptable to put all the data together that I need and then use SSIS to export that data.

Let me know if you think this is a stupid idea.

Thanks again for your help and willingness to help.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-03 : 16:40:07

quote:
Originally posted by benking9987

I tried the following SQL and received an error:

SELECT 'email' as col1,'codevalue' as col2,'Replenishment' AS col3
UNION ALL
SELECT Master_Child.OS_Child AS vendor_sku, (CASE when OS_Active='0' then 'N' else 'Y' end) AS Replenishable, (Case when LeadTime is Null then '30' else LeadTime end) AS lead_days, CONVERT(VARCHAR(10), GETDATE()+183, 101) AS landing_month, '0' AS quantity
FROM Master_Child
WHERE (((Master_Child.OS_Child) Is Not Null));

The error reads:

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I've never used a UNION ALL statement, so I'm unsure how to troubleshoot. Any ideas?

You have defined 3 fields in the header, but you have 4 fields in the second SELECT. You need to add one more field in the header.

Or, is it just a random header with these 3 fields alone?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources