January 14, 2010 at 2:36 pm
Hi,
I have an agent job I created which loads a few tables with data, aggregates it, and loads that data into another table. I then created an SSIS package which executes a sql script to put the data into a machine readable text file. Problem is when the SSIS package runs it gives me this error: Error at Data Flow Task [Source-Query[1]]: there is a data source column with no name.
Normally I would execute my script in a query window and save results to file, and everything was good. Once I tried to automate this process that is when I ran into issues. Below is what the results in the text file normally look like:
Sample output:
0020100109 WEEKLY DATA LOAD
00B900228 30002684901000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000000000000000000000000000000000000
00A900228 20002693546000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000016900+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000016900000000000000000000000000000000000
00G7W1933 20002695665000+00000705000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000705000000000000000000000000000000000000
00G7W1933 20002695720000+00000090950+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000141200000000000000000000000000000000000
00G7W2384 50002712016000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000024400+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000024400000000000000000000000000000000000
00G7W2384 50002712018000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000013700+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000013700000000000000000000000000000000000
00G7W2384 50002712051000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000047000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000000000+00000047000000000000000000000000000000000000
This is the script in my SSIS package that formats the data.
script:
SET NOCOUNT ON
-- Header Record
SELECT '00' + CONVERT(VARCHAR(8),GETDATE(),112)+
' WEEKLY DATA FEED'+
REPLICATE(' ', 190)
UNION
-- FULL Level Records
SELECT '01'+
REPLICATE(' ', 7 - LEN(CAST([CNumber] AS VARCHAR(7)))) + CAST([CNumber] AS VARCHAR(7)) +
REPLICATE(' ', 3 - LEN(CAST([ClNumber] AS VARCHAR(3)))) + CAST([ClNumber] AS VARCHAR(3)) +
REPLICATE(0, 10 - LEN(CAST([BCN] AS VARCHAR(10)))) + CAST([BCN] AS VARCHAR(10)) +
REPLICATE(0, 3 - LEN(CAST([Revision Number] AS VARCHAR(3)))) + CAST([Revision Number] AS VARCHAR(3)) +
CASE WHEN SUM([Override]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([Override])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([Override])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([RecAmount]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SFSched])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([SFSched])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([SFDuplicates]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SFDuplicates])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([SFDuplicates])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([SPDuplicates]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SPDuplicates])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([SPDuplicates])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([SMiscellaneous]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SMiscellaneous])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([SMiscellaneous])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([SMRT]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SMRT])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([SMRT])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([SPPO]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SPPO])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([SPPO])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([SSRules]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SSRules])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([SSRules])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([SUCR]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SUCR])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([SUCR])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([SAPA]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SAPA])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([Savings-APA])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([Savings-Code_Review]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([SCReview])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([SCReview])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([TSavings]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([TSavings])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([TSavings])) AS VARCHAR(11)),'.','') +
LEFT([Filler],33)
FROM Work_JTR.dbo.billData B
LEFT JOIN Work_JTR.dbo.APA T ON T.BILLIDNO = B.[BCN]
GROUP BY [CNumber], [CLNumber], [BCN], [RNumber], [Filler]
UNION
-- Trailer Record
SELECT '99' +
REPLICATE(0, 10 - LEN(CAST((COUNT(DISTINCT [BCN])+ COUNT([Line-Seq-Number])) AS VARCHAR(10)))) + CAST(COUNT(DISTINCT [BCN])+ COUNT([Line-Seq-Number]) AS VARCHAR(10)) +
REPLICATE(0, 10 - LEN(CAST(COUNT(DISTINCT [BCN]) AS VARCHAR(10)))) + CAST(COUNT(DISTINCT [BCN]) AS VARCHAR(10)) +
REPLICATE(0, 10 - LEN(CAST(COUNT([Line-Seq-Number]) AS VARCHAR(10)))) + CAST(COUNT([Line-Seq-Number]) AS VARCHAR(10)) +
CASE WHEN SUM([TSavings]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([TSavings])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([TSavings])) AS VARCHAR(11)),'.','') +
CASE WHEN SUM([TSavings]) >= 0
THEN '+' ELSE '-' END +
REPLICATE(0, 11 - LEN(REPLACE(CAST(ABS(SUM([TSavings])) AS VARCHAR(11)),'.',''))) + REPLACE(CAST(ABS(SUM([TSavings])) AS VARCHAR(11)),'.','') +
REPLICATE('0',194)
FROM Work_JTR.dbo.billData
ORDER BY 1
SET NOCOUNT OFF
Any ideas how to get around that error?
Thanks,
Code
January 15, 2010 at 1:03 am
I'd be tempted to heed the error message and give the column a name:
SELECT '00' + CONVERT(VARCHAR(8),GETDATE(),112)+
' WEEKLY DATA FEED'+
REPLICATE(' ', 190) Col1
--etc etc
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 15, 2010 at 9:57 am
Thanks. I labled the column last night, and it got me a little futher, however I was getting a new error stating no column was mapped to the text file, so it wouldnt work. I ended up creating another table and storing the formatted data there, and then created a new package based off that table and it works.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply