January 30, 2012 at 4:35 pm
Hi all,
I am trying to create a report and export to CSV file without column headers.When i run the query from SSMS and 'SAVE RESULTS AS..' CSV or text file it saves without headers.But when i am createing a job to run the query ,it exports with headers.I sat up OUTPUT Format (from Tools\Query Results\results To Text\Comma delimeted\unchecked Include column headers ....It still exports with headres.Any ideas how to do this?
January 31, 2012 at 12:06 am
vardan.hakopian (1/30/2012)
Hi all,I am trying to create a report and export to CSV file without column headers.When i run the query from SSMS and 'SAVE RESULTS AS..' CSV or text file it saves without headers.But when i am createing a job to run the query ,it exports with headers.I sat up OUTPUT Format (from Tools\Query Results\results To Text\Comma delimeted\unchecked Include column headers ....It still exports with headres.Any ideas how to do this?
Try it with SSIS, there is an option to drop the headers.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 2:12 am
:hehe:
To eliminate column title row that you see before the data, use NoHeader option:
http://msdn2.microsoft.com/en-us/library/ms155365.aspx
You can use it with URL access (add &rc:NoHeader=true to report url), or, for RS 2005, you can configure your CSV rendering extension to always ommit the header row.
CSV renderer doesn't stack data from 2 different data regions, so your trick with table above matrix won't get you the result you are looking for.
from this discussion
http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/90967670-14af-42a5-a0a4-9b0dfe60e99c/[/url]
January 31, 2012 at 2:17 am
johnitech.itech (1/31/2012)
:hehe:To eliminate column title row that you see before the data, use NoHeader option:
http://msdn2.microsoft.com/en-us/library/ms155365.aspx
You can use it with URL access (add &rc:NoHeader=true to report url), or, for RS 2005, you can configure your CSV rendering extension to always ommit the header row.
CSV renderer doesn't stack data from 2 different data regions, so your trick with table above matrix won't get you the result you are looking for.
from this discussion
http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/90967670-14af-42a5-a0a4-9b0dfe60e99c/[/url]
I believe this solution is for SSRS. We're talking about SQL Agent here.
(although SSRS would be a viable alternative)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 11:20 am
Thank you guys all for for your responds.
I used Export Data to create SSIS pakage.I saved (file system) then created a Agent job.But job fails.Here is the error message:
Message
Executed as user: NT AUTHORITY\NETWORK SERVICE. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:13:22 AM Error: 2012-01-31 10:13:22.40 Code: 0xC0011007 Source: {25854D68-FAF9-441B-8EDF-ECBB2ECE1FC8} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2012-01-31 10:13:22.40 Code: 0xC0011002 Source: {25854D68-FAF9-441B-8EDF-ECBB2ECE1FC8} Description: Failed to open package file "C:\Documents and Settings\Administrator\Desktop\testSSIS\testSSIS\bin\Deployment\testSSIS.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "C:\Documents and Settings\Administrator\Desktop\testSSIS\testSSIS\bin\Deployment\testSSIS.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:\Documents and Settings\Administrator\Desktop\testSSIS\testSSIS\bin\Deployment\testSSIS.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. Source: {25854D68-FAF9-441B-8EDF-ECBB2ECE1FC8} Started: 10:13:22 AM Finished: 10:13:22 AM Elapsed: 0.031 seconds. The package could not be found. The step failed.
January 31, 2012 at 12:00 pm
i loged in as 'sa'.I changed the Protection level and it still fails.When changing Protection level do i have to rebuild the pakage and then run Agent job?
January 31, 2012 at 12:02 pm
Did you click on the link I gave you about troubleshooting SSIS deployments?
Jared
CE - Microsoft
January 31, 2012 at 12:07 pm
yes
January 31, 2012 at 12:32 pm
Yes, you should rebuild once you change and save properties. Which protection level is it now set at?
Jared
CE - Microsoft
January 31, 2012 at 12:34 pm
DontSaveSensitive
January 31, 2012 at 12:40 pm
DontSaveSensitive
January 31, 2012 at 1:07 pm
You really are not giving enough information here. Did you rebuild the project after saving? Have you verified all the other scenarios in the article I gave you? Is the error the same now as it was before you changed ProtectionLevel?
Jared
CE - Microsoft
January 31, 2012 at 1:38 pm
vardan.hakopian (1/31/2012)
Thank you guys all for for your responds.I used Export Data to create SSIS pakage.I saved (file system) then created a Agent job.But job fails.Here is the error message:
Message
Executed as user: NT AUTHORITY\NETWORK SERVICE. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:13:22 AM Error: 2012-01-31 10:13:22.40 Code: 0xC0011007 Source: {25854D68-FAF9-441B-8EDF-ECBB2ECE1FC8} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2012-01-31 10:13:22.40 Code: 0xC0011002 Source: {25854D68-FAF9-441B-8EDF-ECBB2ECE1FC8} Description: Failed to open package file "C:\Documents and Settings\Administrator\Desktop\testSSIS\testSSIS\bin\Deployment\testSSIS.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "C:\Documents and Settings\Administrator\Desktop\testSSIS\testSSIS\bin\Deployment\testSSIS.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:\Documents and Settings\Administrator\Desktop\testSSIS\testSSIS\bin\Deployment\testSSIS.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. Source: {25854D68-FAF9-441B-8EDF-ECBB2ECE1FC8} Started: 10:13:22 AM Finished: 10:13:22 AM Elapsed: 0.031 seconds. The package could not be found. The step failed.
Does the SQL Server Agent account have permissions to access the Admin's Desktop?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 1:45 pm
Koen Verbeeck (1/31/2012)
vardan.hakopian (1/31/2012)
Thank you guys all for for your responds.I used Export Data to create SSIS pakage.I saved (file system) then created a Agent job.But job fails.Here is the error message:
Message
Executed as user: NT AUTHORITY\NETWORK SERVICE. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:13:22 AM Error: 2012-01-31 10:13:22.40 Code: 0xC0011007 Source: {25854D68-FAF9-441B-8EDF-ECBB2ECE1FC8} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2012-01-31 10:13:22.40 Code: 0xC0011002 Source: {25854D68-FAF9-441B-8EDF-ECBB2ECE1FC8} Description: Failed to open package file "C:\Documents and Settings\Administrator\Desktop\testSSIS\testSSIS\bin\Deployment\testSSIS.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "C:\Documents and Settings\Administrator\Desktop\testSSIS\testSSIS\bin\Deployment\testSSIS.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:\Documents and Settings\Administrator\Desktop\testSSIS\testSSIS\bin\Deployment\testSSIS.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. Source: {25854D68-FAF9-441B-8EDF-ECBB2ECE1FC8} Started: 10:13:22 AM Finished: 10:13:22 AM Elapsed: 0.031 seconds. The package could not be found. The step failed.
Does the SQL Server Agent account have permissions to access the Admin's Desktop?
Ah ha! I should have looked closer at the error to see it was stored in the file system instead of msdb.. Of course the article I referenced as method 1 for troubleshooting said to do that 🙂
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply