DTS Package - How to prevent a 0 byte file?

I've created a DTS package -- that uses a query to export to a .txt file. My question is -- if the results of this query are zero (no results returned within the package ) -- how can I tell the package not to export a zero byte file. Any thoughts on that? Any help you could give would be greatly appreciated. Thanks!Do you mean that the file doesn't exist (which would cause an error) or that it's a 0 length file?|||the package is producing a file that is 0 length. what i'd like to be able to do is -- if the file produced is 0 in length -- i would like for it not to output a file at all...does that help?|||Create a ExecuteSQL task that computes the COUNT() on your returning query and assigns it to a global variable. Then, create an ActiveX task that checks that global variable and performs the export only if it is noy null...

DTS Package

How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
ThanksYou need an ActiveX script task in your DTS package to do this. In your
ActiveX script task, you have to instantiate the FileSystemObject object to
rename the file.
Here's an example from one of my DTS packages:
dim DataFile, FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
DataFile = DTSGlobalVariables("FileLocation")
If FSO.FileExists(DataFile) Then
FSO.MoveFile DataFile, DTSGlobalVariables("ProcessedFiles") + "Orders_"
+CStr(Year(Now)) + Right("0" + CStr(Month(Now)), 2) + Right("0" +
CStr(Day(Now)), 2) + Right("0" + CStr(Hour(Now)), 2) + Right("0" +
CStr(Minute(Now)), 2) + Right("0" + CStr(Second(Now)), 2) + ".txt"
End If
Main = DTSTaskExecResult_Success
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Johnfli" <john@.ivhs.us> wrote in message
news:OFU2fLxiGHA.4204@.TK2MSFTNGP02.phx.gbl...
How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
Thanks

DTS Package

How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
ThanksYou need an ActiveX script task in your DTS package to do this. In your
ActiveX script task, you have to instantiate the FileSystemObject object to
rename the file.
Here's an example from one of my DTS packages:
dim DataFile, FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
DataFile = DTSGlobalVariables("FileLocation")
If FSO.FileExists(DataFile) Then
FSO.MoveFile DataFile, DTSGlobalVariables("ProcessedFiles") + "Orders_"
+CStr(Year(Now)) + Right("0" + CStr(Month(Now)), 2) + Right("0" +
CStr(Day(Now)), 2) + Right("0" + CStr(Hour(Now)), 2) + Right("0" +
CStr(Minute(Now)), 2) + Right("0" + CStr(Second(Now)), 2) + ".txt"
End If
Main = DTSTaskExecResult_Success
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Johnfli" <john@.ivhs.us> wrote in message
news:OFU2fLxiGHA.4204@.TK2MSFTNGP02.phx.gbl...
How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
Thanks

DTS load

New to SQL Server :(
load a txt file to SQL
there is a field, data type is numeric (13,6).
But in the file, it is like this: 9999999999999
How do I add the decimal points to make it: 9999999.999999??
Thanks a lotOriginally posted by qingbo
New to SQL Server :(

load a txt file to SQL

there is a field, data type is numeric (13,6).
But in the file, it is like this: 9999999999999

How do I add the decimal points to make it: 9999999.999999??

Thanks a lot

Add an Execute SQL Task object to the package and insert code something similar to this:

UPDATE tablename
SET ColumnName = ColumnName / 1000000.00

Where ColumnName is the numeric(13, 6) column defined in the table.

Hope this helps.

DTS Job ActiveX Script Help

I have a DTS Package Job that needs to pre-check a txt file (see below) with a 'Date' in it. TO compare it with the current Date (execution Date -> today). If they match, move on to the next step and fail otherwise. I don't know how to create an ActiveX script to do this kind of comparison.

----------------------
Volume Unit Referred SBR Used Recfm SSNE BlkSz Dsorg Dsname
5GSL4B 6760 2005/03/09 1065535 FB 3000 27000 PS 'AAS3P.QT.SECMRK.ZXWSDB.FULL.UNPACKED'
----------------------

Thank you for any suggestion!

J827I would create the text file as another data source. You can then grab the first line, and put the date into the global variable or something, then run an activeX script to compare the global variable to the current date.

Does that kinda achieve what you are after?

DTS Import txt File looping.

Hello,
Here i my problem;
I have a number of files with all the exact same format and i need to import
all of them into the same table.
As i never faced the problem before im thinking
to created a txt source and and sql server source with a data transfrom
between them. I want to change the the txt source connection "File Name"
properties for each file I need to import and loop until im done importing
all my files.(FYY: I hold all the table names in a sql table that I refreash
before importing)
So my question are ;
Is this the best approch to solve the issue with i data provided above? how
(high level - unless u want to give me the code ;-) )
or is there other ways that are better then this?
FYI: I cant use a bulk insert because the flat file layout it has follows.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1
D`DVAL1 1`DVAL2 2
D`DVAL1 1`DVAL2 2`DVAL3 3
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
i could use a bulk insert if the layout were as follows BUT its not.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1```
D`DVAL1 1`DVAL2 2``
D`DVAL1 1`DVAL2 2`DVAL3 3`
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
Thanks!!I just did this very thing. You're on the right track.
I disabled the transformation step using Disconnected Edit (in my case this
step is called DTSStep_DTSDataPumpTask_2). I then had an ActiveX script loop
thru the file names calling that transformation step.
Here's some code/pseudocode:
Set oPkg = DTSGlobalVariables.Parent
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim strThisFileName
strThisFileName = yourfirstfilename ' Get the first file name from
your table here.
' Loop thru your file names here
Do While NOT ...EOF
If oFSO.FileExists(strThisFileName) then
oPkg.Connections("YourSourceFileConnection").DataSource =
strThisFileName ' Set the filename
oPkg.Steps("DTSStep_DTSDataPumpTask_2").Execute
' Import file into table
else
exit do
end if
strThisFileName = yournextfilename
loop
Set oPkg= Nothing
Set oFSO = Nothing
"John Smith" <zzaro@.excite.com> wrote in message
news:O4gYZ96cGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Here i my problem;
> I have a number of files with all the exact same format and i need to
> import all of them into the same table.
> As i never faced the problem before im thinking
> to created a txt source and and sql server source with a data transfrom
> between them. I want to change the the txt source connection "File Name"
> properties for each file I need to import and loop until im done importing
> all my files.(FYY: I hold all the table names in a sql table that I
> refreash before importing)
> So my question are ;
> Is this the best approch to solve the issue with i data provided above?
> how (high level - unless u want to give me the code ;-) )
> or is there other ways that are better then this?
> FYI: I cant use a bulk insert because the flat file layout it has follows.
>
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1
> D`DVAL1 1`DVAL2 2
> D`DVAL1 1`DVAL2 2`DVAL3 3
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
> i could use a bulk insert if the layout were as follows BUT its not.
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1```
> D`DVAL1 1`DVAL2 2``
> D`DVAL1 1`DVAL2 2`DVAL3 3`
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
>
> Thanks!!
>