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

Question

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

10e5x

Default port

Points: 1445
More actions
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

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

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

10e5x Default port Points: 1445 More actions · Answer 2

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)

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

10e5x Default port Points: 1445 More actions · Answer 4

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.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 5

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

10e5x Default port Points: 1445 More actions · Answer 6

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

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 7

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"

10e5x Default port Points: 1445 More actions · Answer 8

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

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

10e5x Default port Points: 1445 More actions · Answer 10

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.