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 8 (of 8 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