February 12, 2008 at 12:15 am
Hi,
I want to generate CSV files based on location.
i have a location master assume values will be India, US, CA, etc...
based on the location i want to create and save my CSV file.
example: if location is India, my csv file should be saved as India02022008.csv
if Canada -- canada02022008.csv
02022008 -- should be current date and time.
February 12, 2008 at 9:54 am
I'm not sure precisely what you're asking, but I'll give it a shot...
You can use a variable defined by an expression.
I assume the package knows which location it is dealing with during run time.
1. Create a variable that contains the location (India, US, etc.)
2. Create a second variable that uses an expression to build the filename using the first variable plus the date format you want
3. The CSV file destination object's connection string can also be set using an expression that can reference your dynamically built filename
BOL has a good deal of info on how to use the expression builder if you need help.
Hope this gives you a good start.
Dan
February 13, 2008 at 1:43 am
Good advise above, I would recommend you use the for each looping container to break each segment of data out into separate csv files.
Basically it would go like this.
1.) select a distinct list of locations back from your data source. This will create you list of files to create.
2.) Inside the looping container execute another select with a WHERE clause matching the location variable.
3.) Output to CSV file as laid out above.
Off to the races!
February 13, 2008 at 2:20 am
Hello,
Can you please tell me how to create CSV file......?
February 13, 2008 at 4:02 am
This link should get you set.
February 13, 2008 at 8:54 am
Some points to look out for when creating the SP or just executing the OpenRowset command shown below.
1. Be sure the text file being accessed does exist on the Server which hosts the instance of SQL Server In this example:
D:\MSSQL\','SELECT * FROM Test.txt' is the full path name to the text file where the ouput will be written.
2. Be sure the text file (Test.txt) has as its first line the field names (comma separated)
In this example the first line of the text file will be:
Keywordid, Keyword
A brief sample taken from the output text file:
Keyword, KeywordId
"1","Discs"
"2","freespace"
"3","drives"
"4","UTC"
"5","Time"
"6","Constraints"
3. KeywordId, Keyword - the columns whose data will be selected and written into the text file (starting on the 2nd line).
4. Keywords it the name of the table in the current database.
Finally - the code
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=D:\MSSQL\','SELECT * FROM Test.txt') SELECT KeywordId, Keyword FROM Keywords
February 19, 2008 at 2:10 pm
k (2/12/2008)
Hi,I want to generate CSV files based on location.
i have a location master assume values will be India, US, CA, etc...
based on the location i want to create and save my CSV file.
example: if location is India, my csv file should be saved as India02022008.csv
if Canada -- canada02022008.csv
02022008 -- should be current date and time.
I would take ALL the above replies into considerations. But then, you also wanted current time in your CSV right? This can be generated by adding a derieved column transformation just before your last destination and then type in GETDATE() in the expression column and name the column anything you want. Make this as the first row to be output for your csv file. Now you will have current date for all your files.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 19, 2008 at 2:15 pm
diptidjadhav (2/13/2008)
Hello,Can you please tell me how to create CSV file......?
I am assuming that you are brand new for this. I will also recommend a lot of reading. But to help you jump start, you must have something (data) as a source and then bring it to a destination as csv by loading them in Flat file destination. There you can specify the delimiter as comma separated. There you go. You have just created a CSV file.
Reply back if you want step by step guidance. I did not do it as I thought you would have already know some SSIS transformations and how a data flow work.;)
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 19, 2008 at 2:22 pm
SQL King (2/19/2008)
I would take ALL the above replies into considerations. But then, you also wanted current time in your CSV right? This can be generated by adding a derieved column transformation just before your last destination and then type in GETDATE() in the expression column and name the column anything you want. Make this as the first row to be output for your csv file. Now you will have current date for all your files.
The issue with this suggestion is that the derived column task takes place inside the Data Flow. This will add to your processing overhead. (The overhead obviously depends on your data size.) It will run the GETDATE() function for each row in your data set. Then you have to pain of getting back into the data to extricate the date value from the dataset to apply to the filename.
(With all due respect to SQL King) You're definitely better off setting your filename (and date value) in the Control Flow before the Data Flow task. Then the CSV filename can be referenced with no extra overhead.
Dan
February 19, 2008 at 2:31 pm
Thanks Daniel. I certainly will also think about that in fact. I know how database size have made my life hard before. You are right that everything should be set in the control flow before going to dataflow. I have seen drastic changes in that. Thanks again!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply