Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8400 Visits: 19504
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 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)

Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8400 Visits: 19504
..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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 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.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8400 Visits: 19504
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 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
Chris Hurlbut
Chris Hurlbut
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1778 Visits: 540
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
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 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 rightSmile

Thanks,
10e5x
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7480 Visits: 15142
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search