Exporting multiple tables as flat files

Hi.

I've tried to create a SSIS package to simply export a bunch of tables as flat files, and am having troubles because when the for each loop hits the second table the column mappings in the flat file destination are not synchronised with its schema.

I created a for each loop with an enumerator that returns the table names and sets a user variable.

I created a data flow task which dynamically connects to the table name variable.

In the Flat File Destination there is a column mapping property, but I don't know how to reset these mappings on each iteration.

Any ideas?

Are all the tables you are trying to export have exactly same column metadata? If not, you can't do this using a for loop. This is because SSIS cannot dynamically update the column mapping/schema. If the tables have different metadata, you need to create separate data flow tasks for them.|||Thanks.

Exporting data using T-SQL... something opposite of BULK INSERT.

Hello-
We have to export data as part of processing and save it as a flat file.
What developers did was to use xp_cmdshell and export data that way.
It solved that problem but now the account calling it has be a member of
System Administration Server Role... a security risk!
Is there any other way of exporting data... with limited rights?
Regards,
MZeeshan
Do it using an app external to SQL Server? Then all they need to be is
db_datareader and be able to execute the stored procedure that generates the
results.
Or, lock down your SQL Server. Just because the job runs as SA doesn't mean
that anybody can do anything with it... they have to get to it first.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com...
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan
|||MZeeshan wrote:
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
Could you not set up a Text File data source and add it as a linked
server, then INSERT INTO ( SELECT FROM ) it?
|||Hi MZeeshan,
You might also want to setup a SQL Server Agent proxy account allows SQL
Server users who do not belong to the sysadmin fixed server role to execute
xp_cmdshell. The administrators can assign appropriate security permissions
to the proxy account. When xp_cmdshell is invoked by a user who is a member
of the sysadmin fixed server role, xp_cmdshell will be executed under the
security context in which the SQL Server service is running. When the user
is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account. If the proxy account is not available,
xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
always executed under the security context of the Windows 9.x user who
started SQL Server.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
INSERT.
>thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==
>X-WBNR-Posting-Host: 208.250.29.8
>From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
>Date: Thu, 24 Mar 2005 11:57:03 -0800
>Lines: 13
>Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hello-
>We have to export data as part of processing and save it as a flat file.
>What developers did was to use xp_cmdshell and export data that way.
>It solved that problem but now the account calling it has be a member of
>System Administration Server Role... a security risk!
>Is there any other way of exporting data... with limited rights?
>--
>Regards,
>MZeeshan
>
|||Have you considered using DTS ? Unless there are some really really complex
requirement in the export DTS will probably handle anything you need. You
can define a DTS job and allow a user account to run it and avoid the
security problem you described.
"MZeeshan" wrote:

> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan

Exporting data using T-SQL... something opposite of BULK INSERT.

Hello-
We have to export data as part of processing and save it as a flat file.
What developers did was to use xp_cmdshell and export data that way.
It solved that problem but now the account calling it has be a member of
System Administration Server Role... a security risk!
Is there any other way of exporting data... with limited rights?
--
Regards,
MZeeshanDo it using an app external to SQL Server? Then all they need to be is
db_datareader and be able to execute the stored procedure that generates the
results.
Or, lock down your SQL Server. Just because the job runs as SA doesn't mean
that anybody can do anything with it... they have to get to it first.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com...
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan|||"Aaron [SQL Server MVP]" wrote:
> Do it using an app external to SQL Server? Then all they need to be is
> db_datareader and be able to execute the stored procedure that generates the
> results.
>
Would you elaborate on this? It seems interesting!
We have nested SPs and one in the inner level has this functionality (so
that it can be called anytime any data has to be exported).
Its a 3-tier application with data requests being generated from app. server
level (calling the outer stored procedure)?
> Or, lock down your SQL Server. Just because the job runs as SA doesn't mean
> that anybody can do anything with it... they have to get to it first.
>
I wish I could... but that's out of question. Because its an old code
(before I joined here) and the business unit is not willing to let that
functionality... unless I have a better plan!
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com...
> > Hello-
> >
> > We have to export data as part of processing and save it as a flat file.
> > What developers did was to use xp_cmdshell and export data that way.
> >
> > It solved that problem but now the account calling it has be a member of
> > System Administration Server Role... a security risk!
> >
> > Is there any other way of exporting data... with limited rights?
> >
> > --
> > Regards,
> > MZeeshan
>
>|||MZeeshan wrote:
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
Could you not set up a Text File data source and add it as a linked
server, then INSERT INTO ( SELECT FROM ) it?|||Can you explain with some example?
"Ryan Walberg [MCSD]" wrote:
> MZeeshan wrote:
> > Hello-
> >
> > We have to export data as part of processing and save it as a flat file.
> > What developers did was to use xp_cmdshell and export data that way.
> >
> > It solved that problem but now the account calling it has be a member of
> > System Administration Server Role... a security risk!
> >
> > Is there any other way of exporting data... with limited rights?
> Could you not set up a Text File data source and add it as a linked
> server, then INSERT INTO ( SELECT FROM ) it?
>|||Hi MZeeshan,
You might also want to setup a SQL Server Agent proxy account allows SQL
Server users who do not belong to the sysadmin fixed server role to execute
xp_cmdshell. The administrators can assign appropriate security permissions
to the proxy account. When xp_cmdshell is invoked by a user who is a member
of the sysadmin fixed server role, xp_cmdshell will be executed under the
security context in which the SQL Server service is running. When the user
is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account. If the proxy account is not available,
xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
always executed under the security context of the Windows 9.x user who
started SQL Server.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
INSERT.
>thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==>X-WBNR-Posting-Host: 208.250.29.8
>From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
>Date: Thu, 24 Mar 2005 11:57:03 -0800
>Lines: 13
>Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hello-
>We have to export data as part of processing and save it as a flat file.
>What developers did was to use xp_cmdshell and export data that way.
>It solved that problem but now the account calling it has be a member of
>System Administration Server Role... a security risk!
>Is there any other way of exporting data... with limited rights?
>--
>Regards,
>MZeeshan
>|||Is it also true for SQL Server on Windows 2003 Server? Can you also give some
examples. I am also searching on the net and BOL explanation was little bit
cryptic.
"William Wang[MSFT]" wrote:
> Hi MZeeshan,
> You might also want to setup a SQL Server Agent proxy account allows SQL
> Server users who do not belong to the sysadmin fixed server role to execute
> xp_cmdshell. The administrators can assign appropriate security permissions
> to the proxy account. When xp_cmdshell is invoked by a user who is a member
> of the sysadmin fixed server role, xp_cmdshell will be executed under the
> security context in which the SQL Server service is running. When the user
> is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
> Server Agent proxy account, which is specified using
> xp_sqlagent_proxy_account. If the proxy account is not available,
> xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
> Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
> always executed under the security context of the Windows 9.x user who
> started SQL Server.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> >Thread-Topic: Exporting data using T-SQL... something opposite of BULK
> INSERT.
> >thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==> >X-WBNR-Posting-Host: 208.250.29.8
> >From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> >Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
> >Date: Thu, 24 Mar 2005 11:57:03 -0800
> >Lines: 13
> >Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >Path: TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Hello-
> >
> >We have to export data as part of processing and save it as a flat file.
> >What developers did was to use xp_cmdshell and export data that way.
> >
> >It solved that problem but now the account calling it has be a member of
> >System Administration Server Role... a security risk!
> >
> >Is there any other way of exporting data... with limited rights?
> >
> >--
> >Regards,
> >MZeeshan
> >
>|||That was really helpful. Thank you!!!
"William Wang[MSFT]" wrote:
> Hi MZeeshan,
> You might also want to setup a SQL Server Agent proxy account allows SQL
> Server users who do not belong to the sysadmin fixed server role to execute
> xp_cmdshell. The administrators can assign appropriate security permissions
> to the proxy account. When xp_cmdshell is invoked by a user who is a member
> of the sysadmin fixed server role, xp_cmdshell will be executed under the
> security context in which the SQL Server service is running. When the user
> is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
> Server Agent proxy account, which is specified using
> xp_sqlagent_proxy_account. If the proxy account is not available,
> xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
> Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
> always executed under the security context of the Windows 9.x user who
> started SQL Server.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> >Thread-Topic: Exporting data using T-SQL... something opposite of BULK
> INSERT.
> >thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==> >X-WBNR-Posting-Host: 208.250.29.8
> >From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> >Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
> >Date: Thu, 24 Mar 2005 11:57:03 -0800
> >Lines: 13
> >Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >Path: TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Hello-
> >
> >We have to export data as part of processing and save it as a flat file.
> >What developers did was to use xp_cmdshell and export data that way.
> >
> >It solved that problem but now the account calling it has be a member of
> >System Administration Server Role... a security risk!
> >
> >Is there any other way of exporting data... with limited rights?
> >
> >--
> >Regards,
> >MZeeshan
> >
>|||To setup the Server Agent proxy account, follow these steps:
1. Start Enterprise Manager.
2. Expand a server group and expand your SQL Server.
3. Expand the Management folder and right-click SQL Server Agent, select
Properties.
4. Click on the Job System tab. Under the "Non-Sysadmin job step proxy
account" section, clear the "Only users with SysAdmin privileges can
execute CmdExec and ActiveScripting job steps" check box, and click the
"Reset Proxy Account" button.
5. Type the user name, password, and domain of the user account to be used
when non-sysadmin executes xp_cmdshell or by SQL Server Agent running jobs
owned by users who are not system administrators.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
INSER
>thread-index: AcUxCPfsCMB02TOwRRyHt65H99UOTw==>X-WBNR-Posting-Host: 208.250.29.8
>From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>References: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
<ZacGZKQMFHA.1016@.TK2MSFTNGXA03.phx.gbl>
>Subject: RE: Exporting data using T-SQL... something opposite of BULK INSER
>Date: Thu, 24 Mar 2005 23:05:03 -0800
>Lines: 72
>Message-ID: <662BCC4F-0854-44B7-903C-BE71DB1C19F3@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383194
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Is it also true for SQL Server on Windows 2003 Server? Can you also give
some
>examples. I am also searching on the net and BOL explanation was little
bit
>cryptic.
>"William Wang[MSFT]" wrote:
>> Hi MZeeshan,
>> You might also want to setup a SQL Server Agent proxy account allows SQL
>> Server users who do not belong to the sysadmin fixed server role to
execute
>> xp_cmdshell. The administrators can assign appropriate security
permissions
>> to the proxy account. When xp_cmdshell is invoked by a user who is a
member
>> of the sysadmin fixed server role, xp_cmdshell will be executed under
the
>> security context in which the SQL Server service is running. When the
user
>> is not a member of the sysadmin group, xp_cmdshell will impersonate the
SQL
>> Server Agent proxy account, which is specified using
>> xp_sqlagent_proxy_account. If the proxy account is not available,
>> xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0
and
>> Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell
is
>> always executed under the security context of the Windows 9.x user who
>> started SQL Server.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>> --
>> >Thread-Topic: Exporting data using T-SQL... something opposite of BULK
>> INSERT.
>> >thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==>> >X-WBNR-Posting-Host: 208.250.29.8
>> >From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>> >Subject: Exporting data using T-SQL... something opposite of BULK
INSERT.
>> >Date: Thu, 24 Mar 2005 11:57:03 -0800
>> >Lines: 13
>> >Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.server
>> >Path: TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >Hello-
>> >
>> >We have to export data as part of processing and save it as a flat
file.
>> >What developers did was to use xp_cmdshell and export data that way.
>> >
>> >It solved that problem but now the account calling it has be a member
of
>> >System Administration Server Role... a security risk!
>> >
>> >Is there any other way of exporting data... with limited rights?
>> >
>> >--
>> >Regards,
>> >MZeeshan
>> >
>>
>|||Sorry that I missed your first question.
>Is it also true for SQL Server on Windows 2003 Server?
Yes, it's true.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Newsgroups: microsoft.public.sqlserver.server
>From: v-rxwang@.online.microsoft.com (William Wang[MSFT])
>Organization: Microsoft
>Date: Fri, 25 Mar 2005 07:50:34 GMT
>Subject: RE: Exporting data using T-SQL... something opposite of BULK INSER
>X-Tomcat-NG: microsoft.public.sqlserver.server
>MIME-Version: 1.0
>Content-Type: text/plain
>Content-Transfer-Encoding: 7bit
>To setup the Server Agent proxy account, follow these steps:
>1. Start Enterprise Manager.
>2. Expand a server group and expand your SQL Server.
>3. Expand the Management folder and right-click SQL Server Agent, select
>Properties.
>4. Click on the Job System tab. Under the "Non-Sysadmin job step proxy
>account" section, clear the "Only users with SysAdmin privileges can
>execute CmdExec and ActiveScripting job steps" check box, and click the
>"Reset Proxy Account" button.
>5. Type the user name, password, and domain of the user account to be used
>when non-sysadmin executes xp_cmdshell or by SQL Server Agent running jobs
>owned by users who are not system administrators.
>Sincerely,
>William Wang
>Microsoft Online Partner Support
>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
>=====================================================>Business-Critical Phone Support (BCPS) provides you with technical phone
>support at no charge during critical LAN outages or "business down"
>situations. This benefit is available 24 hours a day, 7 days a week to all
>Microsoft technology partners in the United States and Canada.
>This and other support options are available here:
>BCPS:
>https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
>Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
>If you are outside the United States, please visit our International
>Support page:
>http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
>=====================================================>This posting is provided "AS IS" with no warranties, and confers no rights.
>--
>>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
>INSER
>>thread-index: AcUxCPfsCMB02TOwRRyHt65H99UOTw==>>X-WBNR-Posting-Host: 208.250.29.8
>>From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>>References: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
><ZacGZKQMFHA.1016@.TK2MSFTNGXA03.phx.gbl>
>>Subject: RE: Exporting data using T-SQL... something opposite of BULK
INSER
>>Date: Thu, 24 Mar 2005 23:05:03 -0800
>>Lines: 72
>>Message-ID: <662BCC4F-0854-44B7-903C-BE71DB1C19F3@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA03.phx.gbl
>>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383194
>>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Is it also true for SQL Server on Windows 2003 Server? Can you also give
>some
>>examples. I am also searching on the net and BOL explanation was little
>bit
>>cryptic.
>>"William Wang[MSFT]" wrote:
>> Hi MZeeshan,
>> You might also want to setup a SQL Server Agent proxy account allows
SQL
>> Server users who do not belong to the sysadmin fixed server role to
>execute
>> xp_cmdshell. The administrators can assign appropriate security
>permissions
>> to the proxy account. When xp_cmdshell is invoked by a user who is a
>member
>> of the sysadmin fixed server role, xp_cmdshell will be executed under
>the
>> security context in which the SQL Server service is running. When the
>user
>> is not a member of the sysadmin group, xp_cmdshell will impersonate the
>SQL
>> Server Agent proxy account, which is specified using
>> xp_sqlagent_proxy_account. If the proxy account is not available,
>> xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0
>and
>> Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell
>is
>> always executed under the security context of the Windows 9.x user who
>> started SQL Server.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader
so
>> that others may learn and benefit from your issue.
>> This posting is provided "AS IS" with no warranties, and confers no
>rights.
>> --
>> >Thread-Topic: Exporting data using T-SQL... something opposite of BULK
>> INSERT.
>> >thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==>> >X-WBNR-Posting-Host: 208.250.29.8
>> >From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
>> >Subject: Exporting data using T-SQL... something opposite of BULK
>INSERT.
>> >Date: Thu, 24 Mar 2005 11:57:03 -0800
>> >Lines: 13
>> >Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.server
>> >Path: TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >Hello-
>> >
>> >We have to export data as part of processing and save it as a flat
>file.
>> >What developers did was to use xp_cmdshell and export data that way.
>> >
>> >It solved that problem but now the account calling it has be a member
>of
>> >System Administration Server Role... a security risk!
>> >
>> >Is there any other way of exporting data... with limited rights?
>> >
>> >--
>> >Regards,
>> >MZeeshan
>> >
>>
>|||Have you considered using DTS ? Unless there are some really really complex
requirement in the export DTS will probably handle anything you need. You
can define a DTS job and allow a user account to run it and avoid the
security problem you described.
"MZeeshan" wrote:
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshansql

Exporting data using T-SQL... something opposite of BULK INSERT.

Hello-
We have to export data as part of processing and save it as a flat file.
What developers did was to use xp_cmdshell and export data that way.
It solved that problem but now the account calling it has be a member of
System Administration Server Role... a security risk!
Is there any other way of exporting data... with limited rights?
Regards,
MZeeshanDo it using an app external to SQL Server? Then all they need to be is
db_datareader and be able to execute the stored procedure that generates the
results.
Or, lock down your SQL Server. Just because the job runs as SA doesn't mean
that anybody can do anything with it... they have to get to it first.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com...
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan|||MZeeshan wrote:
> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
Could you not set up a Text File data source and add it as a linked
server, then INSERT INTO ( SELECT FROM ) it?|||Hi MZeeshan,
You might also want to setup a SQL Server Agent proxy account allows SQL
Server users who do not belong to the sysadmin fixed server role to execute
xp_cmdshell. The administrators can assign appropriate security permissions
to the proxy account. When xp_cmdshell is invoked by a user who is a member
of the sysadmin fixed server role, xp_cmdshell will be executed under the
security context in which the SQL Server service is running. When the user
is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL
Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account. If the proxy account is not available,
xp_cmdshell will fail. This is true only for Microsoft? Windows NT 4.0 and
Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is
always executed under the security context of the Windows 9.x user who
started SQL Server.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exporting data using T-SQL... something opposite of BULK
INSERT.
>thread-index: AcUwq6YH0fxNNv1mTzCEMQ4DgGq4pQ==
>X-WBNR-Posting-Host: 208.250.29.8
>From: "examnotes" <mzeeshan@.community.nospam>
>Subject: Exporting data using T-SQL... something opposite of BULK INSERT.
>Date: Thu, 24 Mar 2005 11:57:03 -0800
>Lines: 13
>Message-ID: <82D77840-34C2-45F1-8D4B-999B3A27D212@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383149
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hello-
>We have to export data as part of processing and save it as a flat file.
>What developers did was to use xp_cmdshell and export data that way.
>It solved that problem but now the account calling it has be a member of
>System Administration Server Role... a security risk!
>Is there any other way of exporting data... with limited rights?
>--
>Regards,
>MZeeshan
>|||Have you considered using DTS ? Unless there are some really really complex
requirement in the export DTS will probably handle anything you need. You
can define a DTS job and allow a user account to run it and avoid the
security problem you described.
"MZeeshan" wrote:

> Hello-
> We have to export data as part of processing and save it as a flat file.
> What developers did was to use xp_cmdshell and export data that way.
> It solved that problem but now the account calling it has be a member of
> System Administration Server Role... a security risk!
> Is there any other way of exporting data... with limited rights?
> --
> Regards,
> MZeeshan

Exporting data to Flat File

I'm using SSIS package to export some data to a comma delimited CSV file. The problem is that some of the fields have commas in them. Is there a way to deal with this other to changing the delimiter?

It depends on your requirements. As I see it you have 2 options:

1) Change your delimiter

2) Change the commas in the data to something else.

-Jamie

|||

Use Text Qualifiers (for ex. double quotes ") when you export the data.
Each field will be enclosed within double quotes.

Thanks,
Loonysan

exporting data to a flat file

the "flat file" destination is missing from the choices when attempting to

export data.

Need more information. Set the stage so we can see where this is happening.

Exporting data to a fixed-width flat file

Hi,
There's a lot of information on importing data from text files, but not a lot on exporting data to text files... I've checked but found no info on this.

I'm trying to export data from SQL Server to a fixed-width flat file and wondering if I'm doing it the right way.

I use a view as source (using a OLEDB connection manager) and I can see the data without problem.

I defined a Flat File Destination (using a flat file connection manager). When setting up the flat file connection manager, I am asked for a file... Does this mean one should create manually a template file with the desired output format? So I used a production file as template since we're replacing an existing process.

After having set up everything, I run the SSIS only to see all the data on the same row. There are no CRLF...

When I create the file connection manager, there's no way to mention the row delimiter. In the properties I see a "Row Delimiter" field and when I try with "{CR}{LF}" it makes no difference. Interesting to note that, contrary to the HeaderRowDelimiter field, the RowDelimiter field has no drop-down control to give choices.

So I had to return the CRLF as the last field of the source view (SELECT .... ,'CRLF' = CHAR(13) + CHAR(10) FROM ...) to make it work.

Seems odd... Is it the way to go?

Thanks

SSIS implements a row delimiter in an odd way - it parses it from the last field delimiter.

You could Open the Flat File Connection manager - Click on "Advanced" - scroll down to last column and - set ColumnDelimiter to "{CR}{LF}"|||

Hi TVM,

The ColumnDelimiter field is greyed out (unavailable)...

Thanks

|||I tried it out - it seems like the solution you are looking for is to use "ragged-right" format

In that case you can specify widths for columns and last column can be delimited with CR LF|||

Thanks I'll try that!!

EDIT: It works! Thank you very much

|||

fleo wrote:

Thanks I'll try that!!

EDIT: It works! Thank you very much

Please mark this thread as answered.|||

coming in later with a similar issue...

i had probs with a fixed width export, as well -- and the probs didn't seem "rational"; i just couldn't get the fixed-width SSIS solution to work. as soon as i setup up the ragged right solution, everything fell into place.

in my mind, fixed-width = ragged right.

thx/spirits,

seth j hersh

sql

Exporting data to a fixed-width flat file

Hi,
There's a lot of information on importing data from text files, but not a lot on exporting data to text files... I've checked but found no info on this.

I'm trying to export data from SQL Server to a fixed-width flat file and wondering if I'm doing it the right way.

I use a view as source (using a OLEDB connection manager) and I can see the data without problem.

I defined a Flat File Destination (using a flat file connection manager). When setting up the flat file connection manager, I am asked for a file... Does this mean one should create manually a template file with the desired output format? So I used a production file as template since we're replacing an existing process.

After having set up everything, I run the SSIS only to see all the data on the same row. There are no CRLF...

When I create the file connection manager, there's no way to mention the row delimiter. In the properties I see a "Row Delimiter" field and when I try with "{CR}{LF}" it makes no difference. Interesting to note that, contrary to the HeaderRowDelimiter field, the RowDelimiter field has no drop-down control to give choices.

So I had to return the CRLF as the last field of the source view (SELECT .... ,'CRLF' = CHAR(13) + CHAR(10) FROM ...) to make it work.

Seems odd... Is it the way to go?

Thanks

SSIS implements a row delimiter in an odd way - it parses it from the last field delimiter.

You could Open the Flat File Connection manager - Click on "Advanced" - scroll down to last column and - set ColumnDelimiter to "{CR}{LF}"|||

Hi TVM,

The ColumnDelimiter field is greyed out (unavailable)...

Thanks

|||I tried it out - it seems like the solution you are looking for is to use "ragged-right" format

In that case you can specify widths for columns and last column can be delimited with CR LF|||

Thanks I'll try that!!

EDIT: It works! Thank you very much

|||

fleo wrote:

Thanks I'll try that!!

EDIT: It works! Thank you very much

Please mark this thread as answered.|||

coming in later with a similar issue...

i had probs with a fixed width export, as well -- and the probs didn't seem "rational"; i just couldn't get the fixed-width SSIS solution to work. as soon as i setup up the ragged right solution, everything fell into place.

in my mind, fixed-width = ragged right.

thx/spirits,

seth j hersh

export/data dump to flat file is too slow

I am trying to export a table with ~ 10 Million rows to a flat file and it is taking for ever with SQL2005 export functionality. I have tried creating an SSIS package with a flat-file destination and the results are the same. In each case it does the operation in chunks of about 9900+ rows, and each chunk takes ~1-2 minutes which sounds unreasonable.

I tried bcp, and it fails after a few thousand rows. I tried moving the data to SQL2000 first then to flat file from SQL2K, but the move from SQL2005->SQL2000 was going at the same rate as above.

So, the bottleneck seems to be data going out of SQL2005 no matter what the destination is. I'm wondering if there is some setting that Iam missing that would make this run in a reasonable amount of time?

Never mind. I was outputting it to a share, and that is the reason. When I modified it to do a bcp on the local drive on that server, it was flying like it is supposed to. I'll deal with copying it across the network later. Thanks.

Export to text file

Is there any built in functions in sql server that allow you to export data
from sql server to a flat file without using xp_cmdshell?
I would like to do this through an application where the user login wouldn't
need symin rights and have the sql server actually create the file simila
r
to calling bcp through xp_cmdshell.
ThanksWrap it in a Stored Procedure, giving the user EXECUTE rights on the stored
procedure. Set up the proxy account.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"John" <John@.discussions.microsoft.com> wrote in message
news:474BA5A0-7AA3-4393-ABE0-02F72079E4AE@.microsoft.com...
> Is there any built in functions in sql server that allow you to export
> data
> from sql server to a flat file without using xp_cmdshell?
> I would like to do this through an application where the user login
> wouldn't
> need symin rights and have the sql server actually create the file
> similar
> to calling bcp through xp_cmdshell.
> Thanks|||Thanks. I kind of figured something like that. I'm actually having a
problem with the constant syntax though.
Arnie Rowland wrote:
> Wrap it in a Stored Procedure, giving the user EXECUTE rights on the store
d
> procedure. Set up the proxy account.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:474BA5A0-7AA3-4393-ABE0-02F72079E4AE@.microsoft.com...|||I think declaring a variable and setting its value is the way I need to
go(?), but I don't want to insert or replace any values in the table. I
want to take information that isn't in the table (the account number)
and append it to the beginning of what gets pulled from the table. I'm
about how and where to make it happen in SQL 2000.
birdbyte@.gmail.com wrote:
> Thanks. I kind of figured something like that. I'm actually having a
> problem with the constant syntax though.
> Arnie Rowland wrote:

export to flat file - text qualifier problem

I'm exporting using a query to a flat .txt file. The problem I'm encountering is when I export the data and then open the .txt file into excel some columns cause line breaks to the next row. The columns that are breaking to a new row are varchar fields where the user has entered text into the field with double quotes ".

When I export, I'm using row delimiter {CR}{LF} column delimiter Comma and text qualifier Double Quote (")

Is there a way to prevent this from happening when I export and open the flat file into Excel?

I tried using replace, but I was getting a syntax error in my query. Here is the query without using replace:

SELECT e.session_date, l.lab_no, i.first_name + ' ' + i.last_name AS Teacher,
tt.name, d.district_name, s.school_name, t.title, a.q1 AS Question1, a.q2 AS Question2,
a.q3 AS Question3, a.q4 AS Question4, a.q5 AS Question5, a.q6 AS Question6, a.q7 AS Question7,
a.q8 AS Question8, a.q9 AS Question9, a.q10 AS Question10
FROM evaluation e
LEFT OUTER JOIN training t ON t.id = e.training
LEFT OUTER JOIN lab l ON l.id = e.lab_no
LEFT OUTER JOIN instructor i ON i.id = e.instructor
LEFT OUTER JOIN trainee tt ON tt.id = e.trainee
LEFT OUTER JOIN district d ON d.id = e.district
LEFT OUTER JOIN school s ON s.id = e.school
LEFT OUTER JOIN answers a ON a.id = e.answers
WHERE session_date >= '20070401' AND session_date < '20070501'

I would need to use the replace on columns a.q7, a.q8, a.q9, and a.q10

I tried using another delimiter...pipes (|) and that didn't work? Maybe I was attempting it incorrectly?

Thanks in advance for any help.I got Your problem
suppose your column is

a7 = AA"DS
a8 = SD"AD
a9 = WR"TY
a10 = DGHR

now you have given text qualifier as double quote -> "

so while preparing data for export your fields will be treated as

a7 = "AA"DS"
a8 = "SD"AD"
a9 = "WR"TY"
a10 = "DGHR"

since they are text

did you get my point now... AA will be treated as one piece of text instead of AA"DS.....since there is a start " and an end " which defines a text i.e AA

try using a different symbol for text qualifier, a symbol which does not exist in a7,a8,a9,a10 and that should do your job....

i know I have not explained it that clearly but i hope you are getting what i am trying to convey to you......|||Hi Nick,

thanks for replying to my post. yes, I think that is exactly the problem and I understand what you are saying. How do I use a different text qualifier though? The only ones available are a comma, double quote or none?

Thanks for your help.|||the thing is why do you need a text qualifier......if eventually you are going to import the data into another table or the same table you don't need to specify the text qualifier..... SQL server will directly import the data without a hitch.....has worked for me...try it out if that is the case....

Also if you import directly without the text qualifier to excel it should work fine.....try it out and let us know what happens

Export to flat file

We have a need to export a couple of reports to a flat file (not csv). I am thinking that the easiest way to do this is to write a custom extension. Should I do it this way and if so, can somebody point me to some resources or is there an easier way to do this?

Thanks for the information.

You need to write your own rendering extension for any output format not supported out of the box.

http://msdn2.microsoft.com/en-us/library/ms154606.aspx

|||Yeah, I was afraid that you would say that.

Export to flat file

We have a need to export a couple of reports to a flat file (not csv). I am thinking that the easiest way to do this is to write a custom extension. Should I do it this way and if so, can somebody point me to some resources or is there an easier way to do this?

Thanks for the information.

You need to write your own rendering extension for any output format not supported out of the box.

http://msdn2.microsoft.com/en-us/library/ms154606.aspx

|||Yeah, I was afraid that you would say that.

Export to Fixed width text file

Export to Fixed width text file

I am trying to export a table to a fixed lenght text file, there is only flat file option and that does not put LF/CR at the end of row, is there any solution?

I believe you can use bcp tobulk export with a format file to meet your needs. SeeFormat Files for Importing or Exporting Data for a place to start.|||

if you use SQL 2005 export wizard or custom SSIS package try to select Ragged right format for flat file destination:

Format

Indicate whether to use delimited, fixed width, or ragged right formatting.

ValueDescription

Delimited

Columns are separated by a delimiter, specified on theColumns page.

Fixed width

Columns have a fixed width.

Ragged right

Ragged right files are those in which every column has a fixed width, except for the last column, which is delimited by the row delimiter.

|||thanks, that worked. How can I execute SSIS package in a stored procedure or in a batch file in Windows?|||You can run it as SQL Agent Job which has SSIS step and this is probably best way or you can run it as batch process from command prompt or from inside SQL procedure. To generate command you can use dtsrunui.exe program which will help you a lot.

Export to Fixed width text file

Export to Fixed width text file

I am trying to export a table to a fixed lenght text file, there is only flat file option and that does not put LF/CR at the end of row, is there any solution?

Ragged right works for me. You can used a derived column to pad your string with spaces if necessary.|||Can you be more specific about what the problem is? I didn't have any trouble setting up a fixed width flat file destination with a LF/CR row delimiter. Add a flat file destination, open it and click New to create a new connection manager, select "Fixed width with row delimiters", give it a file name, switch to Advanced and select the "Row delimiter column" to change the ColumnDelimiter to {LF}{CR} if desired.
|||Row delimiter is not elabled in my environment. What would the problem be?|||It isn't there, or it is but its disabled? Can't think of why either would be the case.

If the above doesn't work for some reason, you can create the same configuration as follows. Open the flat file connection manager (or create a new one). On the General tab, select Ragged Right as the format. For starters, let's not not check "column names in the first data row". Switch to the Advanced tab and add your columns with appropriate DataTypes and OutputColumnWidths. The ColumnType should automatically be Fixed Width for each column but the last one. The last one will be Delimited. Create a dummy column to be your last one. Make its DataType String, OutputColumnWidth 0, and ColumnDelimited to be whatever you want for the row delimiter (you had previously said you wanted {LF}{CR}).
|||

Part of the confusion here may be that you have to pick the Ragged Right option for the flat file connector, not the Fixed Width option. It seems a little counter-intuitive.

Interestingly, if you create your connection manager as Jay suggested originally (by adding the flat file destination, then choosing the New... button beside the connection manager drop-down, the resulting dialog asks if you want a fixed width with row delimiters, then creates a ragged right as Jay's described above.