.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Custom Connection Manager -- how to use in SSIS 2008 R2 without a DataReader source?

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server

We have a custom connection manager in an SSIS 2005 package. We're able to click on "New Connection" in the Connection Manager section of the data flow task, and see the connection manager in the list. But how do we leverage this new custom connection manager as a source? The DataReader source doesn't seem to be an option anymore. We can't seem to create a new data flow task using this new connection manager as a source.

Ironically, the package runs as is after conversion from 2005 to 2008. It's just that we can't change it or add new data flow tasks using this new connection manager as a source.

Steve Coleman

View Complete Post

More Related Resource Links

SSIS 2008 - Excel Source using SQL Connection cancels as soon as package runs.

Let me preface this by saying I'm brand-new to SSIS development. I'm using 64-bit SSIS 2008 and I'm trying to read an Excel spreadsheet using OLE DB. I've already run into and corrected the Run64BitRuntime problem.  Now when I invoke the Package it cancels immediately with the following output: SSIS package "Lesson1X.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. SSIS package "Lesson1X.dtsx" finished: Canceled. The program '[5400] Lesson1X.dtsx: DTS' has exited with code 0 (0x0). When I edit the Excel Source component itself there are no errors and I am able to Preview the results successfully.  If I use Table or View to access the file, the package runs successfully. Any suggestions will be greatly appreciated. Thanks

SSIS --> Code: 0xC0202070 Source: EnrollmentExport Connection manager "V_FlatFileConn" Descrip


I have a SSIS package which is run by Active bactch job daily morning and generate the three text file at different network location. But the sometime the package is failing during the execution with following error message.

" <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-alt:"Calisto MT"; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-alt:"Times New Roman"; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; m

Connect to an existing connection manager from within a custom transformation?

I'm developing a custom data flow transformation and have hit a roadblock.  I need to create an ADO connection to one of the connection managers that's defined in the package already.  I can either hardcode the name of the connection manager, or define it as a custom property....the problem I'm having is accessing it at all.  What I've found is that ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager only has the connection managers defined by the component itself, not already existing in the package.  How do I create a connection to a connection manager that's defined at the package level at design time? Thanks

SSRS 2008 Custom Authentication works when using ReportServer url but not when report manager url is

Hi, I am using SSRS 2008 with Custom security extension for authentication. The extension is developed in c#. I have placed the dll under report server bin and report manager bin directory and the logon,login pages under appropriate directory. My reports manager url is http://localhost:port/Reports and my reports server url is http://localhost:port/ReportServer. When I try to login through reports manager url, I am getting 'login failed' error message from the dll. But when I try to login using reports server url, it works. I have also set 'FullTrust' named permission set in RSMgrPolicy.config . <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="FullTrust" Description="This code group grants MyComputer code Execution permission. "> < IMembershipCondition class = " ZoneMembershipCondition " version = " 1 " Zone = " MyComputer " /> Any suggestions please!   Thanks!

FTP connection manager Editor in ssis

i am using ftp connection manager on my local system.i passed user name as localhost,port number as 21,user name as anonymous,password blank mean nothing.when i test the connection,it give me error as follow,beside i have also provide values for credentials. TITLE: Microsoft Visual Studio ------------------------------ Connection can not be established. Server name, port number, or credentials may be invalid. ------------------------------

SSIS Excel Connection Manager Data Type Conversion Issues with SS Agent Job

Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

SQL 2008 R2 Custom Data Connection Issue with Data Flow - Data Sources


I have a custom data connection that we used in SQL 2005 to connect to a non-standard source to pull data.  We are in the process of upgrading to SQL 2008 R2 and I am running into an issue that hopefully someone can help me with. 

I have upgraded my custom connection code for SQL 2008 R2.  I can set up a connection using the custom code inside of an SSIS package.  However, when I go to a data flow task and try to select a connection for a data source object, OLEDB or ADO.Net data sources, my connection is not available.  If I select new on the connection option, my connection type is not available.  To get around this in SQL 2005 I used the data reader source.  The data reader source is not available in SQL 2008 R2.  So I am stuck. 

My custom connection is implemented as an IDBConnection.

SQL Server Integration Services 2005 x64 (DTEXEC.EXE) cannot recognise a custom "Connection Manager"


I have built a custom SSIS component for connecting to SQL Reporting Services in SSIS - including a custom data source and custom connection.

It works fine using 32bit DTEXEC and dtsdebughost, but I cannot get it to load in a 64bit environment.

Initialy I was compiling the dlls as MSIL (target=any), then I recompiled specifically targetting x64 (copying the dlls to the 64bit location and deploying to the GAC), just to see whether the problem was because of using an MSIL binary.

The 32bit version works as expected - are there any additional registration steps required for a 64bit Connection manager beyond copying to the /connections directory and installing  into the GAC?

The output from DTSEXEC:

c:\Program Files\Microsoft SQL Server\90\DTS\Binn>DTExec.exe /f "e:\temp\SSISCus
tomADComponent\TestSSISSolution\Integration Services Project1\Package.dtsx"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  12:07:30 p.m.
Error: 2010-09-22 12:07:43.89
   Code: 0xC0014005
   Description: The connection type "SSRS" specified for connection manager "SQL
 Server Reporting Services" is not recognized as a valid

trying to use custom assembly with script task in SSIS 2008 - can't find correct version of GACUtil


I am trying to use a custom assembly (compiled with VS 2005 framework 2.0) with SSIS 2008.  I understand that I need to put this Assembly in the GAC.  I initially attempted to do this using the GACUtil, but I apparently only have version 1.1 of GACUtil intalled on my development PC.  Shouldn't version 2.0 have been installed as part of VS 2005? 

So I then tried simply copying the dll into the GAC folder using windows explorer.  Well, the dll is now visibly in the GAC folder, but I cannot see it from either VS 2005 or BIDS 2008.  Does that mean I do need to use GACUtil 2.0 or higher to install the DLL into the GAC?  If so, is there any place on the Web that I can download it?

Programmatic SSIS: reading Connection Type Specific Connection Manager properties



I'm trying to read Connection Manager properties, but am stumbling when trying to access anything  beyond the generic ConnectionManager objects. In it's stripped down format the code i'm using to read a Property is this:

string InputProperty = string.Empty;

PropertyInfo PropInfo = null;     // Uses System.Reflection to get property info

foreach (ConnectionManager v in package.Connections)


	PropInfo = typeof(ConnectionManager).GetProperty(InputProperty);



However if i'm trying to read an input property that's specific to a type of connection manager, e.g. Flat File Connection manager property HeaderRowsToSkip, the above code fails as the ConnectionManager object only serves up a few generic properties.

Now, I assume that I need to cast or similar to get the IDTSConnectionManagerFlatFile Connection type...  but at this point I get lost. Can anybody give me a helpful pointer?

Cheers, James

James Beresford @ www.bimonkey.com
SSIS / MSBI Consultant in Sydney, Aust

SSIS as data source in SSRS connection string


I have a connection string

="/f c:\test\test2\test2\package.dtsx /set \package.variables[User::varfil].Value;" & Parameters!varempid.Value

but its not returning any results. I need a valid connection string.

SSIS -2008 R2 - ODBC Connection error - Password does not save


Please know that I am new to SSIS 2008 and XML. Have a package I am trying to deploy. Am on a HV 64 Bit environment SQL Server 2K8 R2. Have an ODBC connection to our HBOC system as a data source. As always package runs in test environment and not in prod.  I need a bit of insight into the what and why's of the configuation file and how to change out the XML. 

Main issue is the password not saving and how to handle the ODBC side of things. I appreciate any help in this matter as I have searched with no real understanding of XML and best practices of creating the Congif file.


SSIS Connection Manager name




Does anyone know how I can get the name of the connection in an ssis package using the package or connections object in c# code?

Can we import data from Green plum to SQL Server 2005 using OLEDB connection manager in SSIS



actually we are planning to move data from GreenPLum data base to SqlServer 2005 data base using SSIS as ETL tool. What type of connection manager can we use. Is to good to use OLEDB connection manager or ODBC connection manager.

Please give me a reply as soon as possible.




RAISERROR does not cause OLE DB Source Task to fail in SSIS 2008?



I have a stored procedure with a TRY / CATCH block.  In the catch block I capture information about the error.  And then use RAISERROR to "rethrow" the exception so that it will be available to SSIS.

I notice that there is a defect reported which is about Execute SQL Task. "Execute SQL Task not signalling failure on a Raiserror." https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276828&wa=wsignin1.0

It was fixed in SSIS 2008. But I found that RAISERROR still cannot fail a data flow source task. The stored procedure is executed in OLE DB Source task. The RAISERROR is justed omitted by OLE DB Source task and the task box turns green. But if there is any exception throw by system (eg. 1/0), it would fail the task.

Here are the two stored procedures I tried.

CREATE PROCEDURE [dbo].[GenerateErrorA]
  Select 1/0 AS A
  RAISERROR('Error Raised', 16, 1)

CREATE PROCEDURE [dbo].[GenerateErrorB]
 Select 1/0 AS A

Only stored proce

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

abstract away the source of the connection string using a class with a static property.

ASP.NET provides a configuration system we can use to keep our applications flexible at runtime. In this article we will examine some tips and best practices for using the configuration system for the best results.

The element of a web.config file is a place to store connection strings, server names, file paths, and other miscellaneous settings needed by an application to perform work. The items inside appSettings are items that need to be configurable depending upon the environment, for instance, any database connection strings will change as you move your application from a testing and staging server into production.

Let's abstract away the source of the connection string using a class with a static property.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend