use adodb to get data

Question

use adodb to get data

hamzajosh

SSChasing Mays

Points: 632
More actions
December 1, 2011 at 1:45 pm

#247733

Hi, I am using ADODB to get data from SQL server in a script task using code below
Dim Server, Database, Username, Password
Dim ADOCN As New ADODB.Connection
Dim ADORS As New ADODB.Recordset
Dim Catno, X, Y, Z, StrSQL, StrCON, Main_No
Server = "xxx"
Database = "yyy"
Username = "zzz
Password = "aaa"
StrCON = "DRIVER={SQL Server};SERVER=" & Server & ";" & _
"DATABASE=" & Database & ";UID=" & Username & ";PWD=" & Password
ADOCN.Open StrCON
StrSQL = "select * from iv00102 where itemnmbr = '00016'"
ADORS.Open StrSQL, ADOCN
How can i use connection manager and use the same code to connect to data source defined in the package for using the same code.
What do i replace these lines with to use a predefined connection called "SQLConnection"
StrCON = "DRIVER={SQL Server};SERVER=" & Server & ";" & _
"DATABASE=" & Database & ";UID=" & Username & ";PWD=" & Password
ADOCN.Open StrCON
Please advise. Thanks in advance.

Viewing 9 posts - 1 through 9 (of 9 total)

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

kramaswamy SSCoach Points: 18181 More actions · Answer 1

You're connecting to a SQL Server database? Just use the OLE DB Source as your component and follow the on-screen steps to connect, then use the SQL Command option as your source and put your query in.

You shouldn't use SELECT * btw, change it to get only the fields you need.

hamzajosh SSChasing Mays Points: 632 More actions · Answer 2

I need to connect to it in a script task. I need to do some processing on the data and save it to other tables. I do need to do it in script task FYI.

Got the point about not using select *. thanks for that.

kramaswamy SSCoach Points: 18181 More actions · Answer 3

I'd really strongly recommend that you instead do the processing using SSIS components. That's the whole point of using SSIS Pretty much everything you could do with the script component, you can do with the other built-in components.

Why don't you post what you're trying to accomplish and we can try and find a way to help you to do it with the other components?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

I have on very rare occasion used a script task to read SQL data in SSIS, and in those cases only when I had to do something that was very difficult in SQL. Unfortunately I can't name the cases right now but they were kind of last resorts. I would strongly recommend not doing this. You might add a script transform in the pipeline to to the manipulations but I would try VERY hard to NOT use it as a source in this case.

CEWII

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 5

Check this link.

http://blogs.msdn.com/b/mattm/archive/2008/08/22/accessing-oledb-connection-managers-in-a-script.aspx

Hope that it helps.

Thanks...Chris

hamzajosh SSChasing Mays Points: 632 More actions · Answer 6

It's too late for that. I have coded many packages using these techniques. Just need to change the way the connection to server is done. It would help me a lot if someone could tell me how to replace the code to use connection manager.

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 7

CGSJohnson

SSCertifiable

Points: 7208

December 2, 2011 at 9:28 am

#1416007

Did you see my post?

- Chris

hamzajosh SSChasing Mays Points: 632 More actions · Answer 8

Chris, sorry, yes I just saw it. i had the browser open and didn't refresh before posting the reply. I think that would work. I am trying it now to see. thanks

use adodb to get data

Cookies on SQLServerCentral