Showing posts with label source. Show all posts
Showing posts with label source. Show all posts

Tuesday, March 27, 2012

Can't change the name of a OLE DB Source component name

Hi,

I've created an application that create package with a dataflow. The package with the dataflow and the ole db Source was created succesfully but for some reason the name of the Ole DB Source dosen't change programmitcally It's stays with the default name "OLE DB Source". This is very problematic because I want to add another Ole Db source and the package won't load because there will be two components with the same name.

Thanks.

This is the Code, It's from msdn exmples:

Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Module Module1

Sub Main()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _
New Microsoft.SqlServer.Dts.Runtime.Package()
Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Add an OLEDB connection manager to the package.
Dim cm As ConnectionManager = package.Connections.Add("OLEDB")
cm.Name = "OLEDB ConnectionManager"
cm.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=AdventureWorks;Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;"

' Add an OLE DB source to the data flow.
Dim component As IDTSComponentMetaData90 = _
dataFlowTask.ComponentMetaDataCollection.New()
component.Name = "AdventureWorks;Product"
component.ComponentClassID = "DTSAdapter.OleDbSource.1"
' You can also use the CLSID of the component instead of the PROGID.
'component.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";

' Get the design time instance of the component.
Dim instance As CManagedComponentWrapper = component.Instantiate()

' Initialize the component.
instance.ProvideComponentProperties()

' Specify the connection manager.
If component.RuntimeConnectionCollection.Count > 0 Then
component.RuntimeConnectionCollection(0).ConnectionManager = _
DtsConvert.ToConnectionManager90(package.Connections(0))
End If

' Set the custom properties.
instance.SetComponentProperty("AccessMode", 2)
instance.SetComponentProperty("SqlCommand", _
"Select * from Production.Product")

' Reinitialize the metadata.
instance.AcquireConnections(vbNull)
instance.ReinitializeMetaData()
instance.ReleaseConnections()

' Add other components to the data flow and connect them.

End Sub

End Module

If I remember correctly, the Name should be set after the call to

instance.ProvideComponentProperties();

|||Thanks It works

Sunday, March 25, 2012

Can't assign connection manager object to DataReader Source

I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.

Problem:

When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:

Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager

I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:

"Not all connection managers have been set. Set all connection managers."

When I click refresh the warning message changes to:

"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."

I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.

Thanks, -- Mike

I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!

Hector

|||

I had the same problem because I was using the wrong Provider when specifying my Data Source.

Hope it helps,

Nuno

|||

I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.

|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||

Hello.

I read that you found a solution, but I didn't understand it very well.

Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source

Thank you.

|||

Data Reader source accepts connection manager of Type ADO.NET only.

so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.

open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.

|||thank you for your reply. i resolve the problem.|||

hello,

i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.

the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."

Can you help me?

Can't assign connection manager object to DataReader Source

I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.

Problem:

When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:

Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager

I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:

"Not all connection managers have been set. Set all connection managers."

When I click refresh the warning message changes to:

"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."

I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.

Thanks, -- Mike

I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!

Hector

|||

I had the same problem because I was using the wrong Provider when specifying my Data Source.

Hope it helps,

Nuno

|||

I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.

|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||

Hello.

I read that you found a solution, but I didn't understand it very well.

Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source

Thank you.

|||

Data Reader source accepts connection manager of Type ADO.NET only.

so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.

open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.

|||thank you for your reply. i resolve the problem.|||

hello,

i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.

the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."

Can you help me?

Can't assign connection manager object to DataReader Source

I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.

Problem:

When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:

Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager

I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:

"Not all connection managers have been set. Set all connection managers."

When I click refresh the warning message changes to:

"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."

I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.

Thanks, -- Mike

I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!

Hector

|||

I had the same problem because I was using the wrong Provider when specifying my Data Source.

Hope it helps,

Nuno

|||

I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.

|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||

Hello.

I read that you found a solution, but I didn't understand it very well.

Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source

Thank you.

|||

Data Reader source accepts connection manager of Type ADO.NET only.

so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.

open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.

|||thank you for your reply. i resolve the problem.|||

hello,

i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.

the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."

Can you help me?

Can't assign connection manager object to DataReader Source

I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.

Problem:

When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:

Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager

I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:

"Not all connection managers have been set. Set all connection managers."

When I click refresh the warning message changes to:

"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."

I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.

Thanks, -- Mike

I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!

Hector

|||

I had the same problem because I was using the wrong Provider when specifying my Data Source.

Hope it helps,

Nuno

|||

I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.

|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||

Hello.

I read that you found a solution, but I didn't understand it very well.

Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source

Thank you.

|||

Data Reader source accepts connection manager of Type ADO.NET only.

so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.

open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.

|||thank you for your reply. i resolve the problem.|||

hello,

i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.

the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."

Can you help me?

Can't assign connection manager object to DataReader Source

I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.

Problem:

When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:

Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager

I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:

"Not all connection managers have been set. Set all connection managers."

When I click refresh the warning message changes to:

"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."

I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.

Thanks, -- Mike

I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!

Hector

|||

I had the same problem because I was using the wrong Provider when specifying my Data Source.

Hope it helps,

Nuno

|||

I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.

|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||

Hello.

I read that you found a solution, but I didn't understand it very well.

Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source

Thank you.

|||

Data Reader source accepts connection manager of Type ADO.NET only.

so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.

open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.

|||thank you for your reply. i resolve the problem.|||

hello,

i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.

the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."

Can you help me?

sql

Sunday, March 11, 2012

Cannot view browser or load metadata in KPI tab

Why is it that after generating the cube with all the connections to the database setup ( in data source etc) i cannot browse the cube and has the following error message:Either the user, xxxx, does not have access to the xxxx database, or the database does not exist. (Microsoft SQL Server 2005 Analysis Services)

I have created an sql server auth. account in the database and use it in AS. Thanks.

Regards

Alu

Sorry I did not deploy. :) Thanks.

Thursday, March 8, 2012

Cannot update. Database or object are read-only

I have to build application Excel (interface) - SQL Server (backend)

I've created Data Source File "test.dsn" and tested succesfully.
SQL Server database in on the other machine.
This is a code that I am using.

And error message I am geting is "Cannot update. Database or object are read-only".

Dim cn As ADODB.Connection
Dim strSQL As String

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\pd\Desktop\Development\test.xls;" & _
"Extended Properties=Excel 8.0"


strSQL = "INSERT INTO [DSN = test.dsn].Region Values(5, 'ab');"


cn.Execute strSQL

cn.Close
Set cn = Nothing

Read-only of databse properties is not selected.If I get it right you're attempting to insert a row into excel. As long as I know it's not possible because you cannot update, insert records throught Jet provider. mojza|||Your query is unclear . Understanding you are trying to update region table In sql database .
if that's case you should then rather open an ODBC or OLE DB connection to SQL server

cn.Provider = "sqloledb"
cn.Properties("Data Source") = ServerName
cn.Properties("Initial Catalog") = DatabaseName

' If Windows NT authentication.
cn.Properties("Integrated Security")= "SSPI"

'If SQL Authentication
cn.Properties("User ID")= UserID
cn.Properties("Password") = pwd

' Open the database.
cn.Open

Dim strSQL as string

strSQL "INSERT INTO dbo.Region Values(5, 'ab')"

cn.execute strSQL

cn.close
set cn = nothing

Sunday, February 19, 2012

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce this issue.

1. I could set the value of the "SourceConnection" property to the name of the connection I want to use in my bulk insert task and it works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.
|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce this issue.

1. I could set the value of the "SourceConnection" property to the name of the connection I want to use in my bulk insert task and it works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.
|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce

this issue.

1. I could set the value of the "SourceConnection" property to

the name of the connection I want to use in my bulk insert task and it

works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.

|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce

this issue.

1. I could set the value of the "SourceConnection" property to

the name of the connection I want to use in my bulk insert task and it

works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.

|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Cannot Set Connection String For Bulk Insert Source Connection

I came across something strange today. I was wondering I was doing something wrong.

I want to go through a directory and get files and assign the Fully Qualified File Name to a User Variable. I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow Task and File System task. It is NOT working for Bulk Insert. Did you come across this or am I doing something wrong?

- Expressions
ConnectionString @.[User::FileName]

Did anyone come across this before?

Thanks

Sutha

Seems to be a problem to me, it does not work within the context of a for each loop, keep getting the error message, connection not valid.

I have written this script and placed it into an activex task before the bulk insert, this seems to fix it.

Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")
connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()
connmgr.AcquireConnection(Nothing)

Hope this helps !

Mark|||

Cheers Mark. It does work fine. I put a script task inside Foreach, just before Bulk Insert to set the connections.

Still thinks its a Bug. Anyone from MS can confirm it. If so do you want me to raise a Bug for this?

Thanks for your help

Sutha

|||

Hello Sutha,

I am trying to achieve the same. Can you help me.

Foreach loop container is reading all the files in the specified folder.

I can also read the variable. But I am having difficulties in assiging this variable file name to Bulk Insert, connection, Source connection.

thanks & regards

radha.

|||I could not reproduce this issue.

1. I could set the value of the "SourceConnection" property to the name of the connection I want to use in my bulk insert task and it works.

2. I can also assign the value of the "ConnectionString" property of the connection to a variable that points to the file and it works.
|||

I am having same issue:

Simple package: Foreach Loop, inside ForEach Loop is a Bulk Insert Task.

I set the variable User::FileName in the variable Mappings of the Foreach Loop. I create a FlatFile connection in connection manager and go into the Expressions of the flat file connection and set the ConnectionString to be @.[User::FileName]. I set the connection in the Bulk Insert to be the Flat file connection I created and I run the package... I get the error: "The specified connection "XYZ Files" is either not valid, or points to an invalid object. To continue, specify a valid connection." Its very reproduceable.

Been fighting with this all day... can someone post step-by-step how to do the workaround where the script sets the connection?

|||Dim connmgr As ConnectionManager = Dts.Connections("Name of Flat File Conn Manager")
connmgr.ConnectionString = Dts.Variables("Var Name of Full path and filename").Value.ToString()
connmgr.AcquireConnection(Nothing)|||See this link for a Dec 27 Blog. http://denster.spaces.live.com/

Thursday, February 16, 2012

Cannot select Provider in Data Source Wizard Connection Manager

I think I either have an option set wrong or installed something wrong. When
I try to create a New Data Source in Data Model project, the dropdown box for
Provider is disabled and so SQLclient Data Provider is all I can do. I would
like to add a connection to an ODBC data source, which I should be able to
do. Any suggestions on what I can change to enable me to select the Provider?Semantic modelling in Model Designer supports native SQL provider only.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shane_SDE" <ShaneSDE@.discussions.microsoft.com> wrote in message
news:054075DE-92DF-4127-9B53-0DB5FEB43670@.microsoft.com...
>I think I either have an option set wrong or installed something wrong.
>When
> I try to create a New Data Source in Data Model project, the dropdown box
> for
> Provider is disabled and so SQLclient Data Provider is all I can do. I
> would
> like to add a connection to an ODBC data source, which I should be able to
> do. Any suggestions on what I can change to enable me to select the
> Provider?