May 4, 2010 at 12:22 am
Hi Buddies,
I'm converting SQL Server 2000 DTS packages to SSIS packages. In existing packages they've used system DSN to get connections in ActiveX Script Tasks.
Now I'm replaced ActiveX Script Tasks with Script Tasks because ActiveX Script Tasks are going to be deprecated.
I have question here: Can I use system DSN to get connection in Script Task packages.?
I have created .Net Providers\ODBC Data Provider connection and tried but got failed.
And Please suggest me which provider I can use, if I need to create SQL OLEDB connection to the Script Task Connection Manager.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
May 4, 2010 at 7:27 am
something like this?
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name') AS a;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 4, 2010 at 11:01 pm
Before employing the solution be sure that you enable the appropiate options from Surface Area Configuration to use OpenRowSet
Raunak J
May 4, 2010 at 11:54 pm
Hi,
I enabled configuration options in Surface Area Configuration and tried but not succeed.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
May 5, 2010 at 12:07 am
Suresh,
have you tried the Package migration wizard,
why not use the exceute DTS 2000 Package Task editor
Raunak J
May 5, 2010 at 12:12 am
Are you using a 64-bit machine by any chance?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 5, 2010 at 12:17 am
Hi,
In my existing DTS 2000 packages has ActiveX Script Task. I heard like the ActiveX Script task may be deprecated in a future release of SQL Server. So I need to use Script Task. Let me correct if I'm wrong.
That is the reason I'm doing all these DTS 2000 packages step by step without migration.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
May 5, 2010 at 12:23 am
Hi,
Are you using a 64-bit machine by any chance?
No, I'm not using 64-bit machine. It is 32-bit machine.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
May 5, 2010 at 12:40 am
I am assuming that you tried creating a new connection using Connection Manager?
What happened when you selected New Connection / ODBC? Were you not able to see your system DSN in the list ('Use User or System data source name')?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 5, 2010 at 12:56 am
Hi Phil Pakin,
You are right.
Initially I created a data source under Data Sources folder in Solution Explorer that is using System DSN.
Next I created a connection in Connection Managers, say "Source".
Now Can I use this "Source" connection in Script Task? or is there any alternative ?
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
May 5, 2010 at 1:01 am
Use the connection you defined in Connection Manager in the Script Task - that is the way to work.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 5, 2010 at 1:08 am
Yeah, I have tried it but not succeed. Here is the connection string:
SQLCon.ConnectionString = "Data Source=Source;Initial Catalog=BIW;User ID=" + Login + ";Password=" + Password + ";Trusted_Connection=True;"
Login and Password are variables that I have created.
Can you give me sample code?
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
May 5, 2010 at 5:01 am
Finally I've got it.
these lines I've added to my code:
Imports Microsoft.Data.Odbc
Dim conn As Odbc.OdbcConnection
Dim comm As Odbc.OdbcCommand
Dim conn As Odbc.OdbcConnection
Dim comm As Odbc.OdbcCommand
connectionString = "DSN=BI_Database;UID=" + Login + ";Pwd=" + Password + ";"
conn = New Odbc.OdbcConnection(connectionString)
conn.Open()
I really appreciate all your assistance on this. Thank you.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply