SSIS 2005 Export to Flat File

Question

Post reply

SSIS 2005 Export to Flat File

Bulent Gucuk

SSCertifiable

Points: 5497
More actions
April 6, 2009 at 11:06 pm

#136404

Hello all,
what I need to accomplish sounds simple but I was not able to deploy it to production with success. Here is my situation. I have table that I need to export data daily to a flat file. I have created Data Flow Task with OLE db source and Flat file destination. File destination is a network share which sql agent has full access to read and write data. I also have an expression that appends the date to the end of the file name (i.e. Newsletter_04062009.txt). I am able to run the package manually from my workstation without a problem. However when I deploy the package to server it does not work. I tried to schedule a job using SSIS job type, it fails. I tried to run the package using DTExec it fails too. The server is SQL Server 2005 64 bit Enterprise Edition. I also tried changing the Run64BitRuntime false and used 32 bit DTexec with no success. Below is the error message from job history. Thanks in advance.
Source: Prepare and Write to Text File Validating: 0% complete End Progress Progress: 2009-04-06 22:56:26.20 Source: Prepare and Write to Text File Validating: 50% complete End Progress Warning: 2009-04-06 22:56:26.22 Code: 0x802... Process Exit Code 1. The step failed.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Nasty - can you get a more verbose error message somehow?

Have you tried changing the Flat File Destination to a local drive, just to rule out possible permissions problems?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Balaji L Hall of Fame Points: 3067 More actions · Answer 2

Hi, I am newbie to SSIS. Can you please tell me how to modify the filename once the process has completed. As you said in your forum, you are appending the filename with date. How are you achieving this. Can u pls explain me detail on that ?

Or pls share the ssis package it will help me to understand..

Thanks,

Balaji L

Famson SSC Eights! Points: 997 More actions · Answer 3

I'll suggest you try editing the configuration file and make the package create the text file locally on the server. Then run the package and verify if it runs successfully.

Then if this succeeds, you can try map to the network folder with the credentials the server agent will be using, create a text file and write to the file.

Try this approach, I hope it helps you resolve the issue.

Cheers!

Bulent Gucuk SSCertifiable Points: 5497 More actions · Answer 4

Hello all,

Thanks for responding.

Phil,

I have tried to write to local drive and do file system task to move and rename the file it failed too. But this time I received different error message. Basically errror message was 'SSIS Error Code DTS_E_PRODUCTLEVELTOLOW.' When I searched the internet I found out that the SSIS has to be installed on the server. That's what error message means. I am kind of surprised that I have other SSIS packages saved in the file system which transfers data from ServerA to ServerB with no problem but when I want to write a flat file that requires SSIS installation. We did not install SSIS on the specific server because it's a clustered server which MS does not recommend installing SSIS to clustered server since it's not cluster aware (however it's still doable). So for now I have moved the package to another server that SSIS is installed and run the package manually and as scheduled job with success.

Balaji,

I created a flat file connection. Then I go into the properties of the connection and find expressions and click on the ellipsis to bring the property expression editor. Then on the property column select Connection String then on the expression column click on the ellipsis to bring the Expression Builder.In the expression window copy and paste the text below. When you click the evaluate expression you should see a network share and a text file with date is appended as mm-dd-yy.txt change the folder and file name to your need.

"\\\\MYNETWORKSHARE\\DAILYEXPORT\\DailyDataExport_" + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"

+ RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "-"

+ SUBSTRING((DT_WSTR,4)YEAR(GETDATE()),3,2) + ".txt"

Thanks for the replies again.