July 14, 2009 at 8:29 am
Hi,
My SSIS package has a data flow task that uses a data source whose connection string property changes at runtime. The OLEDB source was validated at design time connecting to an Oracle database. Before performing the data flow task I've a script that changes the connectionstring value of the OLEDB source.
dataSource = dv(0)("sqlStatement_Connection_ServerName").ToString()
databaseName = dv(0)("sqlStatement_Connection_InitialCatalog").ToString()
userId = dv(0)("sqlStatement_Connection_UserName").ToString
password = dv(0)("sqlStatement_Connection_Password").ToString
provider = dv(0)("sqlStatement_Connection_Type").ToString
connectionString = "Data Source=" + dataSource + ";User ID=" + userId + ";Password=" + password + ";Provider=" + provider + ";"
If Not String.IsNullOrEmpty(databaseName) Then
''--The initial catalog would be used only if the connection is made to a SQL database.
connectionString = connectionString + "InitialCatalog=" + databaseName + ";"
End If
''--Assign the connection string to the connection object.
Dim con As ConnectionManager = Dts.Connections("Thread1Connection")
''con.ConnectionString = connectionString
con.Properties("ConnectionString").SetValue(con, connectionString)
When the task is run, as long as the connection is made to Oracle it runs fine; but as soon as the connection string is set to SQL, it breaks with error:
Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name 'MyTable'".
And
[DTS.Pipeline] Error: "component "Alert values from Oracle" (585)" failed validation and returned validation status "VS_ISBROKEN".
Another thing that I noted is, when I log the connection string just assigned it doesn't show the password (while the string "connectionString" does). Dts.Events.FireInformation(0, "connectionString - " + connectionString, "connectionString - " + connectionString, String.Empty, 0, False) shows the password value.
Dts.Events.FireInformation(0, Dts.Connections("Thread1Connection").ConnectionString.ToString(), Dts.Connections("Thread1Connection").ConnectionString.ToString(), String.Empty, 0, False) doesn't show the password value. I assume SSIS does that for protecting credentials.
Any ideas on what I'm missing out here? Thanks.
July 14, 2009 at 8:40 am
Can you use SQL Native Client to connect to Oracle?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 14, 2009 at 9:00 am
I assume you meant changing my OLEDB source type from Oracle to SQL Native Client. When I do that then the oracle query breaks with the following error:
[Alert values from Oracle [585]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00923: FROM keyword not found where expected ".
.
Or did you mean something else?
Edit: The Oracle query runs fine when the source provider is Oracle. There are no syntactical mistakes. {the error message seems to indicate so}.
July 14, 2009 at 9:04 am
The error message specifically mentions SQL Native Client - making me wonder whether you tried to connect to Oracle using the wrong provider ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 14, 2009 at 9:25 am
Phil Parkin (7/14/2009)
The error message specifically mentions SQL Native Client - making me wonder whether you tried to connect to Oracle using the wrong provider ...
Umm.. let me try to explain what I'm doing. The idea is to run various queries through the same package across SQL and Oracle. The queries are numerous and the dB they hit is quite big. So I have multiple For..Loop containers to implement a thread kind of thing. At design time I do not know which query will hit which thread so I assign the query and the connection string to the source at run time. Each container has its own OLEDB data source. At design time I had the source configured with Oracle database but re-assign the connection string before a SQL statement is executed.
And I think there's the problem. The SQL statement that I expect to run against SQL Server is still being executed against Oracle {that's why it can't see 'MyTable'}. While I expect it to execute correctly as I have reset the connection string. The output column names and types are same for both Oracle & SQL queries.
July 14, 2009 at 9:41 am
Got it!! :w00t:
I had to modify my query to include the absolute path of the SQL Server database. Somehow the "Initial Catalog" thing in the connection string doesn't seem to work.
So the query became:
select column1 from dbname.schema.mytable
Thank you Phil, for your time.
July 14, 2009 at 9:52 am
Nice work and no problem - I don't think I helped much. By the sound of it, you already know way more than I do anyway! Glad that you tracked it down.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 20, 2012 at 11:00 am
I'm a newbie in SSIS and i was trying to find out why i can't make it with data access mode= sql command from variable, for hours! now i know that was the dbname.schema.tablename thing! it works! thanks a lot....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply