Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to store the Count(*) value fro datareader to int. SSIS 2005 Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 3:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
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 [KEY]= '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
Post #1397661
Posted Tuesday, December 18, 2012 6:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:49 PM
Points: 4,973, Visits: 11,660
Have you tried setting a breakpoint and debugging line by line? It might illuminate the problem.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1397739
Posted Tuesday, December 18, 2012 7:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
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)
Post #1397798
Posted Tuesday, December 18, 2012 10:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:49 PM
Points: 4,973, Visits: 11,660
..which you will quickly diagnose when running in debug. Sounds like the datareader may not be getting populated at all.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1397927
Posted Wednesday, December 19, 2012 2:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
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.
Post #1398244
Posted Wednesday, December 19, 2012 11:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:49 PM
Points: 4,973, Visits: 11,660
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1398600
Posted Wednesday, December 19, 2012 5:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
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
Post #1398713
Posted Thursday, December 20, 2012 1:40 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:18 PM
Points: 1,428, Visits: 459
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"
Post #1399096
Posted Friday, December 21, 2012 1:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
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
Post #1399270
Posted Friday, December 21, 2012 2:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 5,228, Visits: 9,443
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
Post #1399281
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse