For Next Loop not Looping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

For Next Loop not Looping

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-12-06 : 14:45:05

I have the following VB code in a script task in an SSIS package:


                For Each myFile In files
                    fileName = myFile.ToString
                    dateStamp = Mid(fileName, (fileName.LastIndexOf("_") + 2), 8)
                    dateStamp2 = Mid(fileName, (fileName.LastIndexOf("_") + 1), 9)
                    If IsNumeric(dateStamp) Then
                        If strWeekday = "Monday" And CDbl(dateStamp) < CDbl(strDateToday) Then
                            Now = DateTime.Now
                            sqlCmdText = "IF EXISTS (SELECT FileName FROM R9.R9RefreshExpectedFiles WHERE FileName = " & fileName.Replace(dateStamp2, "").Replace("D:\SSIS\R9\Hewitt\Import\", "") & ")"
                            sqlCmdText = sqlCmdText & " INSERT INTO R9.DataRefreshErrors([Date],Task,ObjectTable,RuleID,ErrorNumber,ErrorMessage) Values('" & Now & "','Check File Dates','','','','" & fileName.Replace("D:\SSIS\R9\Hewitt\Import\", "") & " may be outdated.')"
                            sqlCmd = New SqlCommand(sqlCmdText, sqlConn)
                            rdrReader = sqlCmd.ExecuteReader()
                            rdrReader.Close()
                        ElseIf strWeekday = "Saturday" And CDbl(dateStamp) < CDbl(strDateToday) - 1 Then
                            Now = DateTime.Now
                            sqlCmdText = "IF EXISTS (SELECT FileName FROM R9.R9RefreshExpectedFiles WHERE FileName = " & fileName.Replace(dateStamp2, "").Replace("D:\SSIS\R9\Hewitt\Import\", "") & ")"
                            sqlCmdText = sqlCmdText & " INSERT INTO R9.DataRefreshErrors([Date],Task,ObjectTable,RuleID,ErrorNumber,ErrorMessage) Values('" & Now & "','Check File Dates','','','','" & fileName.Replace("D:\SSIS\R9\Hewitt\Import\", "") & " may be outdated.')"
                            sqlCmd = New SqlCommand(sqlCmdText, sqlConn)
                            rdrReader = sqlCmd.ExecuteReader()
                            rdrReader.Close()
                        End If
                    End If
                Next

There are 110 files in the "files" directory, and many of them meet the conditions of the IF statement, but the loop is only executing once. Does anybody see what I'm missing?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-06 : 14:52:31

either it is erroring out -- probably on this line: sqlCmd.ExecuteReader() -- or, more likely, the files aren't meeting the conditions.

for debugging, you can add an ELSE at the end of the if/elseif statement and have it log something, or msgbox something.

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-12-06 : 15:03:35

At least 40 files definitely meet the conditions. I'll try adding an ELSE and see what that tells me.

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-12-06 : 15:46:10

OK, I put a try.catch around sqlCmd.ExecuteReader() and got a lengthy error message so that line is indeed the problem. Here is the first line of the error:

System.Data.SqlClient.SqlException: The multi-part identifier "HRMS_CDS_CON176568.TXT" could not be bound.

"HRMS_CDS_CON176568.TXT" is the name of the file. Guess I'll have to debug the SQL and possibly the connection.

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-12-06 : 15:57:34

Got it. This line:

"IF EXISTS (SELECT FileName FROM R9.R9RefreshExpectedFiles WHERE FileName = " & fileName.Replace(dateStamp2, "").Replace("D:\SSIS\R9\Hewitt\Import\", "") & ")"

Should be:

"IF EXISTS (SELECT FileName FROM R9.R9RefreshExpectedFiles WHERE FileName = '" & fileName.Replace(dateStamp2, "").Replace("D:\SSIS\R9\Hewitt\Import\", "") & "')"

Note the single quotes that were missing before and after the file name. D'oh!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources