DTS file name to include System Date/Time

We have an application group that wants to pull date from SQL Server and write it to text file on the server. They want the file format to be 12100_YYYMMDDHHMM.fr1 for one set of data, 12100_YYYMMDDHHMM.fr2 for a second set...and so on. The '12100' is fixed, but the rest of the file name will always have to include the system date/time. Is there an easy way to do this within a DTS package (when writing to the output file)?

I would really appreciate help on this. Thank you.Hi

You can set a global variable to the fixed part at the start of the process. This should help http://www.sqldts.com/default.aspx?200 although you want to set a global variable rather than a local one.

John
"steven virnig" <pezguy@.mn.rr.com> wrote in message news:o1ovd.114089$ye4.34831@.twister.rdc-kc.rr.com...
We have an application group that wants to pull date from SQL Server and write it to text file on the server. They want the file format to be 12100_YYYMMDDHHMM.fr1 for one set of data, 12100_YYYMMDDHHMM.fr2 for a second set...and so on. The '12100' is fixed, but the rest of the file name will always have to include the system date/time. Is there an easy way to do this within a DTS package (when writing to the output file)?

I would really appreciate help on this. Thank you.

DTS and permissions

I'm developing a DTS package which will be scheduled and will write files out to a LAN share. When I execute the package manually from enterprise manager it works just fine. But when scheduled..fails with a access denied. Can someone point me towards the area I need to fix this? I'm running the SQL server under the System account.

Thanks,
EYeah...when you run the DTS package from your client, it runs under the context of your id (not to mention the mapped paths that you have).

When it's scheduled it runs under the context of the sql server service account (not to mention it's mapped drives)

Drove me NIUTS when I installed 7

What Id di was to map one of my drive to look exactly as the servers D:\ Drive

That way If I ran it or scheduled a package it would run fine...

If your problem is that you're trying t write from the server to another server, you'll need permissions for the sql server service account to that other server...

Why not write it out to the server itself if that's the case...

Good Luck|||Hi, lil_elvis2000

You should configure a domain user account to run SQL Server Service and SQL Server Agent Service.
You also have to grant write permission to that account on the lan share.

Regards,

Originally posted by lil_elvis2000
I'm developing a DTS package which will be scheduled and will write files out to a LAN share. When I execute the package manually from enterprise manager it works just fine. But when scheduled..fails with a access denied. Can someone point me towards the area I need to fix this? I'm running the SQL server under the System account.

Thanks,
E

DTS "The Parameter Is Incorrect" Error

I'm running SqlServer 2000 and am trying to write a DTS. For some reason I keep getting that error for my Dynamic Property Task and not sure why. I've tried giving my Package a password like was mentioned in another thread on here, but that didn't work either.

I'm trying to dynamically set a Data Source for an Excel connected, using a query.

Query:

DECLARE @.directory VARCHAR(1000)
SET @.directory = 'C:\'
EXEC [Legacy].[dbo].[spr_GetNewestExcel] @.directory

The Store Procedure:

CREATE PROCEDURE spr_GetNewestExcel
(
@.directory VARCHAR(1000)
)
AS
BEGIN
DECLARE @.cmdParam VARCHAR(8000)

--Temp Table that saves the output of a DOS command.
CREATE TABLE #output
(
output VARCHAR(300)
)

--Temp Table that saves the output of a "dir" DOS output, in parts.
CREATE TABLE #files
(
fileDate VARCHAR(20),
fileSize VARCHAR(20),
fileName VARCHAR(100)
)

--Sets the command parameter
SET @.cmdParam = 'dir "' + @.directory + '"'

--Inserts the output of the above DOS command into a table
INSERT INTO #output
EXEC master..xp_cmdshell @.cmdParam

--Parses the #output table for the files with the correct extension and breaks it up into
--Date, Size, and Name.
INSERT INTO #files
SELECT
SUBSTRING(output, 0, 21) As fileDate,
LTRIM(SUBSTRING(output, 21, 18)) As fileSize,
SUBSTRING(output, 40, LEN(output))
FROM #output
WHERE output LIKE '%.xls'

--Displays the values
SELECT TOP 1
CASE
WHEN SUBSTRING(@.directory, LEN(@.directory), 1) = '\' THEN @.directory + fileName
ELSE @.directory + '\' + fileName
END AS fileName
FROM #files ORDER BY fileDate DESC

DROP TABLE #output
DROP TABLE #files
END

The Stored Procedure runs great on its own, it displays a list of filenames with the .xls extension in the desired directory, which is what I want. It should put the first in the list((if there is more than one)) as the datasource, but it doesn't.

When I go to the "Add/Edit Assignment" window for it, and push "Refresh" to preview it, it gives me a "The Parameter Is Incorrect".

Any help would be much appreciated, this has had me stumped for quite some time.

peace,
CR
you're in the wrong forum. this forum is about ssis, not dts.|||fair enough, I saw other threads similar to this one, so I posted here. And since the description mentions data transforms, seemed fitting. Though feel free to point me in the right direction since the other boards don't seem anywhere near as fitting as this one. Least not to me.|||Well after a while I finally determined what was wrong, but I must say, what a vague error.

Anyway, apparently Dynamic Properties don't like when you use Inserts, that is all.

Thanks for reading!

peace,
CR|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

I spect that you understand my english. About this problem it isn′t that Dynamic Properties don't like Inserts the problem is the messages that SQL return when you make an insert. You have to add to your code "SET NOCOUNT ON " to ommit the messages.

DTS "The Parameter Is Incorrect" Error

I'm running SqlServer 2000 and am trying to write a DTS. For some reason I keep getting that error for my Dynamic Property Task and not sure why. I've tried giving my Package a password like was mentioned in another thread on here, but that didn't work either.

I'm trying to dynamically set a Data Source for an Excel connected, using a query.

Query:

DECLARE @.directory VARCHAR(1000)
SET @.directory = 'C:\'
EXEC [Legacy].[dbo].[spr_GetNewestExcel] @.directory

The Store Procedure:

CREATE PROCEDURE spr_GetNewestExcel
(
@.directory VARCHAR(1000)
)
AS
BEGIN
DECLARE @.cmdParam VARCHAR(8000)

--Temp Table that saves the output of a DOS command.
CREATE TABLE #output
(
output VARCHAR(300)
)

--Temp Table that saves the output of a "dir" DOS output, in parts.
CREATE TABLE #files
(
fileDate VARCHAR(20),
fileSize VARCHAR(20),
fileName VARCHAR(100)
)

--Sets the command parameter
SET @.cmdParam = 'dir "' + @.directory + '"'

--Inserts the output of the above DOS command into a table
INSERT INTO #output
EXEC master..xp_cmdshell @.cmdParam

--Parses the #output table for the files with the correct extension and breaks it up into
--Date, Size, and Name.
INSERT INTO #files
SELECT
SUBSTRING(output, 0, 21) As fileDate,
LTRIM(SUBSTRING(output, 21, 18)) As fileSize,
SUBSTRING(output, 40, LEN(output))
FROM #output
WHERE output LIKE '%.xls'

--Displays the values
SELECT TOP 1
CASE
WHEN SUBSTRING(@.directory, LEN(@.directory), 1) = '\' THEN @.directory + fileName
ELSE @.directory + '\' + fileName
END AS fileName
FROM #files ORDER BY fileDate DESC

DROP TABLE #output
DROP TABLE #files
END

The Stored Procedure runs great on its own, it displays a list of filenames with the .xls extension in the desired directory, which is what I want. It should put the first in the list((if there is more than one)) as the datasource, but it doesn't.

When I go to the "Add/Edit Assignment" window for it, and push "Refresh" to preview it, it gives me a "The Parameter Is Incorrect".

Any help would be much appreciated, this has had me stumped for quite some time.

peace,
CR
you're in the wrong forum. this forum is about ssis, not dts.|||fair enough, I saw other threads similar to this one, so I posted here. And since the description mentions data transforms, seemed fitting. Though feel free to point me in the right direction since the other boards don't seem anywhere near as fitting as this one. Least not to me.|||Well after a while I finally determined what was wrong, but I must say, what a vague error.

Anyway, apparently Dynamic Properties don't like when you use Inserts, that is all.

Thanks for reading!

peace,
CR|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

I spect that you understand my english. About this problem it isn′t that Dynamic Properties don't like Inserts the problem is the messages that SQL return when you make an insert. You have to add to your code "SET NOCOUNT ON " to ommit the messages.

DTS - Write Exceptions to table?

Can DTS write exceptions to a database table? In the code
below, I copy fname, lname and (at the end) company from
the source to the destination. However, with title, if the
title is executive or president, the title is copied over;
if the title is ceo it is entered into the destination
table as ceo/chairman; if none of these conditions apply
(i.e. title is owner) I want the entire row written to a
new table.

If I use Main = DTSTransformStat_SkipRow, rows that meet
the title criteria are entered into the destination table,
and the rows that don't meet the criteria are not entered.
I can get DTS to output the exeption file to a text file,
however, with the large number of records that don't meet
the criteria, it would be better to have them in a new
table.

Thanks,

Jay

----------

Function Main()
DTSDestination("fname") = DTSSource("fname")
DTSDestination("lname") = DTSSource("lname")

'Check Titles
If lcase(DTSSource("title")) = "executive" then
DTSDestination("title") = DTSSource("title")
ElseIf lcase(DTSSource("title")) = "president" then
DTSDestination("title") = DTSSource("title")
ElseIf lcase(DTSSource("title")) = "ceo" then
DTSDestination("title") = "CEO/Chairman"
Else
Main = DTSTransformStat_ExceptionRow
'Main = DTSTransformStat_SkipRow
Exit Function
End if



DTSDestination("company") = DTSSource("company")
Main = DTSTransformStat_OK
End FunctionYou can break your task in 2 phases:

First Transformation Task:
Insert / Update all rows that meet the criteria

Second Transformation Task:
Insert rows that does not meet the criteria in a anther table

Remeber the you can have more than one transformatrion task between two connections|||Thanks - I will give that a try.

DTS - Multiple Files to Multiple Tables

I am trying to write (my first, unfortunatly) DTS, and am having some problems.

I need to be able to import multiple flatfiles (all in the same format, just with different schema), each one going into a different table. I have written an application to call my DTS, sending it variables for the tablename and the filename. This works fine when I test it on a single flatfile.

My problem is, the Tranformation object does not reset after each DTS call, so I get "Column does not exist" errors after the first successful import. I can go into the DTS Manager and reset the Transformation options, but that would defeat the purpose of automation. Is there anyway to reset, or another technique, the Transformation object so that it will continuosly work on files that use different schema?

I am very new at DTS, so please consider me "ignorant" when replying.

Thanks in advance.

- JordanExpansion:

I basically need a way to "remove all transformations and remap columns" automatically after, or before, each new flatfile is imported. I can't find an option to do this.

Any suggestions?

Or am I going about this task all wrong?|||I think you want to create a separate file connection a separate data source connection for each transformation. If file A has to successfully import before file B, C, or D can be imported, then link your steps together with workflow "on success" links like:

File A--transform--table A--On Success--File B--transform--table B--On Success--File C--transform--table C--On Success--File D--transform--table D

It is possible to modify your transformations through scripts, but that would be much more difficult. Just keep your file names the same and the four-step package can be re-used the next time you need to do this import.|||Thank you for the reply,

My problem is that I have a variable number of files. Maybe if I better explain what I'm trying to do, it would help.

Each month, we get updates for a database in the form of flatfiles. The flatfiles come with various names, and stored in various directories. Each flatfile name consist of the tablename that it belongs to.

I have written a small application that recursivly loops through all directories, and grabs the files. Then, it will call my DTS sending some variables: fileName and tableName. Using these variables, I would like to have the DTS import the fileName into the tableName with simple column-to-column mapping.

This process works for the first file. But the DTS retains the mapping, so the next file that is sent (with a different number of columns into a different table) stops the DTS because of "column does not exist" errors.

In a nutshell: I'd like to use a single DTS which will import one file into one table. But each call would use a different file that goes into a different table.

Thanks,|||I've decided to use BULK INSERT instead. My DTS still accepts a fileName and tableName, but now it just does a bulk insert from the file. This method seems to be working fine for me.

Thanks for the help,