December 28, 2011 at 4:15 pm
I have an issue with an SSIS package I have taken over from a previous developer. The code has been working since the beginning of the year and now if giving us issues.
There is actually two different packages getting the same result. The first Script Task is doing a simple System.IO.File.Copy from a remote share, which it created the file in, to the parent directory in the same remote share with the intent of just renaming the file.
'============OTC TO OTC============
Dim MKT_DEST_PATH As String = ALGO_MKT_FILE_PATH & ALGO_FILE_CUSTOMER_PREFIX & ALGO_DEST_FILE_MKT_PREFIX & DATE_YMD & ALGO_DEST_FILE_MKT_EXT
Dim MKT_BACKUP_PATH As String = ALGO_ARCHIVE_PATH & ALGO_FILE_CUSTOMER_PREFIX & ALGO_DEST_FILE_MKT_PREFIX & DATE_YMD & ALGO_DEST_FILE_MKT_EXT
'=====================OTC = 0==========================
f_transfer(0) = MKT_DEST_PATH
'===============ALGO FILE================
If File.Exists(MKT_DEST_PATH) Then File.Delete(MKT_DEST_PATH)
System.IO.File.Copy(ALGO_MKT_PATH_FULL, MKT_DEST_PATH)
The result is the destination file is the same size, but appears empty (Notepad). Viewing the file in Notepad++ shows that there are thousands of NULL values one after the other until the file is the same size.
The second package is doing some string/file manipulation to filter certain records with the same end result, NULL values until the file is the same size (or close based on how many lines were skipped) as the orignial file.
Dim oWRITER As New System.IO.StreamWriter(ALGO_DEST_PATH)
If File.Exists(ALGO_SOURCE_PATH) = True Then
Dim MK_EX_SBA_DEF_NAME As String = "DEPT: PARENT_MK~DEPT: MK_Corp,~DEPT: PARENT_MK~DEPT: MK_Govt,~DEPT: PARENT_MK~DEPT: MK_Mtge,~DEPT: PARENT_MK~DEPT: MK_Muni"
Dim MK_EX_SBA_SUMMARY As String = "DEPT: PARENT_MK"
Dim MK_FID_EX_SBA_DEF_NAME As String = "DEPT: MK_FIXED_INCOME~DEPT: MK_Corp,~DEPT: MK_FIXED_INCOME~DEPT: MK_Govt,~DEPT: MK_FIXED_INCOME~DEPT: MK_Mtge,~DEPT: MK_FIXED_INCOME~DEPT: MK_Muni"
Dim MK_FID_EX_SBA_SUMMARY As String = "DEPT: MK_FIXED_INCOME"
Dim MK_NEW_NAME_SUMMARY As String = "MK_EX_SBA"
Dim MK_SKIP_LINE As String = ">>"
Dim o_OPENTXT As System.IO.StreamReader = File.OpenText(ALGO_SOURCE_PATH)
Dim Line_ReadLine As String
Dim EX_SBA As Boolean = False
Dim OLF_VAR_Cmd As New SqlClient.SqlCommand
OLF_VAR_Cmd.Connection = SQLConn
o_OPENTXT = File.OpenText(ALGO_SOURCE_PATH)
Dim WRITE_ALL As String = ""
While o_OPENTXT.Peek <> -1
Line_ReadLine = o_OPENTXT.ReadLine()
If Line_ReadLine.Contains(MK_EX_SBA_DEF_NAME) = True _
OrElse _
Line_ReadLine.Contains(MK_FID_EX_SBA_DEF_NAME) = True Then
EX_SBA = True
End If
If EX_SBA = False Then
WRITE_ALL = WRITE_ALL & Line_ReadLine & vbCrLf
'oWRITER.WriteLine(Line_EX_SBA)
ElseIf EX_SBA = True Then
'================SUMMARY=======================
If Line_ReadLine.Contains(MK_EX_SBA_SUMMARY) = True _
OrElse _
Line_ReadLine.Contains(MK_FID_EX_SBA_SUMMARY) = True Then
If Line_ReadLine.Contains(MK_SKIP_LINE) = False AndAlso _
Line_ReadLine.Contains(MK_EX_SBA_DEF_NAME) = False AndAlso _
Line_ReadLine.Contains(MK_FID_EX_SBA_DEF_NAME) = False _
Then
Line_ReadLine = Line_ReadLine.Replace(MK_EX_SBA_SUMMARY, MK_NEW_NAME_SUMMARY)
Line_ReadLine = Line_ReadLine.Replace(MK_FID_EX_SBA_SUMMARY, MK_NEW_NAME_SUMMARY)
WRITE_ALL = WRITE_ALL & Line_ReadLine & vbCrLf
End If
'================SUMMARY=======================
ElseIf Line_ReadLine.Contains(MK_EX_SBA_SUMMARY) = False _
AndAlso Line_ReadLine.Contains(MK_FID_EX_SBA_SUMMARY) = False _
Then
'================DETAIL'================
WRITE_ALL = WRITE_ALL & Line_ReadLine & vbCrLf
End If
End If
'===================MK_SKIP_LINE======================
End While
oWRITER.Write(WRITE_ALL)
oWRITER.Flush()
oWRITER.Close()
End If
Has anyone ever seen anything like this? Any suggestions? Thanks for any direction here.
December 29, 2011 at 1:56 am
This was removed by the editor as SPAM
December 29, 2011 at 5:00 am
Hi,
Please send the screenshot of error message.
Suggestions:-
Please check the file format and the delimeters.
Please check the source data as well.
Regards,
Chan
December 29, 2011 at 10:20 am
There is no error. The script thinks it was successful. I've checked the file types and delimiters. This script has worked until just recently and works fine on my PC. It is not working on the production server any longer. I was told it is a Windows Server 2003 Enterprise x64.
December 29, 2011 at 12:09 pm
Stewart "Arturius" Campbell (12/29/2011)
SSIS has a File System Task component that can handle file copy and move operations, including the posssiblity of replacing the destination file if it already exists.
I looked at this, but I'm not an SSIS expert (by a long shot). The copy is just part of the script and the biggest problem is the file names and directories are variable based on the date. I wasn't sure how to set a variable that would update each run. It's also not just current date, it's based on the last run date pulled from a DB.
The other problem is that this only fixes one of the packages. I still have the issue of the other package creating a new file that is blank as well. There's not a way to do that with a File System Task.
December 30, 2011 at 2:47 am
This was removed by the editor as SPAM
December 30, 2011 at 3:02 pm
I'll have to research how to set variables from DB queries. I had not known you could do that with a task item. However, I still believe there is an issue with the environment.
I found out yesterday that there was a move to new hardward and an upgrade of the OS to Windows Server 2008. The SQL Server version stayed the same, but not the OS and hardware. I'm going to try to move the package to run from a different server. Or recompile with my Win 7 PC and rerun it in the new server environment.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply