DTS package question

I have created one package and schedule for everday 11.00 pm. Which
processes some data and exports in text file. My problem is I can not export
into separate file. I would like to export into exportfile<yymmdd>.txt. How
can I do this?one way to do that is to create a global variable that set the output file
name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
script and execute it using xp_cmdshell. To change the file name set your
global variable with the /L option on the DTSRUN command. The /L parameter
of course is generated with the T-SQL script.
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I have created one package and schedule for everday 11.00 pm. Which
> processes some data and exports in text file. My problem is I can not
export
> into separate file. I would like to export into exportfile<yymmdd>.txt.
How
> can I do this?
>|||Thanks Gregory, but I am new to SQL server. Could you please advice me step
by step.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
> one way to do that is to create a global variable that set the output file
> name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
> script and execute it using xp_cmdshell. To change the file name set your
> global variable with the /L option on the DTSRUN command. The /L
parameter
> of course is generated with the T-SQL script.
> --
> ----
--
> ----
--
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > I have created one package and schedule for everday 11.00 pm. Which
> > processes some data and exports in text file. My problem is I can not
> export
> > into separate file. I would like to export into exportfile<yymmdd>.txt.
> How
> > can I do this?
> >
> >
>|||I changed my mind on what was easiest here. I think you should just use the
"Dynamic Properties Task". So do this:
1) Add a "Dynamic Properties Task" to your DTS package.
2) Click on the Dynamic Properties task you just added, and then click on
the "Add" button.
3) On the "Package Properties" page expand the "Connections" item. Find the
"Text File" connection that you want to change the output file based on the
run date. Then click ont the "Property Name" called "Data Source" (this is
the property the contains your file name). Next click on the "Set" button.
4) On "Add/Edit Assignment" page expand the Source pulldown. Select the
"Query" option. Now type the following query in the the "Query:" text box "
select 'c:\temp\' + convert(char(6),getdate(),12) + '.txt' " (without the
double quotes at the beginning and end.)
5) Click Ok and then OK again.
6) Now save your package and run it. Note you might have to run it twice to
make the dynamic property take.
Good luck.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:%23af2PT0nEHA.3992@.TK2MSFTNGP15.phx.gbl...
> Thanks Gregory, but I am new to SQL server. Could you please advice me
step
> by step.
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
> > one way to do that is to create a global variable that set the output
file
> > name in yoru DTS parameter. Then generate a DTSRUN statement from a
T-SQL
> > script and execute it using xp_cmdshell. To change the file name set
your
> > global variable with the /L option on the DTSRUN command. The /L
> parameter
> > of course is generated with the T-SQL script.
> > --
> >
> ----
> --
> ----
> --
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > > I have created one package and schedule for everday 11.00 pm. Which
> > > processes some data and exports in text file. My problem is I can not
> > export
> > > into separate file. I would like to export into
exportfile<yymmdd>.txt.
> > How
> > > can I do this?
> > >
> > >
> >
> >
>