November 12, 2007 at 3:36 pm
Hi all SSIS experts,
I have a task to read certain columns from the db, convert it into a tab delimted text file and post it in an FTP site.
Please suggest on the steps to accomplish this.
Is SSIS the best way?
Can the same task be accomplished using only sql code.
Regards,
Josiny
Alicia Rose
November 13, 2007 at 5:23 am
SSIS is the one of many possible ways. I do this task in few minutes.
Do you want some support to do this task?
MP
November 14, 2007 at 2:20 am
I would be interested to know how to do that in SSIS please
November 14, 2007 at 2:47 pm
1. Drag Data Flow Task on the Control Flow tab.
2. Double click on the Data Flow Task.
3. Drag Ole Db Source on the Data Flow tab.
4. Double click on it.
5. Click on New button and add connection to database table.
6. Click on Columns list box item and select columns from the table.
7. Click on OK button.
8. Drag Flat File Destination on the Data Flow tab.
9. Drag green arrow from it on the Flat File Destination component.
10. Double click on it.
11. Click on new button and select destination file.
12. Click on mappings list box item and select column mappings between database columns and file columns.
13. Click on Control Flow tab.
14. Drag FTP task on it.
15. Drag green arrow from Data Flow Task and place on FTP task.
16. Double click on FTP task and click on FTP connection and enter valid data for it
17. Click on File transfer list box item and enter remote and local paths.
15. press F5 and check if file is on the FTP site.
November 15, 2007 at 5:03 am
Thanks MArek 🙂
November 15, 2007 at 4:31 pm
I need to post files to another organization and they have to send back a set of excel files.
One option is data exchange through FTP site.
It would be great if anybody can suggest me other options.
Thanks in advance.
Alicia Rose
November 15, 2007 at 5:09 pm
Thanks Marek for the detailed steps.
I have tried out the same steps, except I had specified query as source.
And for destination I have selected these options in Flat File connection manager
-‘Ragged right’ File format.
-Header Row Delimiter : {CR}{LF}(not sure which would be the best)
When I select the preview tab in the Flat File connection manager I get the error message
“The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly.”
And when I execute the package I get the fol errors:
[font="Arial Narrow"]Error: 0xC02020A1 at Data Flow Task, Flat File Destination [231]: Data conversion failed. The data conversion for column "Product Name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC02020A0 at Data Flow Task, Flat File Destination [231]: Cannot copy or convert flat file data for column "Product Name".
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Flat File Destination" (231) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC02020A0. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC02020C4 at Data Flow Task, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.[/font]
Please suggest.
Sorry abt the messy mail.
Alicia Rose
November 16, 2007 at 1:10 am
it says 'Cannot copy or convert flat file data for column "Product Name"
Check if the conversion is right or that the columns are mapped correctly...
November 16, 2007 at 1:19 am
Alicia Rose (11/15/2007)
I need to post files to another organization and they have to send back a set of excel files.One option is data exchange through FTP site.
It would be great if anybody can suggest me other options.
Thanks in advance.
I think SSIS is the better choice.
November 16, 2007 at 1:10 pm
I used the following instructions:
1. Drag Data Flow Task on the Control Flow tab.
2. Double click on the Data Flow Task.
3. Drag Ole Db Source on the Data Flow tab.
4. Double click on it.
5. Click on New button and add connection to database table.
6. Click on Columns list box item and select columns from the table.
7. Click on OK button.
8. Drag Flat File Destination on the Data Flow tab.
9. Drag green arrow from it on the Flat File Destination component.
10. Double click on it.
11. Click on new button and select destination file.
12. Click on mappings list box item and select column mappings between database columns and file columns.
13. Click on Control Flow tab.
14. Drag FTP task on it.
15. Drag green arrow from Data Flow Task and place on FTP task.
16. Double click on FTP task and click on FTP connection and enter valid data for it
17. Click on File transfer list box item and enter remote and local paths.
15. press F5 and check if file is on the FTP site.
With one exception,
I need to have a dynamic file name everytime the FTP fires off.
I have added the dynamic name to the expression property of the FTP object and continue to get
TITLE: Microsoft Visual Studio
------------------------------
Nonfatal errors occurred while saving the package:
Error at FTP Task 1: The object name "BCF_80425_111607.txt" is not valid. The name cannot contain any of the following characters: / \ : [ ] . =
Error at FTP Task 1: The result of the expression ""BCF_80425_"+ @[User::cMMDDYY]+".txt"" on property "Name" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Any body have any ideas??
HELP :-O
--Ron
November 16, 2007 at 2:42 pm
With one exception,
I need to have a dynamic file name everytime the FTP fires off.
I have added the dynamic name to the expression property of the FTP object and continue to get
TITLE: Microsoft Visual Studio
------------------------------
Nonfatal errors occurred while saving the package:
Error at FTP Task 1: The object name "BCF_80425_111607.txt" is not valid. The name cannot contain any of the following characters: / \ : [ ] . =
Error at FTP Task 1: The result of the expression ""BCF_80425_"+ @[User::cMMDDYY]+".txt"" on property "Name" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Any body have any ideas??
HELP :-O
Show me the place where you set this expression. I think you do this in wrong place.
Is it written in Expressions option or direct in Remote or Local Paths for File Transfer option?
November 16, 2007 at 2:49 pm
MenTaT 🙂 (11/16/2007)
TITLE: Microsoft Visual Studio------------------------------
Nonfatal errors occurred while saving the package:
Error at FTP Task 1: The object name "BCF_80425_111607.txt" is not valid. The name cannot contain any of the following characters: / \ : [ ] . =
Error at FTP Task 1: The result of the expression ""BCF_80425_"+ @[User::cMMDDYY]+".txt"" on property "Name" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Any body have any ideas??
HELP :-O
Where is set the name? In expression option or for remote or local paths in file transfer option?
November 16, 2007 at 2:57 pm
In the expresion option as Name
--Ron
January 10, 2012 at 11:55 pm
Hey Thanks man...
Yours helped resolve my problem...
February 3, 2012 at 12:46 pm
Select Expressions and then select NAME and then set the following Expression to it to get the dynamic file name set to
"FileName_" +
(DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "_"+
RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) +".txt"
This will create the file as below.
FileName_yyyymmdd_hhmiss.txt
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply