Using Integration Services to create a flat file? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Using Integration Services to create a flat file?

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-15 : 14:48:32

Sorry I mistakenly put the question in the T-SQL area.

Hi All,

I am using SQL Server 2005 on a server.

I have a SQL script that is returning data from the database. Right now it is just a script, and if needed I can easily change it to a stored proceddure. What the script returns I want to create a pipe delimited txt file against it. Meaning the output of the script should be return as txt file (pipe | delimited ). I want to use SQL Server Integration Services for this. But don't know the steps needed to do this.

When I say steps I mean that for example, a)In the SSSIS Package under Control flow tab add 'Execute SQL Task', b) add 'Data flow task' c) Combine 'Execute SQL Task' and 'Data flow task' with XYZ parameters etc, d) In the DataFlow tab add 'Script component' etc etc.

Can someone please help and guide me in the right direction.
Desperatly looking for help.
Thanks,

Zee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 14:59:17

1. In the SSIS package add a Data Flow Task
2. Double click Data Flow Task and you will enter Data Flow Tab. place a OLEDB source connnection and connect it to your server db
3. Select SQL Command option and put your query. click preview and see data. then goto columns tab and check columns are all as expected
4. Place a flat file destination and configure it to point to your file. Give the delimiter info as pipe(|).

Try running ssis and check if data is transfered as expected.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-15 : 15:21:12

Thanks visakh16 !!!

It worked. I don't think anyone can explain it better....in just 4 lines :)

Now two more question for you.
1) The txt file that has been generated how can I timestamp(the date/time the file has been generated) the file name? Meaning the file name should be something like 'Results_2005-01-05 15:19:00.txt'? Please help on this.

2) How can I schedule this package to run on a particular date/s time?

Thanks again for your help.

Zee

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-15 : 15:46:48

You need to add variable for it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-15 : 15:47:48

quote:
Originally posted by zeeshan13

Thanks visakh16 !!!

It worked. I don't think anyone can explain it better....in just 4 lines :)

Now two more question for you.
1) The txt file that has been generated how can I timestamp(the date/time the file has been generated) the file name? Meaning the file name should be something like 'Results_2005-01-05 15:19:00.txt'? Please help on this.

2) How can I schedule this package to run on a particular date/s time?
Thanks again for your help.

Zee

You need run it through SQL Agent -Jobs

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-15 : 16:03:24

Can some explain better, to what & where to use the variable in the SSIS and how?

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-15 : 16:04:14

Correction
-better means "in detail"

Can some explain in detail, to what & where to use the variable in the SSIS and how?

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-15 : 16:18:10

Can someone help please????

Thanks....

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-15 : 16:44:04

Here is what you need to do:

http://www.sqlnerd.com/ssis_dynamic_dates.htm

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-18 : 17:12:59

sodeep,

Thanks. It worked. It is displaying the timestamp without time i.e only yyymmdd
Following is the code;

@[User::path]+
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +

How Can I get the time aswell, for example;
20080818 17:12:32

Please help.

Thanks,

Zee

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-18 : 17:17:19

I am sorry I am using the following code. It is displaying the timestamp without time i.e only yyymmdd
Following is the code;

@[User::path]+
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".txt"

How Can I get the time aswell, for example;
20080818 17:12:32

Please help.

Thanks,

Zee

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-18 : 18:47:00

sodeep,

Never mind. I got it resolved using the link that you provided :). Thank you so much for all your help.
And this is what I putting in the expression to get the complete timestamp.

@[User::path]+
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) +
"-" +
Right("0" + (DT_STR,4,1252) DatePart("hour",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("minute",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("second",getdate()),2) + ".txt"

The file name is know as follows;
Results20080818-154650.txt

:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-18 : 21:42:08

[quote]Originally posted by zeeshan13

sodeep,

Never mind. I got it resolved using the link that you provided :). Thank you so much for all your help.
And this is what I putting in the expression to get the complete timestamp.

Make you path: Results_

@[User::path]+
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) +
"-" +
Right("0" + (DT_STR,4,1252) DatePart("hour",getdate()),2) + ":" +
Right("0" + (DT_STR,4,1252) DatePart("minute",getdate()),2) + ":" +Right("0" + (DT_STR,4,1252) DatePart("second",getdate()),2) + ".txt"

The file name is know as follows;
Results20080818-154650.txt

:)
[/quote

Make above changes to make it more readable.Glad to hear that it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources