Post reply

How to store the Count(*) value fro datareader to int. SSIS 2005

  • December 18, 2012 at 3:42 am

    #272205

    Hi all,

    I have been stucked with this for quite a while. I have a script:

    Dim sqlConnStr As String = "Data Source=" & RdVarStr("DB_Server_Name") & ";Initial Catalog=" & RdVarStr("DB_Init_Cat") & ";Integrated Security=True;"

    Dim rowCounter As Integer

    Using sqlConn As New Data.SqlClient.SqlConnection(sqlConnStr)

    sqlConn.Open()

    Using sqlCmd As New Data.SqlClient.SqlCommand("SELECT Count(*) from Target where WORKED_HRS >= (select VALUE from CONFIG where MODULE = 'SSIS TACSReport' and = 'Var_WorkHrs_Limit') +':00'", sqlConn)

    Dim sqlDtReader As Data.SqlClient.SqlDataReader

    sqlDtReader = sqlCmd.ExecuteReader()

    rowCounter = sqlDtReader.GetInt32(0)

    sqlDtReader.Close()

    End Using

    End Using

    Return rowCounter

    It seems simple and i tot this is it, however i keep getting error:

    Invalid attempt to read when no data is present.

    But i am sure there is value as i did a sqlDtReader.Read to check. What wrong and what to do?

    Thanks,

    10e5x

  • December 18, 2012 at 6:08 am

    #1569518

    Have you tried setting a breakpoint and debugging line by line? It might illuminate the problem.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 18, 2012 at 7:38 am

    #1569571

    HI phil,

    thanks for helping once again. I will trying what u suggest later, currently out of office. But i guess the problem is most likely to do with getting the count(*) and store it as an int in an int variable

    rowCounter = sqlDtReader.GetInt32(0)

  • December 18, 2012 at 10:44 am

    #1569682

    ..which you will quickly diagnose when running in debug. Sounds like the datareader may not be getting populated at all.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 19, 2012 at 2:15 am

    #1569936

    Hi phil,

    I heed ur advice and did a debug. It was strange it doesnt return me any data. Hence i read through my script and realize it may be due to that the prog did not read it row by row even though i am only expecting 1 row with 1 column result. Therefore i added in a While(reader.read()).... cycle and it WORKS. although i do not uds why i need a loop when it is only one row, but it does solved my problem.

    Thanks for your help.

  • December 19, 2012 at 11:59 am

    #1570250

    10e5x (12/19/2012)

    Hi phil,

    I heed ur advice and did a debug. It was strange it doesnt return me any data. Hence i read through my script and realize it may be due to that the prog did not read it row by row even though i am only expecting 1 row with 1 column result. Therefore i added in a While(reader.read()).... cycle and it WORKS. although i do not uds why i need a loop when it is only one row, but it does solved my problem.

    Thanks for your help.

    Excellent - I should have noticed that, but well done.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 19, 2012 at 5:20 pm

    #1570347

    Hi phil,

    Thanks for helping all these while. With all the help from amazing users of this forum, finally i am wrapping up my first project in my new dept. Now i am asked to create a .cmd file to run my SSIS package, which i have not figured out how to. Do u have any guide/article for me to read so i know how to start?

    Thanks,

    10e5x

  • December 20, 2012 at 1:40 pm

    #1570647

    Here is a batch file to execute ssis packages:

    the /DECRPYT is where you would put the package password (if there is one)

    The following illustrates using the 64 bit bit version and the 32 bit version

    rem this will be a batch file to call ssis packages

    rem "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTEXEC.exe" /f "C:\Subversion\Test\Test\package.dtsx" /DECRYPT password"

    "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTEXEC.exe " /f "C:\Subversion\Test\Test\package.dtsx" /DECRYPT password"

  • December 21, 2012 at 1:52 am

    #1570788

    Hi Churlbut,

    Thanks for replying, wasnt expecting that but this really explain why this forum is so good.

    This is my first time working with batch file or cmd file. I dont uds what u tryng to do. U pasted two lines of codes, which goes to which. I am guessing some goes to a notepad file am i right? And as for the path u states, which are the standards, which are dependent on my file location?

    Just hope i can get that right:)

    Thanks,

    10e5x

  • December 21, 2012 at 2:22 am

    #1570799

    Yes, paste the code into notepad, edit it so that it's right for your environment, and save it as a .bat or .cmd file. Make sure the path to the executable is the same as on your computer, and likewise for the .dtsx file. If your package is stored in msdb instead of the file system, you'll have to tweak the command a bit - just search for the syntax for dtexec.exe to find out how to do that.

    John

  • December 21, 2012 at 2:29 am

    #1570803

    Thanks got it! I will paste

    "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTEXEC.exe " /f "C:\Subversion\Test\Test\package.dtsx" /DECRYPT password"

    into a notepad i do the necessary.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply