Connect to remote instance of SSIS from SSMS using SQL Login?

I have installed a SQL Server 2005 Standard Edition instance with SSIS on Window Server 2003 x64. From my PC, I am able to connect to the Integration Services on the server using Windows Authentication but not with a local SQL login - and the option to change authentication methods is greyed out.

Is it possible to remotely connect to SSIS using a SQL login rather than a Windows login? If so, any ideas or references for configuring it as such?

Thanks.

- Lance

Per the post linked below, it's not supported.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=316143&SiteID=1

Connect To Oracle 10g 64 Bit using SQL Server Integration Services 2005 64 Bit

I am trying to create a oracle 10g connection in SSIS using connection manager and i am getting the following error,

Test Connection failed because of an error in initializing provider. Oracle client and networking

components were not found. These components are supplied by Oracle corporation and are part of the

Oracle version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.

Can someone please help.

Thanks.

If your operating System and VS2005 are 32bits then you need to download and install the Oracle 10g client for Windows 32bits, when I last downloaded it was more than 1gig install it and create your connection to the database make sure your TNS ORA works before adding SSIS connection to Oracle. The client tools are free but you need an Oracle Technet account which you can create for free. The Developer is also free it let you see Oracle tables and databases. If you are in an employer's network I find connecting with Named Pipes is easier than TCP/IP. One more thing watch the installer. Hope this helps.

http://www.oracle.com/technology/software/index.html

I forgot to add you must compile your code with AnyCPU.

|||

Thanks for your reply Caddre..

But.. i have MS 2003 64 bit OS.. i have installed oracle 10g 64 bit server.. i can successfully connect thru sqlplus.. no problem...

I am missing something on SQL 2005 and i am not sure what it is...

|||

So you are saying you have installed Oracle 10g 64bits client tools in the box? I know connecting through SQL plus does not solve development connection issue you need the client tools in your box this time 64bits. Another thing you are not missing anything but I think VS2005 unless you buy 64bits full version is 32bits that is not all your setup is 64bits, so you need to either make it all 64bits or develop in 32bits and deploy in 64bits because I read there are issues known issues.

|||

yes - as far as i know all are 64bit

I cannot connect thru both providers - OLEDB and Net Oracle client

|||

Try this recent Microsoft support article steps, I will still look for more info.

http://support.microsoft.com/kb/255084

|||

Hi Caddre,

Either you can have a look at summarized article here. or You may look for "Getting SSIS Packages To Run On A 64 Bit Box" in Sutha Thiru's Blog .

Thanks

Subhash Subramanyam

|||

thanks subash.. awesome..

those articles helped me to fix the issue...

appreciate yours and caddre help on this..

|||I am glad to see your problem is resolved and Subhash thanks for the articles much appreciated.

Connect to object explorer but cannot connect to Integration services.

I try to import my SSIS packages which are created using the Business Intelligent studio into SQL server 2005 but I cannot do that.

I am running SQL server 2005 enterprise edition.

Also when i tried to connect to Integration services using Object explorer, it only prompt me for server name. I cannot change the username and password which is disabled.

Then how am i supposed to connect to Integration services on a server in this case?

There is no username and password required to connect to SSIS Server.

-Jamie

|||

i got a server A...i tried to connect without password and user it gives me error....

the sql server is using sql authetication not windows authetication.

|||SQL Server authentication is for SQL Server. The SSIS Server (service) is something else and does not support SQL Server auth. You need windows auth to connect. It actually uses DCOM.|||

so how should i proceed from here?

my server wont allow me to connect to SSIS services using object explorer.

"Access is denied" connect to server 1 on machine failed.

Do i need to create anything on the server?

sqlsql

Connect to a Novell NDS Datasoource via LDAP

Hi All,

Is there a connector in Connections Manager that can access a Novell NDS tree and help to pull data in an SSIS package?

Thanks,

BIG SYou might be able to use a script component (vb.net) as a source to get at your Novell tree.|||Can't. I run a component in CLR and it craps out.Would SSIS be any different?|||There is a Novell ODBC connector which allows you to do that. Check out Novell's website for "NDK:Novell

ODBC Driver for eDirectory".

You need to have rights to the NDS to get the information and you need to have the Novell CLIENT installed before you install the driver (which they don't tell you).|||Is that a connector that I can add to SSIS?|||

BIG S wrote:

Is that a connector that I can add to SSIS?

Once it's installed, you can use a datareader source against the ODBC driver.|||Would simply like to pull user object data back from eDirectory to MS SQL without having to implement the Novell Client, which I think is needed for the Novell ODBC driver. This can be done with a couple of lines of perl... Isn't there an easy way to make this connection and pull this data from within SQL Server? It's just an ldap query....sqlsql

Connect to a Novell NDS Datasoource via LDAP

Hi All,

Is there a connector in Connections Manager that can access a Novell NDS tree and help to pull data in an SSIS package?

Thanks,

BIG S
You might be able to use a script component (vb.net) as a source to get at your Novell tree.|||Can't. I run a component in CLR and it craps out.Would SSIS be any different?
|||There is a Novell ODBC connector which allows you to do that. Check out Novell's website for "NDK:Novell ODBC Driver for eDirectory".

You need to have rights to the NDS to get the information and you need to have the Novell CLIENT installed before you install the driver (which they don't tell you).

|||Is that a connector that I can add to SSIS?
|||

BIG S wrote:

Is that a connector that I can add to SSIS?

Once it's installed, you can use a datareader source against the ODBC driver.|||Would simply like to pull user object data back from eDirectory to MS SQL without having to implement the Novell Client, which I think is needed for the Novell ODBC driver. This can be done with a couple of lines of perl... Isn't there an easy way to make this connection and pull this data from within SQL Server? It's just an ldap query....

Connect to a DataSet Variable as a Source?

Hello. I’m new to SSIS. This forum and Kirk Haselden’s book are my teachers. I’m having a hard time grasping something basic to get started defining a set of packages to automate the ETL process, however, and perhaps I’m simply misunderstanding the best practices of SSIS.

I have source data in two different transactional databases, and use OleDb connection managers (and OleDb Source components in the Data Flow) to extract the data. I use a Script Task and several Lookup widgets in the Data Flow to transform the data, and output each to two different package-scope DataSet variables.

How do I join these two datasets in a third Data Flow task for loading into my data warehouse? It seems I can iterate through them in the Control Flow, but I can’t write a query against them in the Data Flow, since there is no connection manager that allows me to “connect” to a package-level variable. Should I instead be storing my extracted, transformed data in temporary database tables, and then joining these to do the final load?

Any advice greatly appreciated. Thanks in advance.

MilwaukeeCharlie wrote:

Hello. I’m new to SSIS. This forum and Kirk Haselden’s book are my teachers. I’m having a hard time grasping something basic to get started defining a set of packages to automate the ETL process, however, and perhaps I’m simply misunderstanding the best practices of SSIS.

I have source data in two different transactional databases, and use OleDb connection managers (and OleDb Source components in the Data Flow) to extract the data. I use a Script Task and several Lookup widgets in the Data Flow to transform the data, and output each to two different package-scope DataSet variables.

How do I join these two datasets in a third Data Flow task for loading into my data warehouse? It seems I can iterate through them in the Control Flow, but I can’t write a query against them in the Data Flow, since there is no connection manager that allows me to “connect” to a package-level variable. Should I instead be storing my extracted, transformed data in temporary database tables, and then joining these to do the final load?

Any advice greatly appreciated. Thanks in advance.

Charlie,

You're absolutely correct, there is no source adapter for these and with good reason too.

There is a workaround though:

Recordsets instead of raw files

(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/SSIS_3A00_-Recordsets-instead-of-raw-files.aspx)

Another option (and the one I would use for performance reasons as explained here: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-Comparing-performance-of-a-raw-file-against-a-recordset-destination.aspx) is to use raw files

By the way, the correct nomenclature is "components", not "widgets"

Good luck!

-Jamie

connect dbase II in ssis

I am having trouble connecting dbf of version dbase II. I can connect dbase III and dbase IV in ssis package,but can't connect dbase II.hope your help!who can help me|||

MS does not support DBBase II with their solution (JET). See the supported versions listed here-

How to use dBASE data with Access and Jet
(http://support.microsoft.com/kb/230125/en-us)

You will need to find a third-party driver, for ODBD, OLE-DB or ADO.Net. Saying that, it is rather old now, and I have never seen one. If you can understand the file format, then you could write your own source and/or connection manager.

|||

liguancong wrote:

I am having trouble connecting dbf of version dbase II. I can connect dbase III and dbase IV in ssis package,but can't connect dbase II.hope your help!

have you tried saving the dbf as a version III or IV file?

Confusing layout in SSIS with regard to "Execute SQL Task".

I hope someone can help.

I'm trying to read rows from a SQL Server Table and for each row use a few columns as parameters into a query to be run against oracle which will delete oracle rows.

I add OLDEB connections for Oracle and SQL and then I try to add a "Execute SQL Task". I've also tried a "OLE Command" but I can't get the mapping of the columns to the parameters to work.

There is lots of articles on the web that talk in general around parameterized queries but no clear examples.

I also find the difference between the Control Flow and Data Flow tabs confusing as its not intuitive where to place things. It also appears to force me to re-define things that it should already know (this is no doubt because I'm interpreting what I've done / acheived wrongly).

I have my source and destination on the "Data Flow" tab along with a "Execute SQL Task" object in the middle.

I'm setting its "connection manager" the Oracle (i.e. the destination where I want the deletes to be executed). I don't follow why this also has a "connection property, surely this it set when I drag the output of the SQL Server OLEDB Source to the input of the "Execute SQL Task".

Perhaps I'm expected too much from the wizards / dialogs and I have to create "variables" and "parameters" myself?

Any help or suggestions would be very much appreciated.

Thanks in advance

Craig

Scotland

Craig,

In general, you can think in the control flow as the one responsible for the workflow of the package; it could be also use to perform batch operations against a table. In you case, for example you could issue a delete statement using an execute sql task against the Oracle table to delete all rows at once. In order to do so, you would need to have a staging table that holds the rows that need to be delete.

The DataFlow; is deemed to move data and perform operations in a row-by-row basis. You could solve your problem by using an OLE DB command transformation to perform the delete. The drawback with this approach, it is that the delete statement would be performed for every row in the dataflow pipeline; so the performance is affected considerably.

If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction.

In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement.

I hope this helps you

|||

Thanks,

Some comments.

"If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction."

One table exists in SQL Server, then other in Oracle, does you comment still apply?

"In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement."

MUST I do this. This does not appear to me to be in the nature of a parameterized query? Would this method still use "Prepared Statements" ?

Lastly, I'm still stuck in that my real issue is on how to map input data (from SQL) to parameters (to Oracle), I think if I followed your example, I'd still have that same problem only now I would be trying to map to a variable that was my entire query instead of just a parameter?

Thanks for the help so far and best regards

Craig

|||

Can you create and populate a staging table in the Oracle side? If so, you could load all the data in the SQL server table into a staging table in the Oracle side and then use an execute sql task to perform a 1 time update. This is the way I do this kind of things because it's more efficient performance wise.

If that is not possible; I guess you have to stick with the data flow/OLE DB command approach. But I cannot help with that as I don't have an Oracle instance to test how the parameters get mapped.

Confirmation on Script Task Behavior

Hello,

I am looking for some confirmation on a behavior of the SSIS Script Task. I have a custom script task that takes an input file, and archives it after it has been processed into the database.

When I run this package in the Visual Studio GUI, if the destination drive is full, it throws an exception telling me that there is not enough disk space. So, my questions are:

1) If this happens when the package is running through the command line, would this exception still be thrown? (I am thinking it will be)

2) Also, Do I need to explicitly fail the script task in the event handler, in order to ensure this .Net exception being thrown will cause the component to fail. (I am fairly certain I do, since this is what I had to do inside of the Visual Studio GUI, but does anyone know if this same behavior would occur when running from the command line?)

Thanks,

Chris

If you don't catch the exception, it should cause your script task to fail. Otherwise, you would need to explicitly fail it by setting Dts.TaskResult=Dts.Results.Failure. The exception-related behavior will be the same between the GUI and the command-line.

Configuring SSIS package to read a value before executin

Hi,

Is it possible in any ways to Configure a SSIS package in such a way that based on the User Input the package runs. For e.g if there is a table which has say 10 distinct groups. Normal SSIS package would ideally pick all the data from the source to the Destination

I want to know how to configure in Such a way that I should be able to say Group X as the input and data related to GroupX alone should be copied.

Select * from SomeTable where GroupName = @.CongigVar.

how to achieve something like this?

thanks

Hello,

What kind of input are you intrested in getting?

If you have the group name that you want to get information from in a table, you can, in control flow, make a query to a table, and put it on a variable, then on the data flow you can use the variable as a parameter.

To use it on a parameter your query should be

"Select * from SomeTable where GroupName = ?"

Then in the parameter button on the left you choose the variable that will pass tha value to the question mark.

Is that good for you?

Regards,

|||

Hello,

I think that might just be what I need, but when I tried creating one, its failing with the error, 'Parameter Name Unrecognized'. I think am doing it wrongly. Can you post some links that explains how to put a value from a table onto a variable, and then use it as i/p to a Data flow?

thanks

|||

Nfrf wrote:

Hello,

What kind of input are you intrested in getting?

If you have the group name that you want to get information from in a table, you can, in control flow, make a query to a table, and put it on a variable, then on the data flow you can use the variable as a parameter.

To use it on a parameter your query should be

"Select * from SomeTable where GroupName = ?"

Then in the parameter button on the left you choose the variable that will pass tha value to the question mark.

Is that good for you?

Regards,

There's no need to store values in a table. You can parameterize your package by passing it values when you call it. Explore the /SET option of dtexec. All the information you require is in Books Online.

-Jamie

|||

Yes it is true. I just gave him a way.

The thing that i thnik he wanted to know was the parameter on the query.

Even so thanks

Configuring SSIS in an Active\Active Cluster

OK so I am aware that the SSIS service can be clustered relatively easily but my problem is a bit more complex I believe.

I have an Active\Active\Active 3 node cluster running Windows 2003 sp1 all running SSIS packages. What I need is a solution to having an instance fail to another node and still be able to run scheduled SSIS packages through the SQL Agent.

The solution that I have come up with is to have a single network share for all three instances of SSIS running locally on the individual nodes. I have removed the ability for SSIS to look in MSDB by altering the config file and reference only the Packages share on the network for accessability. They all share the same config file on the share as well. The share structure looks something like this simplified example.

\\server1\SSIS\Configuration

\\server1\SSIS\Packages

\\server1\SSIS\Packages\Instance1\

\\server1\SSIS\Packages\Instance2\

\\server1\SSIS\Packages\Instance3\

Once I change the registry to point to the config file all three instances can access the Instance1, 2 or 3 directories.

This works ok for the jobs, and I can access the stored packages under the file system folder only if I'm remotely connected to that particular physical server hosting the service or on the console. The problem I am having though is when users or I attempt to access the SSIS Engine through SSMS from our locale machines. It seems as though SSMS is having trouble with the passthrough of the directory information. On the share I obviously have permissiosn to access the directory structure (full control to be exact) because I can get to it when I access it from SSMS on the server.

This is the error I get...

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

The storage location for the folder "File System\" cannot be accessed. (MsDtsSrvr)

DETAILS:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.GetChildren()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren()

===================================

The storage location for the folder "File System\" cannot be accessed. (MsDtsSrvr)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String sPackageFolder, String sServerName)
at Microsoft.SqlServer.Dts.SmoEnum.PackageEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)

Any work around or suggestion would be appreciated.

Thanks,

Zach

Anyone have any thoughts on how to make this work?

Thanks for the look,

Zach

Configuring ole db provider for Indexing Service

Hi:

Am trying to access a Indexing Services catalog from SSIS. Problem is configuring the Data Connection.

I chose New Connection, Provider=Microsoft OleDB provider for Indexing Services.

Then I am not quite sure what to fill in for:

A. Server or File Name (I tried the IP address, the machine name, catalog name)

B. Location(tried catalog Name, ip address, machine name)

The Log On to Server frame is disabled.

Test Connection succeeds, but trying to use a Table or a Query does not work out.

How do I configure the Connection?

TIA

Kar

I thought this would be simple enough, but I cant get it to work. Cant find much help on MSDN/BOL either.

Hope somebody here can help me!

TIA

Kar

|||

The following may be a good starting point:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefindservprovspec.asp

In general, when searching for OLE DB related information, you can usually also search in docs for technology that uses OLE DB, such as ADO. Things like connection string info and command syntax are usually just pass-through from those technologies to OLE DB, so their documentation will most likely apply.

|||

Thanks,

I managed to get it work, but the nomenclature(OLE DB versus SSIS Connection Manager Editor) is ambiguous.

The Provider part of the string is fine, but the other property names do not match.

I suppose Server or file Name=Data Source=catalog name, and

Location = Locale ID or probably it is the Server name if you are connecting to a remote server?

Best regards

Kar

Configuring ole db provider for Indexing Service

Hi:

Am trying to access a Indexing Services catalog from SSIS. Problem is configuring the Data Connection.

I chose New Connection, Provider=Microsoft OleDB provider for Indexing Services.

Then I am not quite sure what to fill in for:

A. Server or File Name (I tried the IP address, the machine name, catalog name)

B. Location(tried catalog Name, ip address, machine name)

The Log On to Server frame is disabled.

Test Connection succeeds, but trying to use a Table or a Query does not work out.

How do I configure the Connection?

TIA

Kar

I thought this would be simple enough, but I cant get it to work. Cant find much help on MSDN/BOL either.

Hope somebody here can help me!

TIA

Kar

|||

The following may be a good starting point:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefindservprovspec.asp

In general, when searching for OLE DB related information, you can usually also search in docs for technology that uses OLE DB, such as ADO. Things like connection string info and command syntax are usually just pass-through from those technologies to OLE DB, so their documentation will most likely apply.

|||

Thanks,

I managed to get it work, but the nomenclature(OLE DB versus SSIS Connection Manager Editor) is ambiguous.

The Provider part of the string is fine, but the other property names do not match.

I suppose Server or file Name=Data Source=catalog name, and

Location = Locale ID or probably it is the Server name if you are connecting to a remote server?

Best regards

Kar

Configuring Connection Manager for 6.5 database

Greetings once again SSIS friends,

I am trying to configure a connection manager for a database that is still using SQL Server 6.5 But it is not working properly. I put the server name, user name and pass word, I select the Native OLE DB\Microsoft OLE DB Provider for SQL Server as my provider.

When I click Test Connection it says the connection is fine but then the drop down list for the list of databse names hows nothing!

Am I using the wrong provider?! I'm confused!

You have to use datareader source component as you need ODBC connection. At least that was the only way I could get it to work; as far as I remember.|||

Hi mate,

I have tried using the DataReader Source but I can't seem to be able to configure it correctly. When I try and refresh the component I get the following error :

Cannot acquire a managed connection from the run-time connection manager.

Please advise.

Thanks.

p.s.

I did add a new ODBC Data source for my SQL Server 6.5 database but I still don't know how to configure the component.

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

Is this how you are doing it?

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Yes.. I had already done that

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

You are right. That's what I had wrong. I was using the naitive ODBC data provider which apparently only works if I am using a SQL Query Task but not when creating a connection manager. I did just that and now my data source works just fine.

Thanks for your help mate. Much appreciated!

Configuring Connection Manager for 6.5 database

Greetings once again SSIS friends,

I am trying to configure a connection manager for a database that is still using SQL Server 6.5 But it is not working properly. I put the server name, user name and pass word, I select the Native OLE DB\Microsoft OLE DB Provider for SQL Server as my provider.

When I click Test Connection it says the connection is fine but then the drop down list for the list of databse names hows nothing!

Am I using the wrong provider?! I'm confused!

You have to use datareader source component as you need ODBC connection. At least that was the only way I could get it to work; as far as I remember.|||

Hi mate,

I have tried using the DataReader Source but I can't seem to be able to configure it correctly. When I try and refresh the component I get the following error :

Cannot acquire a managed connection from the run-time connection manager.

Please advise.

Thanks.

p.s.

I did add a new ODBC Data source for my SQL Server 6.5 database but I still don't know how to configure the component.

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

Is this how you are doing it?

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Yes.. I had already done that

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

You are right. That's what I had wrong. I was using the naitive ODBC data provider which apparently only works if I am using a SQL Query Task but not when creating a connection manager. I did just that and now my data source works just fine.

Thanks for your help mate. Much appreciated!

Configure SSIS package

I would like to develop a SSIS package where in there are 'X' number of input data sources like flat files, MS Access mdb files etc. The details about the number of input data sources, type of each data sources, the physical path of the data file, name os database server, username for the database server, password for the database server etc has to come from the configuration file.

Can anyone help on this ?

While you can use configurations to set things like database connections, user names, etc, SSIS packages can't change the metadata used in a dataflow at runtime. They aren't self-modifying. If you are trying to build a "generic" package that can handle any data dynamically, you will need to build an application that creates the package on the fly. SSIS has a good API, so this is certainly in the realm of possiblity, but it is still a lot of work.