Finding Zero Length Files

Apr 11, 2008

I'm trying to build a process within SSIS and running into a large number of road blocks. I could just drop out into T-SQL and code this entire thing, but I'm trying not to do that so that I can take advantage of the parallel processing pieces as well as learn a few things along the way.

Here's the overview of what I need to do on a daily basis:
1. FTP files to a local directory which I haven't already grabbed
2. Make a copy of the files into an archive directory
3. gunzip the files in my working directory
4. Remove any zero length files
5. For each file in the directory, run a data flow task
6. At the successful completion of the data import, log the imported filename into a table, along with the row count
7. If 6 is true, delete the file from the working directory

I gave up trying to get the FTP task to do #1 and I also gave up trying to get a script task written to do this. So, I wrote a stored procedure that will grab any files off an FTP site that are not already in my archive directory.

I figured out that the File System Task is incapable of handling wildcard characters to basically do "copy *.gz...". I could have done this in very little code within TSQL as well, but decided to use a for each loop to go 1 file at a time and use the file system task to copy the file.

I've already setup the for each loop with the execute process task to do the gunzip of each file in the directory. I also figured out how to suppress the pop up window as well as make it ignore any error messages. (Some of our files get an "unexpected eof" from gunzip, so that I know when we're done with the process, anything with a .gz extension had an error unzipping and needs to be kicked back to the provider.) I do have one issue with the fact that the execute process task leaves a copy of gzip.exe running in memory that leaves the last file locked and has to be killed manually.
Now, I'm to the zero length files issue. There isn't anything in the file system task to let me perform operations based on a file attribute. I can do this using TSQL by shelling out through xp_cmdshell and running a dir /-C, piping that to a file, BCP the file into a table, and then string parse the file names for everything with a length of 0 and then issue a delete command. I would really rather not do that. I'm fairly certain that I can do this in WMI, but I have no idea how to code that. Essentially, what I want to do is fire off a WMI query that pulls back a list of files in a given directory with a length of zero, hand that list to a for each loop, log each zero length file name into a table within SQL Server that it was processed with 0 records, then delete the file.

Two questions:
1. Anyone know how to get the Execute Process task to not leave the last instance of the exe running when it completes?
2. Anyone know how to get a list of zero length files from a specified directory to feed to a for each loop?

View 4 Replies


ADVERTISEMENT

Finding Out Variable Type And Length

Apr 28, 1999

I'm new to SQL Server. I installed a copy of 6.5 on my server and set it up today. I received a db from a colleague and have been unable to find out what the variable types and lengths are. This may be very easy but I need to know.

Thanks,
David A. Fordinal
fordinal@uta.edu

View 1 Replies View Related

DTS & Fixed Length Text Files

Apr 13, 2001

I created a package to import records from a fixed length 700 byte text file to a table in a SQL Database. I used the wizard to set it up and note the byte where each column ends. I need to customize the process, as the name of the text file will change each night, so I want to be able to set the file name from the VB front end app.

I have tried modifying the Datasource property of the DTS connection to the flat file, without success. I have also tried setting a global variable for the datasource property in DTS, and assigning that from VB, similarly without luck.

Do I have to create a custom package in code from VB? If so, how do indicate where each column in the text file ends? If I can customize the existing package, is there a specific reference that I need to set in my project that will let me control the value of the global?

TIA

View 2 Replies View Related

Finding Files To Attach

Nov 1, 2007

Using Microsoft SQL Server Management Studio Express when trying to attach a database, and clicking the Add button, the folder hierarchy appears, but access to personal folders is restricted. How can this be fixed?

Thanks,

Grant

View 5 Replies View Related

Finding New Entry By Comparing Two Flat Files In SSIS

Mar 10, 2008

Hi

I am trying to compare two flat files and extract new entry into new file.But in my case there is no key column in both flat files. is any way to find the new entry by checksum with out Key matching?.


Thanks
Guru

View 3 Replies View Related

T-SQL (SS2K8) :: Procedure Parameter Length Declaration Less Than Column Length?

Jun 30, 2014

is there any way or a tool to identify if in procedure the Parameter length was declarated less than table Column length ..

I have a table

CREATE TABLE TEST001 (KeyName Varchar(100) ) a procedure
CREATE PROCEDURE SpFindNames ( @KeyName VARCHAR(40) )
AS
BEGIN
SELECT KeyName FROM TEST001
WHERE KeyName = @KeyName
END
KeyName = @KeyName

Here table Column with 100 char length "KeyName" was compared with SP parameter "@KeyName" with length 40 char ..

IS there any way to find out all such usage on the ALL Procedures in the Database ?

View 2 Replies View Related

BlobColumn.GetBlobData() With Data Length &&> Integer Length (32767)

Mar 27, 2008

For those of you who would like to reference my exact issue, I'm dealing with the RSExecution SSIS package at the "Update Parameters" data flow task, at the Script Component.

The script tries to split parameter data into name and value. Unfortunately, I have several reports that are passing parameters that are very large. One example has over 65,000 characters all in the normal "&paramname=value&parm2=value..." format.

The code in the script works fine until it gets to one of these very large parameter sets. I have figured out what is causing the issue. Here's some code:

Dim paramBlob as Byte()
paramBlob = Row.BlobColumn.GetBlobData(0, Row.BlobColumn.Length)

The second parameter of the .GetBlobData function takes an INTEGER as its count! Therefore, no matter what kind of datatype I pass to the string that the script will later split, it will be limited to 32767 characters.

THIS IS A PROBLEM!!!

Does anyone know a workaround for this issue? I need all of the parameter data to be reported, and I would hate to have to skip over rows like this. Also, if I'm missing something, please fill me in!

Thanks for your help in advance,
LOSTlover

View 6 Replies View Related

CommandParameters.length Don't Match Parametervalues.length

Feb 24, 2008

I am trying to narrow down this problem.  Basically, I added 3 columns to my article table.  It holds the article id, article text, author and so on.  I tested my program before adding the additional field to the program.  The program works fine and I can add an article, and edit the same article even though it skips over the 3 new fields in the database.  It just puts nulls into those columns.So, now I have added one of the column names I added in the database to the code. I changed my businesslogic article.vb code and the addarticle.aspx, as well as the New article area in the addartivle.aspx.vb  page. The form now has an additional textbox field for the ShortDesc which is a short description of the article. This is the problem now:  The command parameters.length is 9 and there are 10 parameter values.  Right in the middle of the 10 values is the #4 value which I inserted into the code.  It says Nothing when I hover my mouse over the code after my program throws the exception in 17 below.  Why is  command parameters.length set to 9 instead of 10?  Why isn't it reading the information for value 4 like all the other values and placing it's value there and calculating 10 instead of 9? Where are these set in the program?  Sounds to me like they are hard coded in someplace and I need to change them to match everything else.  1 ' This method assigns an array of values to an array of SqlParameters.2 ' Parameters:3 ' -commandParameters - array of SqlParameters to be assigned values4 ' -array of objects holding the values to be assigned5 Private Overloads Shared Sub AssignParameterValues(ByVal commandParameters() As SqlParameter, ByVal parameterValues() As Object)6 7 Dim i As Integer8 Dim j As Integer9 10 If (commandParameters Is Nothing) AndAlso (parameterValues Is Nothing) Then11 ' Do nothing if we get no data12 Return13 End If14 15 ' We must have the same number of values as we pave parameters to put them in16 If commandParameters.Length <> parameterValues.Length Then17 Throw New ArgumentException("Parameter count does not match Parameter Value count.") 18 End If19 20 ' Value array21 j = commandParameters.Length - 122 For i = 0 To j23 ' If the current array value derives from IDbDataParameter, then assign its Value property24 If TypeOf parameterValues(i) Is IDbDataParameter Then25 Dim paramInstance As IDbDataParameter = CType(parameterValues(i), IDbDataParameter)26 If (paramInstance.Value Is Nothing) Then27 commandParameters(i).Value = DBNull.Value28 Else29 commandParameters(i).Value = paramInstance.Value30 End If31 ElseIf (parameterValues(i) Is Nothing) Then32 commandParameters(i).Value = DBNull.Value33 Else34 commandParameters(i).Value = parameterValues(i)35 End If36 Next37 End Sub ' AssignParameterValues38 39 40 41  

View 2 Replies View Related

SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups

Jan 9, 2015

I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.

View 2 Replies View Related

For Loop - Iterate From Older Files To Newer Files Based On File's Timestamp

Mar 13, 2008

In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.

Any Suggestions?

View 3 Replies View Related

Script Task: How To Compare Files On FTP With Existing Files In Local Folder Before Transfer!

Apr 24, 2008

In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.

I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.

Thanks for the help in advance and i apologize for the long lines of code!

example for step 1:
----------------------------------------------------------------------------------------------------------

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class

example for step 2:
-------------------------------------------------------------------------------------------------------

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Try

'Create the connection to the ftp server

Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

'Set the properties like username & password

cm.Properties("ServerName").SetValue(cm, "ftp.name.com")

cm.Properties("ServerUserName").SetValue(cm, "username")

cm.Properties("ServerPassword").SetValue(cm, "password")

cm.Properties("ServerPort").SetValue(cm, "21")

cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout

cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb

cm.Properties("Retries").SetValue(cm, "1")

'create the FTP object that sends the files and pass it the connection created above.

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connects to the ftp server

ftp.Connect()

'ftp.SetWorkingDirectory("..")

ftp.SetWorkingDirectory("directoryname")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Next

Dim files(0) As String

files(0) = maxname

ftp.ReceiveFiles(files, "C: emp", True, True)

' Close the ftp connection

ftp.Close()





'Set the filename you retreive for use in data flow

Dts.Variables.Item("FILENAME").Value = maxname

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

View 16 Replies View Related

SQL 2012 :: FOR FILES Command To Delete Old Backup Files On Remote Server?

Feb 24, 2015

I have the need to delete old backup files via TSQL job. Found this solution online:

PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD

It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?

View 6 Replies View Related

HELP!!! Cannot Import SSIS Package Files From .dtsx Files

Oct 8, 2007





Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!

View 20 Replies View Related

Is It A Good To Replace SQL Script Files With XML Files?

Jul 23, 2005

I am thinking about replacing the INSERT data scriptfiles that I have with XML files. This way I can open the XMLfile using an XML Editor and see the values in a GRID andmake changes easier.Do you see any problem with this approach?I managed to put together some code that is exportinga SQL table with its data to an XML file and also a codethat reads the XML file's data and inserts it into a table.Now I am researching on XSD, td:datatype, DTD...(I am new to XML) in order to figure out how I canuse a single xml file that will hold both the sql serverfields, the datatypes and their values.If you have links to some sample code that has anythingto do with the datatype export and import I am workingon, can you please share them with me?Most importantly what do you think about the idea of usingXML files vs sql scripts?Thank you

View 4 Replies View Related

Integration Services :: Converting RTF Files To PDF Files?

Jun 25, 2015

I have a scenario where I need to convert RDF files to PDF files? may I know is this achievable in SSIS - writing C# code?

View 6 Replies View Related

Length

Jun 7, 2004

How can I measure the length of a field of type text?

View 1 Replies View Related

Allow Zero Length

Jul 20, 2005

We ran into a problem loading access where tables where if we did notselect the property allow zero length we got an error message whenloading data with some empty values.We are now ramping up SQL server and the question came up will SQLserver have the same problem with empty data values.Is there an SQL server equivalent to allow zero length?

View 2 Replies View Related

Cmd.ExecuteNonQuery() - Max Length

Oct 22, 2007

what the max length that I can run query to sql Server?example: 

View 2 Replies View Related

Decimal Length

Feb 6, 2008

Hi all - am in a bit of a quandry over this one!My application is all up and running - it's an despact & accounts app - I've missed one thing though - the ability to deal with half pennies (or half cents for those of you over the pond ;))
All of my tables are set up as decimal (19,2) along with all the params in my stored procs (a lot!).  Is there an easy way to change all of these in 1 swoop?
Thanks in advance,
Stephen.

View 1 Replies View Related

Length Word In Sql

May 5, 2008

Is there a function how I can see how many caracters a word from my sql is?
 
for example:
if I want the lenght of the word "sql" => 3
 if I want the lenght of the word "forum" => 5

View 1 Replies View Related

Max Record Length For DTS?

Apr 26, 2001

Hi folks,

I'm trying to load a flat file (text) into a table with 83 columns in it. The record length on the flat file is 251 characters long. For some reason when running the DTS import utility, DTS can't read the past the 142 character on the flat file, it spills on to the next line. The red bar is set on the 143 character when i use the fix field options in DTS import. Can the red bar be moved out further? Or is there a max field length using DTS?

anyone out there that can help?


Joe

View 1 Replies View Related

Max. Length Of A Row In The Query.

Jul 13, 2001

Hi Guys
I am having a problem with the execution of a query.This is how it is working,if i include all the clounms(26 columns) for the output of 34,000 records it is taking around 4 Min. but if i exclude one column or reduce the size of the column i.e from text to varchar(100) it is taking 23 Sec.

If i am trying to change the size of the column from varchar(100) to varchar(150) it is taking the same time i.e, 4 Min.

Looking at this i am wondering if there are any system setting where we can increase the size of the rows for the out put or are there any other things i have to look at to increase the speed of the Query.

Did anyone come across this problem.
Any help is appriciated.
Thanks
Chak.

View 2 Replies View Related

Output Row Length

Aug 28, 2001

Hi,
I am sending the output of a query to a file and unfortunately every row is containing only a certain number of characters. My query has rows having length of 1500 characters for each row. When I get the query and send it to a file I am seeing only the first 250 characters for each row instead of 1500 characters.

I will be grateful if anyone can help me suggesting a solution for this problem.

I appreciate your help in advance.

thanks,
Sravan.

View 1 Replies View Related

Row Data Length

Jan 22, 2001

Hi,

SQL Intrduction book on page 269 says that the max.amount of data contained in a single row is 8060 bytes. But I have no difficulty creating a table with two varchar 8000 columns. How can this be ? Can somebody explain how to interpret the statement about max.8060 bytes please?
Thanks.

View 3 Replies View Related

Illegal Length Of 0

Apr 13, 2000

Hi
Has anyone come across SQL Server giving errors like A row on 'page 52995' was accessed that has illegal length of 0 in database? I believe that it is due to some data on the page has been corrupted. Is there a way to prevent such problem? Can daily backup be able identify such problem?

View 1 Replies View Related

Character Length In SQL

Dec 29, 1999

What is the syntax to count the # of characters in any given field in Transact SQL?

View 1 Replies View Related

&#34; .......too Long, Max Length Is 128&#34;

Sep 22, 2000

Hi
When writing a stored procedure,sometimes the line of sql code could be very long and be more than 128 caracthers.
I got an error message saying that the code is vey long and the maximum is 128.
But at the same time, this is erratic in MSSQL server because it works sometimes even with a line of code of more than 800 caracters...
Have you ever experienced the same problem??
What is the solution?
Thanks

View 1 Replies View Related

Is There A &#34;LENGTH&#34; Function?

Jun 28, 1999

Is there a string handling function is SQL which will return the length of the contents of a field?

View 1 Replies View Related

Varchar Length

Jul 12, 2004

i have problem regarding the row length and varchar.
my problem is on every new row i have +6 more character on one of my field then the last record. and BOL says i can only have 8060 character per row.
What i can not use the full lenght of varchar(8000) on field.

Can anybody help?????????

View 5 Replies View Related

String Length In DTS

Jun 8, 2006

Hi

I am quite new in DTS and I want to calculate the length of a string. I am trying to use ActiveX script - Java Script and here is my code

if(length(DTSSource("MaNo")) ==5). It parse fine but when I run a test it brings an error "Error Description: Object Expected"

Please help

View 2 Replies View Related

Max Text Length

Jan 23, 2004

What is the maximum text length that can be entered into a sql server field and what data type should be used for it?

Thanks!

View 5 Replies View Related

Get Column Length

Apr 9, 2008

Hi,

I am creating a piece of code to create audit tables based on my current tables. I'm using the info in sysobjects to get this done, but I have one problem.
I can't seem to find the actual lenght of a column, I tried with the systypes.length, but that was obviously not what I needed. Does someone know how (or where) I can retrieve the length that I need?

thx in advance.

View 2 Replies View Related

Length Of The Value In A Field

Aug 29, 2007

is there a property in the sql server where i can track the length of the value in a field.

Funnyfrog

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved