ODBC Destination in SSIS

I read that Integration Services does not come with built-in support for ODBC destinations, so I tried to write Script component as destination task.

My destination is odbc for oracle, and it failes with the message:

"Null password given, logon denied".

I can't understand it, I used with the same connection with datareader source and it works.

I spent a long time on this problem, please help me.

Thank you in advance

Noam

How do you create your connection object? Do you just take the connection string from the connection manager or call AcquireConnections on it? If you just take the connection string it will have the password filtered out.

Thanks.

|||

First of all, Thank you for your reply.

You are right, I called the AcquireConnections as you are see:

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

Dim connectionString As String

connectionString = Me.Connections.Connection.ConnectionString

odbcConn = New OdbcConnection(connectionString)

odbcConn.Open()

End Sub

I changed it, and moved the initialization of the connection to the PreExecute() function.

Now the compilation is succeed, but it is failed at the run time with the same error "Null Password given, logon denied".

Please Help!

Noam

|||

No, as I said before you should not construct your own ODBC connection. Instead, call AcquireConnection on the connection manager and cast the returned object to IDbConnection.

Something Like this:

Dim connection As IDbConnection

connection = Me.Connections.Connection.AcquireConnection(null) as IDbConnection

HTH.

|||

I tried as you suggested.

Dim connection As IDbConnection

connection = Me.Connections.Connection.AcquireConnection(Nothing)

Now, I get This:

"Unable to cast COM object of type 'System.__ComObject' to interface type 'System.Data.IDbConnection'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{9BB226F4-2AF3-37E7-B91D-3BB936FC0A7E}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))."

I'm stuck with this for 3 days (!!!).

Please your help!

Thanks in advance.

Noam

|||

Is the connection you created ADO.NET for ODBC or native ODBC?

You should use ADO.NET for ODBC.

Thanks.

ODBC Destination in SSIS

I read that Integration Services does not come with built-in support for ODBC destinations, so I tried to write Script component as destination task.

My destination is odbc for oracle, and it failes with the message:

"Null password given, logon denied".

I can't understand it, I used with the same connection with datareader source and it works.

I spent a long time on this problem, please help me.

Thank you in advance

Noam

How do you create your connection object? Do you just take the connection string from the connection manager or call AcquireConnections on it? If you just take the connection string it will have the password filtered out.

Thanks.

|||

First of all, Thank you for your reply.

You are right, I called the AcquireConnections as you are see:

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

Dim connectionString As String

connectionString = Me.Connections.Connection.ConnectionString

odbcConn = New OdbcConnection(connectionString)

odbcConn.Open()

End Sub

I changed it, and moved the initialization of the connection to the PreExecute() function.

Now the compilation is succeed, but it is failed at the run time with the same error "Null Password given, logon denied".

Please Help!

Noam

|||

No, as I said before you should not construct your own ODBC connection. Instead, call AcquireConnection on the connection manager and cast the returned object to IDbConnection.

Something Like this:

Dim connection As IDbConnection

connection = Me.Connections.Connection.AcquireConnection(null) as IDbConnection

HTH.

|||

I tried as you suggested.

Dim connection As IDbConnection

connection = Me.Connections.Connection.AcquireConnection(Nothing)

Now, I get This:

"Unable to cast COM object of type 'System.__ComObject' to interface type 'System.Data.IDbConnection'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{9BB226F4-2AF3-37E7-B91D-3BB936FC0A7E}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))."

I'm stuck with this for 3 days (!!!).

Please your help!

Thanks in advance.

Noam

|||

Is the connection you created ADO.NET for ODBC or native ODBC?

You should use ADO.NET for ODBC.

Thanks.

sql

ODBC connections and SSIS

so im trying to connect to an odbc source and use ado.net to pass some sql queries and then write back into this odbc connection. i am aware that ssis does not have direct capabilities to do this, but i wanted to see if anyone knew of generic help docs/url's that show how to do this? i am new to ssis, and am just trying to get general information.

See Douglas post here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=70469&SiteID=1

The code is also in SQL Books Online.|||

to be honest with you, i did see that thread you mention above, probably the only thread with realistic information. I am looking for something specifically in C#.

I am also wondering if there is any way to not have the SQL Command line filled for the ODBC connection source and use ODBC and a combination of ADO.net? Anyone have success with this?

I did search books online for the particualar phrase mentioned in the above thread..not too helpful.

Just wondering if anyone has had success writing C# ado.net to read and write using ODBC as a source?

thanks!

|||

If that's what you are after then I would try a dev forum if I were you: http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=12&SiteID=1

-Jamie

sql

ODBC connection with stored procedure on iSeries as data source

I have created a stored procedure on the iSeries that creates a cursor and opens it. I am trying to write my report to use the stored procedure. I cannot get the data source to work. How do I create my data source so that it uses the stored procedure? My SP has three parameters I am trying to pass from the report. The parms are created in the layout.

Thank you

Figured it out. Had to use this syntax: Call procname ('parm1', parm2, 'parm3')

|||

I was looking for an answer to my stored procedure problem and I came across your question/answer...I was hoping you'd help me out...we are using ODBC and an iseries and since I'm very much of a newbie to VB and .net, I am using point and click when I can...can you tell me what I'm doing wrong? I have an asp.net master and content pages...the content pages have datagrids (Gridview1) and a SQLDataSource control...Any help is greatly appreciated!

Imports IBM.Data.DB2.iSeries

Partial Class SSNName

Inherits System.Web.UI.Page

Dim ssnpsn As String = "123456789"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim conn As New iDB2Connection("DataSource=datasourcename")

Try

conn.Open()

Catch ex As Exception

End Try

Dim sqlSSN As String = "call libraryname.SP_QSSNNAME(@.ssn)"

Dim cmdSSN As New iDB2Command(sqlSSN, conn)

cmdSSN.Parameters.Add("@.ssn", iDB2DbType.iDB2Numeric, 9)

cmdSSN.Parameters("@.ssn").Value = ssnpsn

Dim dr As iDB2DataReader

dr = cmdSSN.ExecuteReader()

Gridview1.DataSource = dr

Gridview1.DataBind()

conn.Close()

End Sub

End Class

|||

I have been using the iSeries for some time now and I find this approach the best.

1. Create a new class under App_code

2. Create a function with the follwing code:

Shared Function GetCatByCatID(ByVal CatID As Integer) As System.Data.DataSet

Dim connectionString As String = ConfigurationManager.ConnectionStrings("gsafleetcs").ConnectionString

Dim cnn As IBM.Data.DB2.iSeries.iDB2Connection = New IBM.Data.DB2.iSeries.iDB2Connection(connectionString)

Dim proc As String = "GetCatInfo"

Dim dbCmd As IBM.Data.DB2.iSeries.iDB2Command = New IBM.Data.DB2.iSeries.iDB2Command

dbCmd.CommandText = proc

dbCmd.CommandType = Data.CommandType.StoredProcedure

dbCmd.Connection = cnn

Dim dbParam_ID As System.Data.IDataParameter = New IBM.Data.DB2.iSeries.iDB2Parameter

dbParam_ID.ParameterName = "@.CatID"

dbParam_ID.Value = CatID

dbParam_ID.DbType = Data.DbType.Int32

dbParam_ID.Direction = Data.ParameterDirection.Input

dbCmd.Parameters.Add(dbParam_ID)

Dim dataAdapter As IBM.Data.DB2.iSeries.iDB2DataAdapter = New IBM.Data.DB2.iSeries.iDB2DataAdapter

Dim dataSet As System.Data.DataSet = New System.Data.DataSet

dataAdapter.SelectCommand = dbCmd

dataAdapter.Fill(dataSet)

dbCmd.Dispose()

cnn.Close()

Return dataSet

End Function

3. Then in your asp page where the grid view create a Object datasource, unclick the Show only data components to expose you class name, then click next and choose you approiate method and finally define your parameter(s).

These Rebooks helped a lot:

1. IBM Redbook Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries

2. Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET

Good Luck

|||

Are you using the Report Designer?

In my Dataset definition I've defined my Name, Data Source, Command Type as 'Stored Procedure' and in the Query String I put the name of the stored procedure. When I execute it responds with

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Object reference not set to an instance of an object.

ADDITIONAL INFORMATION:

Object reference not set to an instance of an object. (System.Data)

BUTTONS:

OK

I know it finds the store procedure, becuase at first I had a typo and the error message stated it could not find it. When I fix the typo I recieved the above error message. Here is the stored procedure:

BEGIN
DECLARE C1 CURSOR FOR
SELECT PEMS . EMS1548 . EMSDCO , PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT , PEMS . FUELDET . FUDTYP , PEMS . FUELDET . FUDFQ AS GALS , PEMS . FUELDET . FUDFD AS DOLS , PEMS . FUELDET . FUDDAT
FROM PEMS . EMS1548 , PEMS . EMS1547 , PEMS . FUELDET
WHERE PEMS . EMS1548 . EMSDCO = PEMS . EMS1547 . EMSDPT AND PEMS . EMS1547 . EMSIDC = PEMS . FUELDET . FUDIDX AND
( PEMS . FUELDET . FUDTYP IN ( 'UNL' , 'DSL' ) ) AND ( PEMS . FUELDET . FUDEQT IN ( '022406' , '016460' , '026352' , '017068' , '023207' , '021870' , '019334' , '022754' , '020608' , '019337' ) )
ORDER BY PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT ;
OPEN C1 ;
END

Any ideas would be appreciated.

|||

I found the solution:

I was missing "RESULT SET 1" before the LANGUAGE SQL and "SET RESULT SETS CURSOR C1;" after the OPEN C1; statement

ODBC connection with stored procedure on iSeries as data source

I have created a stored procedure on the iSeries that creates a cursor and opens it. I am trying to write my report to use the stored procedure. I cannot get the data source to work. How do I create my data source so that it uses the stored procedure? My SP has three parameters I am trying to pass from the report. The parms are created in the layout.

Thank you

Figured it out. Had to use this syntax: Call procname ('parm1', parm2, 'parm3')

|||

I was looking for an answer to my stored procedure problem and I came across your question/answer...I was hoping you'd help me out...we are using ODBC and an iseries and since I'm very much of a newbie to VB and .net, I am using point and click when I can...can you tell me what I'm doing wrong? I have an asp.net master and content pages...the content pages have datagrids (Gridview1) and a SQLDataSource control...Any help is greatly appreciated!

Imports IBM.Data.DB2.iSeries

Partial Class SSNName

Inherits System.Web.UI.Page

Dim ssnpsn As String = "123456789"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim conn As New iDB2Connection("DataSource=datasourcename")

Try

conn.Open()

Catch ex As Exception

End Try

Dim sqlSSN As String = "call libraryname.SP_QSSNNAME(@.ssn)"

Dim cmdSSN As New iDB2Command(sqlSSN, conn)

cmdSSN.Parameters.Add("@.ssn", iDB2DbType.iDB2Numeric, 9)

cmdSSN.Parameters("@.ssn").Value = ssnpsn

Dim dr As iDB2DataReader

dr = cmdSSN.ExecuteReader()

Gridview1.DataSource = dr

Gridview1.DataBind()

conn.Close()

End Sub

End Class

|||

I have been using the iSeries for some time now and I find this approach the best.

1. Create a new class under App_code

2. Create a function with the follwing code:

Shared Function GetCatByCatID(ByVal CatID As Integer) As System.Data.DataSet

Dim connectionString As String = ConfigurationManager.ConnectionStrings("gsafleetcs").ConnectionString

Dim cnn As IBM.Data.DB2.iSeries.iDB2Connection = New IBM.Data.DB2.iSeries.iDB2Connection(connectionString)

Dim proc As String = "GetCatInfo"

Dim dbCmd As IBM.Data.DB2.iSeries.iDB2Command = New IBM.Data.DB2.iSeries.iDB2Command

dbCmd.CommandText = proc

dbCmd.CommandType = Data.CommandType.StoredProcedure

dbCmd.Connection = cnn

Dim dbParam_ID As System.Data.IDataParameter = New IBM.Data.DB2.iSeries.iDB2Parameter

dbParam_ID.ParameterName = "@.CatID"

dbParam_ID.Value = CatID

dbParam_ID.DbType = Data.DbType.Int32

dbParam_ID.Direction = Data.ParameterDirection.Input

dbCmd.Parameters.Add(dbParam_ID)

Dim dataAdapter As IBM.Data.DB2.iSeries.iDB2DataAdapter = New IBM.Data.DB2.iSeries.iDB2DataAdapter

Dim dataSet As System.Data.DataSet = New System.Data.DataSet

dataAdapter.SelectCommand = dbCmd

dataAdapter.Fill(dataSet)

dbCmd.Dispose()

cnn.Close()

Return dataSet

End Function

3. Then in your asp page where the grid view create a Object datasource, unclick the Show only data components to expose you class name, then click next and choose you approiate method and finally define your parameter(s).

These Rebooks helped a lot:

1. IBM Redbook Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries

2. Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET

Good Luck

|||

Are you using the Report Designer?

In my Dataset definition I've defined my Name, Data Source, Command Type as 'Stored Procedure' and in the Query String I put the name of the stored procedure. When I execute it responds with

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Object reference not set to an instance of an object.

ADDITIONAL INFORMATION:

Object reference not set to an instance of an object. (System.Data)

BUTTONS:

OK

I know it finds the store procedure, becuase at first I had a typo and the error message stated it could not find it. When I fix the typo I recieved the above error message. Here is the stored procedure:

BEGIN
DECLARE C1 CURSOR FOR
SELECT PEMS . EMS1548 . EMSDCO , PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT , PEMS . FUELDET . FUDTYP , PEMS . FUELDET . FUDFQ AS GALS , PEMS . FUELDET . FUDFD AS DOLS , PEMS . FUELDET . FUDDAT
FROM PEMS . EMS1548 , PEMS . EMS1547 , PEMS . FUELDET
WHERE PEMS . EMS1548 . EMSDCO = PEMS . EMS1547 . EMSDPT AND PEMS . EMS1547 . EMSIDC = PEMS . FUELDET . FUDIDX AND
( PEMS . FUELDET . FUDTYP IN ( 'UNL' , 'DSL' ) ) AND ( PEMS . FUELDET . FUDEQT IN ( '022406' , '016460' , '026352' , '017068' , '023207' , '021870' , '019334' , '022754' , '020608' , '019337' ) )
ORDER BY PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT ;
OPEN C1 ;
END

Any ideas would be appreciated.

|||

I found the solution:

I was missing "RESULT SET 1" before the LANGUAGE SQL and "SET RESULT SETS CURSOR C1;" after the OPEN C1; statement

ODBC connection to SQL server DB

I'm writing a VB app to read and write data to an SQL server database.

I have successfully done so using ADO however I now need to do it using ODBC.

I have set up the appropriate DSN and have no trouble reading data from the database but when I try to write to it I get a message indicating that the database is open for read only access.

I'm no sure whether the restriction is and the VB, ODBC, or database level.

Here's the code which opens the database and recordset.

Set dbsWarehouseServer = OpenDatabase(ODBCDSName, _
dbDriverNoPrompt, False, _
"DSN=" & ODBCDSName)

Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", dbOpenDynaset)

Any help is greatly appreciated.What if you changed this to your statement:

Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", adOpenDynamic, adLockOptimistic)

Just a thought.|||Thank's for the idea. No luck though.
Not sure but maybe the restriction is at the ODBC or SQL level although I have no trouble with ADO so I suspect ODBC.|||Just wondering...is RailID an integer value?

If so, wouldn't you use:

Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = " & RailID & "", dbOpenDynaset)

instead of:

Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", dbOpenDynaset)

Again, just another thought but probably not on the right path.|||When you create the ODBC connection on your pc, which username are you using, I believe that is not 'sa'. You must be using another username created in the SQL server user login.
Check the right for that username you have created. Does it have the right to write to that specify database. If you put it to db owner, you can do anything with that database.

Obscuring T-SQL code from RDL File and SQL Server

I would like to write some reports that will query T-SQL for information, but
I don't what anyone to be able to see the T-SQL code. So I can not store the
T-SQLin the RDL file, or put it in a SP. Also can't even use and encrypted
SP because someone would have to have the source code to create the T-SQL.
So what options are available to hide the T-SQL code, but not allow anyone to
see it, or change it?On Apr 17, 7:50 pm, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> I would like to write some reports that will query T-SQL for information, but
> I don't what anyone to be able to see the T-SQL code. So I can not store the
> T-SQLin the RDL file, or put it in a SP. Also can't even use and encrypted
> SP because someone would have to have the source code to create the T-SQL.
> So what options are available to hide the T-SQL code, but not allow anyone to
> see it, or change it?
As far as I know, there is not really much else that can be done to
restrict access to the T-SQL or stored procedure(s). Sorry that I
could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||I would guess if a compiled piece of code could be used as a datasource then
potentially this might work. Don't suppose you know how to create a piece of
compiled code that can be used as a data source to a reporting services
report.
"EMartinez" wrote:
> On Apr 17, 7:50 pm, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> > I would like to write some reports that will query T-SQL for information, but
> > I don't what anyone to be able to see the T-SQL code. So I can not store the
> > T-SQLin the RDL file, or put it in a SP. Also can't even use and encrypted
> > SP because someone would have to have the source code to create the T-SQL.
> > So what options are available to hide the T-SQL code, but not allow anyone to
> > see it, or change it?
> As far as I know, there is not really much else that can be done to
> restrict access to the T-SQL or stored procedure(s). Sorry that I
> could not be of further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>