April 25, 2012 at 12:15 pm
Hello,
I am new to SSIS. I hope someone can help me and provide SSIS solution for the following requirements:
I need to loop through the following table each month to create separate .csv files for each vendor and send the files to a FTP Site in separate folders (based on the vendor ID).
Vendor_IDVendor_NameAccount_Nbr
001 Vendor A 1234
001 Vendor A 4567
002 Vendor B 7890
003 Vendor C 9876
Since the table is updated monthly with new vendors and accounts, I wan the SSIS to be able to create csv files named based on the vendor ID, Vendor Name, and the date of the file created. The SSIS also need to be able to send the files to the corresponding folder.
For example: 001VendorA_2012-03-04.csv should be resided at the FTP folder 001.
Your help is much appreciated
April 25, 2012 at 1:52 pm
weiwei_huang (4/25/2012)
Hello,I am new to SSIS. I hope someone can help me and provide SSIS solution for the following requirements:
I need to loop through the following table each month to create separate .csv files for each vendor and send the files to a FTP Site in separate folders (based on the vendor ID).
Vendor_IDVendor_NameAccount_Nbr
001 Vendor A 1234
001 Vendor A 4567
002 Vendor B 7890
003 Vendor C 9876
Since the table is updated monthly with new vendors and accounts, I wan the SSIS to be able to create csv files named based on the vendor ID, Vendor Name, and the date of the file created. The SSIS also need to be able to send the files to the corresponding folder.
For example: 001VendorA_2012-03-04.csv should be resided at the FTP folder 001.
Your help is much appreciated
Something to help you get started on learning SSIS:
Adding a Time Stamp to a File Name in SSIS
Stairway to Integration Services[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 7:31 am
weiwei_huang (4/25/2012)
Hello,I am new to SSIS. I hope someone can help me and provide SSIS solution for the following requirements:
I need to loop through the following table each month to create separate .csv files for each vendor and send the files to a FTP Site in separate folders (based on the vendor ID).
Vendor_IDVendor_NameAccount_Nbr
001 Vendor A 1234
001 Vendor A 4567
002 Vendor B 7890
003 Vendor C 9876
Since the table is updated monthly with new vendors and accounts, I wan the SSIS to be able to create csv files named based on the vendor ID, Vendor Name, and the date of the file created. The SSIS also need to be able to send the files to the corresponding folder.
For example: 001VendorA_2012-03-04.csv should be resided at the FTP folder 001.
Your help is much appreciated
I'm afraid providing you with a full reponse will mean actually writing the Package for you ie. do your work 🙂
However, a few pointers to a possible approach in the rough?
I am assuming you want to pull all the rows for each Vendor from a Table and output to CSV with a custom file name?
If yes, you could use a Looping Container within SSIS.
Get a distinct set of Vendor names from the Table to loop over.
For each row, get a Sql Task to output the required set of rows to a File destination.
You should be able to use an expression to create the file name.
April 26, 2012 at 10:31 am
Thank you OTF for your quick response and tips
Be honest with, I was hopping someone can guid me through how to write the whole package. I think I most stuck at how to loop through the table and pass the variable (@ventorID) to get all records per ventor. Would you please provide more instruction for me? Thanks.
April 26, 2012 at 10:36 am
Here's a starting point
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply