MS SQL Server :: Cannot Set Connection String For Bulk Insert Source Connection

Dynamic Connection String In Excel Source
Hi all,
I am able to set dynamic source for the text file(flat file) but i want to set the connection string (file name) to excel source dynamicaly. I have tryied lots of time by taking a variable in foreachloop container . Variable is itself able to pick the file name dynamicaly but whern i am tying to set connectionstring to excel source it gives error.

Steps that i have done: -

1) Drag foreachloop container
2) set directory,FileNameRetrieval,FileSpec
3) Made VariableMapping

4) Now drag a dataflow task in the foreachloop container
5) select excel source
6) When i am selecting varaible as connectin string from properties of excel connectin manager, i am getting this error : -

TITLE: Microsoft Visual Studio
------------------------------

Error at Package3 [Connection manager "Excel Connection Manager 2"]: An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager 2" failed with error code 0xC0202009.

------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------

Please help me, whats the problem? can i set connectionstring via variable to excel source

View Replies !

How To Parameterize XML Data Source Connection String?
I need to create a report that lists other reports in the SSRS database. So I created an XML Data Source. But no matter what, I can't seem to parameterize this connection string so that it'll always point to whichever report server the report is deployed to. I know that I can change this data source once I've done deployment, however I don't want to have to change this whenever I change from SIT to UAT to Production. Is there a way to achieve this?

I tried:

"http://" & Globals!ReportServerUrl & "/ReportService2005.asmx"
="http://" & Globals!ReportServerUrl & "/ReportService2005.asmx"
http://Globals!ReportServerUrl/ReportService2005.asmx
=http://Globals!ReportServerUrl/ReportService2005.asmx
Globals!ReportServerUrl & "/ReportService2005.asmx"
=Globals!ReportServerUrl & "/ReportService2005.asmx"

All to no avail

Thanks,
Ray

View Replies !

Get Flat File Source Connection String At Run Time
I have a simple package, which reads a flat file source, does some transformation, and outputs to a flat file destination. The package runs as a SQL Agent job, so I have the flat file source and OLEDB connection ticked and configured on the data sources tab.

What I would like to do is get hold of the flat file source connection string property from inside the package at run time, and use it to set the flat file destination connection string using property expressions.

The easy option is to set the destination in the agent job, but I'd like to add a date/time stamp to the destination filename.

Is this possible?

Thanks,

Greg.

View Replies !

The Command Line Parameters Are Invalid For Source Connection String When Running As A SQL Job

Hi All,

I created a SSIS package to import an excel spreadsheet into my data warehouse.
When I run the package it runs fine. When I created a SQL Job to run the package I get the following error:

Option "Source=D:HelpDeskImportBook2.xls;Extended" is not valid. The command line parameters are invalid.

Now if I look at my source connection string in the job it looks like the following:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:HelpDeskImportBook2.xls;Extended Properties=HDR=YES;EXCEL 8.0;HDR=YES";

Does anyone know why it appears that my connection string looks like it is being cut off and does anyone know how I can correct the problem?

Thanks,

Scott

View Replies !

Rounding Error: Between Flat File Connection Manager Source && OLE DB Connection Destination (SQL Server 2005)
I have a Rounding error: Between flat file connection manager Source & OLE DB Connection Destination (SQL Server 2005) in my Dataflow.

File looks like this lets call column names Col A,B,C,D

70410000        RD1      1223631.92     196042.42
70329000        ICD       11025.84          3353.88
71167300        COL      104270.59       24676.96

flat file connection manager settings: first row Column names then Advanced tab Col A float , Col B float , Col C  string ,Col D float ,

OLE DB Connection Destination (SQL Server 2005)

CREATE TABLE [dbo].[PT_CUST_ABR](

[PARTY_NO] [float] NULL,

[PARTY_NAME] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TELECOMABR] [float] NULL,

[GENIABR] [float] NULL,

Problem: ColA (Source) Rounding error to PARTY_NO (Destination)
I have a field of text of in a flat file that the flat file connection manager Source picks up correctly €œ70000893€?
However when it gets the OLE DB Connection Destination the data has changed to 70000896. That€™s before its even Written to the database.
The only clue that something is wrong in the middle is the great Data viewer shows the number as 7.000009E+07
Other clues looking at the data it appears there is a rounding error on only the number that dont end in 00
ColA (Source) PARTY_NO (Destination)
71167300       71167296
70329000       70329000
70410000       70410000
Any ideas people?
Thanks in advance
Dave

View Replies !

The Connection Name 'LocalSqlServer' Was Not Found In The Applications Configuration Or The Connection String Is Empty.
Hi,
I'm having a BIG problem, this is my 3rd day looking for answer !I'm trying to create a custom membership provider with MS SQL database 2005 but not the default ASPNETDB. I've changed the web.config to be as following:<connectionStrings><add name="sqlConn" connectionString="Data Source=.;Integrated Security=True;Initial Catalog=ASPNETDB;"providerName="System.Data.SqlClient" /></connectionStrings> <system.web><trace enabled="true" /><roleManager enabled="true" /><authentication mode="Forms" />
<membership defaultProvider="MySqlProvider"><providers><remove name="AspNetSqlProvider"/><add name="MySqlProvider" connectionStringName="SqlConn" type="System.Web.Security.SqlMembershipProvider" applicationName="/" /></providers></membership>
But when I try to login to the site using the login control the following error occurs:
Server Error in '/etest' Application.

Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty.Source Error:

Line 149: <roleManager>
Line 150: <providers>
Line 151: <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
Line 152: <add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
Line 153: </providers>Source File: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Configmachine.config Line: 151

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.213
It is clear that this is a prblem with my Machine.Config file - since I've "worked!" on this file for a while. But when I've checked the Machine.config file I've found the LocalSqlServer connection it is talking about! .
I'm lost and I dunno what to do, can anyone help?
Here is the mahine.config in case if you need it:<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=.;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /></connectionStrings>
<system.data><DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /><add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /><add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /></DbProviderFactories>
</system.data><system.web>
<processModel autoConfig="true" /><httpHandlers />
<membership><providers>
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" /></providers>
</membership><profile>
<providers><add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers></profile>
<roleManager><providers>
<add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /><add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers></roleManager>
</system.web>

View Replies !

The Connection Name 'name' Was Not Found In The Applications Configuration Or The Connection String Is Empty

I have seen another thread with this same problem, but the problem seemed to be a typo. I am getting an error when I try to access the web site ..

The connection name 'USASH-AS0013' was not found in the applications configuration or the connection string is empty.

However, in my web.config file I do have a connection named USASH-AS0013. Here is my web.config file (with my user/pwd hidden). Any help would be greatly appreciated.

<?xml version="1.0"?>

<configuration>
<appSettings/>
<connectionStrings>
<add name="USASH-AS0013" connectionString="Provider=SQLOLEDB;Data Source=USASH-AS0013;User ID=****;Password=*****;Initial Catalog="Smart Factory""
   providerName="System.Data.OleDb" />
</connectionStrings>
<system.web>
  
    <compilation debug="true" strict="false" explicit="true"/>
    <pages theme="MySkin" >
   <namespaces>
    <clear/>
    <add namespace="System"/>
    <add namespace="System.Collections"/>
    <add namespace="System.Collections.Specialized"/>
    <add namespace="System.Configuration"/>
    <add namespace="System.Text"/>
    <add namespace="System.Text.RegularExpressions"/>
    <add namespace="System.Web"/>
    <add namespace="System.Web.Caching"/>
    <add namespace="System.Web.SessionState"/>
    <add namespace="System.Web.Security"/>
    <add namespace="System.Web.Profile"/>
    <add namespace="System.Web.UI"/>
    <add namespace="System.Web.UI.WebControls"/>
    <add namespace="System.Web.UI.WebControls.WebParts"/>
    <add namespace="System.Web.UI.HtmlControls"/>
   </namespaces>
  </pages>
  
  <authentication mode="Windows"/>
  
</system.web>
</configuration>

View Replies !

The Connection Name 'name' Was Not Found In The Applications Configuration Or The Connection String Is Empty
I have been trying to host my website on Go Daddy for about 3 weeks and I have cleared several problems but this one remains. I can get into the ASPNETDB database for doing logins , etc but I cant access my database called "PINEmgt".
To try to understand where the problem is located,I built a very simple application without login controls and put on it one Detailform accessing a single line table "Signin". I continually get the error message I got with my real app :"The connection name 'PINEMgtConnectionString2' was not found in the applications configuration or the connection string is empty. " There is only one connection string in my app -'PINEMgtConnectionString2'. When I run the app locally on my machine it works. After I move everything to Go Daddy, I get the error message and the following dump"

Line 24: </Fields>
Line 25: </aspetailsView>
Line 26: <aspqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStringsINEMgtConnectionString2 %>"
Line 27: InsertCommand="INSERT INTO Signup(first_name, last_name, email, customer_id) VALUES (,,,)"
Line 28: SelectCommand="SELECT * FROM [Signup]"></aspqlDataSource>

This is indeed the code from my single page app.

The dump is:

[InvalidOperationException: The connection name 'PINEMgtConnectionString2' was not found in the applications configuration or the connection string is empty.]
System.Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(String connectionStringName) +3039085
ASP.default_aspx.__BuildControlSqlDataSource1() in d:hostinguck7scoutDefault.aspx:26
ASP.default_aspx.__BuildControlform1() in d:hostinguck7scoutDefault.aspx:10
ASP.default_aspx.__BuildControlTree(default_aspx __ctrl) in d:hostinguck7scoutDefault.aspx:1
ASP.default_aspx.FrameworkInitialize() in d:hostinguck7scoutDefault.aspx.vb:912306
System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +40
System.Web.UI.Page.ProcessRequest() +86
System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) +18
System.Web.UI.Page.ProcessRequest(HttpContext context) +49
ASP.default_aspx.ProcessRequest(HttpContext context) +29
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +303
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

After Googleing, asking Go Daddy for info (which they dont give) , etc for three weeks, I need serious mental help
in the form of a solution of this problem. Your help with the problem or the name of a good mental health professional in eastern N.C. (maybe both) would be appreciated.

View Replies !

OlE DB Source - Cannot Acquire Connection From Connection Manager
My source files are in C:Process folder. File names are in the format proc*.csv

I am using foreach loop container to iterate over the files in folder "C:Process"

I have a user variable "filename"

The order of my tasks

ForEach - Data Flow task - File System Task

In Data Flow task, I have ole db source which should take the dynamic filename from user variable "filename". whats happening is when i give fixed filename, without foreach loop container the package is working fine.

with dynamic filename, I am not able to run the package because of ole db source Error - cannot aquire connection from connection manager.

Thanks for u'r help.

View Replies !

Edit Connection Manager Connection String At Runtime With C#
This is the first time I have used SSIS, so please bear with the ignorance.

I have a super simple package that inserts x000's of rows into a temporary table. The data source is a file that the user will upload. I need to be able to tell the package what file to upload. I'm thinking the simplest thing would be to edit the connectionString property of the SourceConnectionFlatFile at runtime. Is this possible? What form should the file path be in (UNC, other)? And, are there any other considerations I should be aware of?

Thanks!

View Replies !

Variable Connection String For OLE DB Connection
Good Morning,

I hope that I am just missing a step in the process somewhere. I have established a string variable called DatabaseServer and another called DatabaseName. In the OLE DB connectionstring expression I have the following:

Code Snippet

"Data Source=" + @[User::DatabaseServer] + ";Initial Catalog=" + @[User::DatabaseName] + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

My understanding is that changing the variable from SERVERA to SERVERB should repoint the connection. (Test to pre-production to production in my current promotion environment.) This should also take effect in any dataflow task using the database connection above right? When I try to repoint the server variable (and only the server variable) I am either a.)throwing errors saying the original connection cannot be found in the data flow task, or b.)the job appears to be processing fine, but the repoint did not take place and data is still flowing to the original server.

Can anyone offer suggestions as to what I am missing?

The properties values in the DB connection:

ConnectionManagerType is OLEDB

DataSourceID is blank

DelayValidation is False (Setting to true changes nothing in my test)

Expression for ConnectionString is (see above)

RetainSameConnection is False

SupportsDTCTTransactions is True (and appears to be unchangeable.)

View Replies !

Connection Manager Not Showing &&"New Connection From Data Source...&&"
Hello,

I've created a SSIS Solution and have created Data Sources. I have two packages. One was created before the Data Sources, and one was created after. The package that was created after is using connections from the Data Sources. I want to change the package before the Data Soruces were created to use them, but when I right click in the Connection Managers pane "New Connection From Data Source.." is not an option.

Did I not add it to the Solution properly?

How do I get it to show?

Did I not refresh something?

Please provide the how if you figure it out.

Thanks

View Replies !

Can We Use Excel File (source) For Bulk Insert Task
Can an excel file be used as a source for a Bulk Insert Task in SSIS instead of a delimited flat file?

View Replies !

Bulk Insert In Foreach Loop Not Setting Source ConnectionString
Hello all,

I am running into an issue that seems to be collaborated here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=188886&SiteID=1.

To summarize, I have a Foreach Loop that uses a Foreach File Enumerator. The loop writes the file names to a variable which is used in the Expressions property for a flat file's ConnectionString. The flat file is used as a source destination in a Bulk Insert Task that is inside the Foreach Loop. The flat file's connection string is not picking up the file name, resulting in this error: "The specified connection "test.txt" is either not valid, or points to an invalid object. To continue, specify a valid connection."

It doesn't work for a file connection manager or a flat file connection manager.

It does work if I replace the Bulk Insert Task with a Data Flow (flat file source -> ole db destination). It also works if I set the file's connection string manually in a script task.

It looks to me as though the Bulk Insert isn't calling whatever method in the connection manager that reevaluates the property expression. Am I missing something or is this a bug? I looked at the connect site, but couldn't find this particular scenario.

Thanks for any insight you might provide!
Jessica

View Replies !

Bulk Insert Vs. Data Flow Task (different Row Results Using Flat File Source)
I'm importing a large csv file two different ways - one with Bulk Import Task and the other way with the Data Flow Task (flat file source -> OLE DB destination).

With the Bulk Import Task I'm putting all the csv rows in one column. With the Data Flow Task I'm mapping each csv value to it's own column in the SQL table.

I used two different flat file sources and got the following:

Flat file 1: Bulk Import Task = 12,649,499 rows; Data Flow Task = 4,215,817 rows
Flat file 2: Bulk Import Task = 3,403,254 rows; Data Flow Task = 1,134,359 rows

Anyone have any guess as to why this is happening?

View Replies !

Failed OLEDB Connection: Cannot Aquire Connection From Connection Manager
I have a package that uses a for loop to iterate through an unknown amount of excel files and pull their data into a table. However, there will be cases when the file is corrupted or has some sort of problem so that either the transformation will fail or the excel data source will fail with an oledb connection error.
Could anyone suggest a clean way to trap these errors? Specifically, the "Cannot Aquire Connection from Connection Manager", which is the excel connection.

Thanks,

John T

View Replies !

Bulk Update Of All Changes From DataView To DataSet (only Opening Connection Once)?
(Hope this isn't a "stupid" question, but I haven't been able to find a straight-forward answer anywhere)"
I currently have code that iterates through a dataview's records, making a change to a field in some of the records. The way I have this coded, a conection has to opened & closed for each individual record that's updated:
    dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)"    dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit)    dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int)
    Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView)         For Each myRow As DataRowView In myDataView       If myRow("FkUsersAnswerID") = myRow("AnswerID") Then         intCorrect = 1       Else         intCorrect = 0       End If       dsrcUserIae.UpdateParameters.Item("blnCorrect").DefaultValue = intCorrect       dsrcUserIae.UpdateParameters.Item("ID").DefaultValue = myRow("ID")       intUpdateResult = dsrcUserIae.Update()    Next
It seems like I should be able to do something like this (call update once), but I'm not sure how...
    dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)"    dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit)    dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int)
    Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView)            For Each myRow As DataRowView In myDataView      If myRow("FkUsersAnswerID") = myRow("AnswerID") Then        myRow("blnCorrect") = 1      Else        myRow("blnCorrect") = False      End If    Next
      intUpdateResult = dsrcUserIae.Update() 'Want all changed myRow("blnCorrect") to be updated to datasource
Can anybody explain how to do the bulk update? I've seen some info about AcceptChanges and Merge, but I'm not sure if they apply here, or if they more for Transactions.

View Replies !

Best Source Of Connection?
hi,

I am using Visual Basic 6 as front end and MS SQL Server 2000 as back end. I am developing a client/server application which will run over net work. We can use different source of connection. we can connect with sql server database using native drivers in tow ways. one is making the system dsn through control panel and then just passing the dsn name as parameter to open method. and other is to make a connection string (including server name, catalog name, database name, user id and password) and passing connection string as parameter to open method. now tell me which way is more fast & efficient. Please describe in brief.
thank you.

View Replies !

DTS Packages Source Connection
Is there any table where we have source connection and destination connection information about the DTS packages?
None of these tables in the msdb have this information.

1) sysdtscategories
2) sysdtspackages
3) sysdtspackagelog
4) sysdtssteplog
5) sysdtstasklog
6) sysdtspackages90
7) sysdtspackagefolders90
8) sysdtslog90

Regards,
Ashish Johri

View Replies !

How Do You Use An Identity Column When Doing A Bulk Insert Using The Bulk Insert Task Editor

Hello,

I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.

Thanks.

View Replies !

Seting Connection Source In Dts At Run Time
every time we develop new dts and move it to production we have to MANUALY
modify data source.

I can submit data source as input parameter to dts and using global variable assign it connection data source.

Could it be done at run time ?

Example
if dts start on dev1 server all
connections points on dev server

if dts starts on prod server all
connection point on prod

Thank you

Alex

View Replies !

Data Source And Connection Manager
Hi all,

I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.

I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....

Thanks for your response

View Replies !

Connection Manager Or Data Source

For seting up the connection should we use Connection Manager or New Connection From Data Source?

The advantage of using Data Source is that we have the connection avalilable at project level but I've studied it somewhere that Data Source is used for SSAS not SSIS.

But I don't have enough explanation to give it to my team, that why I am not using Data Source.

We are working on VSS, do we have to reset the connection managers if want to test our packages on some other machine(else than the one on which it was developed)?

View Replies !

Oracle 9i OLE DB Data Source Connection
help - I€™ve been stuck on this for days.

My software vendor limits the sessions to one (1) per log-in to Oracle 9i and I€™m having tons of problems trying to extract data through an SSIS package. Everytime I run the package SSIS already logged in and the OLE DB source fails because of the sessions exceeded error.
Is there any way to limit the connection to just when I need the data to pump across on the OLE DB source?

Thank you very much for any help.

Regads,
Nestor

View Replies !

Cannot Create A Connection To Data Source !!!!!!!
I cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group! Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password. I thought that since this datasource was set to Windows Authentification that it would just pass it through?

I'm pulling my hair out at this point:

Screen Shots:

http://www.webfound.net/datasource_connection.jpg

http://www.webfound.net/datasource_connection2.jpg

http://www.webfound.net/datasource_connection3.jpg

Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run

An error has occurred during report processing.

Cannot create a connection to data source 'datasourcename'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

View Replies !

Oracle Source Connection Error

Hi,

I was trying to connect to Oracle Source in SSIS

1. Err: Test connection failed because of an error in initializing provider. Oracle Client and networking components were not found
Sol: I installeed the Oracle Client tools

2. Err: ORA -12154: TNS: Could not resolve teh connect identifier specified
Sol: Then I point out the TNS file

3. Now I coonect the Oracle db . I go to Connection Manager . Specifies the provide as: Native OLE DB Oracle Provide for OLE DB, Select my DB, provide teh user name and password. and check if the connection passed.
Then in my data Flow Task when I add this source and check the Colmns its gives me an error./ sort of warning

The component reported the following warnings:

Warning at {C947B.......} [Ole DB Source[1]]: Cannot retrieve the column code page info from the OLE DB provider. if the components supports teh "DefaultCodePage" property, the code page from that property will be used.....

Will this warning has an significance. i dont see any error in my output data. but is there something I m doing wrong.

4. When I set my ProtectionLevel under Security in package property to "Dont Save Sensitive" in order to easily configure the package I m getting this error: failed Validation. The default one was EncryptSensitiveWithUserKey then its running smoothly.
Can somebody advice me on this?

I addded the all the errors and solution for 1 and 2 becuase in that way this will be helpful in future.

View Replies !

RS: Cannot Create A Connection To Data Source
Hi!
I've created a project includen 3 reports based on following code on 3 server. The report includes 3 different datasources an 3 different report. All 3 reports are grouped on Database and summerizad zise.

SELECT

SD.name [Database],

SMF.name [File],

SMF.size [Size]

FROM

sys.master_files SMF INNER JOIN sys.databases SD

ON SD.database_id = SMF.database_id

I can run all reports in Visual Studio (preview). But I can only run one the reports after depolying the project! I get this message when I raun the other 2 reports:

An error has occurred during report processing.

Cannot create a connection to data source 'uasql02_datasource'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

I tried running the report from an server (where reportingservices is installed) finding that all the reports works as they should. I logged in another server with the same credentials. I got same message on the server as I get on my own camputer, "Cannot create a connection to data source ..."

Is there anybody out there having same problem as I, or someone who has any suggestion?

Thanks for any suggestion!

/*
Fari Sah
*/

View Replies !

Cannot Create A Connection To Data Source !!!
Hello everyone,

I posted the same issue before, but I couldn't find the solution yet. I am posting this agian cos I tried almost everything and I am not able to solve the issue.

Let me explain what is happening here:

Server A - is my development local machine

Server B - is the remote reporting server, where all reports are deployed

Server C - is where the databases are located

- After successfully deploying the project (the reports and datasources) on the remote server and try to run the report , I am getting the following error message

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

- I tried to deploy and run the same report on my local machine report server(Server A), which is still accessing the same database from Server C. This works fine.

So from this we can understand that A ==> B ==> C is not working, however A ==>C works fine(using Server A as a report server).

I almost tried all the possible solutions that are posted on the forum, such as storing the credential information on the report server (server B), created a data source, which is not a shared data source, on the report server level e.t.c. and I ended up with nuthin.

The weired thing is that there are existing reports that are already created and deployed(by somebody else) in the report server (server B) , which access the database on Server C. These existing reports run with no problem, even I can create a report which uses the existing data source and runs perfectly. However, when I create a new report which uses a different data source (from Server C), and deploy it to Server B and tried to run it, the above error message comes.

Please let me know if you have any idea.

Thx.

View Replies !

Cannot Create A Connection To Data Source
Why is it that sometimes I get this error and sometimes not after I deploy the same shared data souce to my Report Server?

Even if I try deleting the datasouce through Report Manager the redeploy it from VS 2005, it still errors out when I run my report. I am able to run my report fine in VS.

An error has occurred during report processing.

Cannot create a connection to data source 'mydatasourcenamehere'.

For more information about this error navigate to the report server on the local server machine, or enable remote error

View Replies !

Cannot Create A Connection To Data Source
In most secure environments server hoping is not allowed. In my case I have an application which on its own server. this application initiates a report, which is located on reports server, reports server iniates a call to olap server, which is also separate server. so now we have project server 2003, sql server reports 2005 and analysis services 2000.
when I try to run a report from application server I get an error message: '€˜cannot create a connection to data source€™. Naturally I checked data source it work fine locally on the reports server. For the data source I used windows nt integrated security. In my case windows nt security did not work and I did not have a resource to troubleshoot windows security. After I have used credentials stored securely on the reports server with user who has access to OLAP cubes, my reports were able to execute successfully. i wish an error from the reports server would be more descriptive. hope this helps anyone who has issues connecting to olap server using report services data source.

View Replies !

Cannot Create A Connection To Data Source

Hi,

When I view a report, I get the error message:

An error has occureed during report processing.

Cannot create a connection to data source "data source name"

Cannot open database "database name" requested by the login, The login failed, Login failed for user "username".

The data source will connnect to my OLAP server and I believe I have right to access the OLAP server.

Why the error occurred? Somebody can help me? Thanks very much!

View Replies !

Data Source Connection Error
All,

Good morning.
We are currently getting an data source connection error .
We are using Windows 2003 server and our database in on a Linux box.

We tried to connect thru ODBC and the report works fine in the preview mode but when I deploy the report to the sharepoint site and I run the report from there then I get the following error.

An error has occurred during report processing.

Cannot create a connection to data source 'DataSource1'.

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
The Eventviewer shows the following error

Unable to connect publishing custom string handler for output caching. IIS Instance Id is '1', Url is 'http://n1tmp1ops:8080/reportserver/ReportExecution2005.asmx'.

I appreciate all your help in advance

Thanks and Regards
Murali

View Replies !

Cannot Create A Connection To Data Source
Hi all,

I am trying to view report that i deployed to reporting service but when i try to view it, it gives error:

An error has occurred during report processing.
Cannot create a connection to data source 'INVC'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors

the datasource is ODBC

View Replies !

SQL Data Source Not Showing Any Connection Strings
Everything is set up the way it should be, as far as we can tell.
However, when we drop in a SQL data source and try to configure, it, we
get a blank box for choosing the connection string, and it won't let us
make a new one.

Has anyone encountered this before? If so, how can we fix it?

View Replies !

Data Source Connection Not Setting Correctly
This is a DTS question, not Integration services, please advise if this is the wrong forum, I couldn't find a DTS forum.

I use a loop that sets the source connection object each time at the
beginning of each loop to a different DSN-each DSN points towards a
different server with the same database structure (I set this through
an ActiveX script task, setting the
"pkg.Connections("My_Source_Conn_Name").DataSource" property). I then
run a series of datapump tasks that should use the new DSN each time.
This is not a problem with the loop, it is clearly looping through each
set of steps each time.

I realized that the second time I set it, it doesn't take, i.e. the
datapump tasks all run off of the first DSN setting. Each time through
the loop the queries run from the first server as if the source
connection object had not been changed. If I use a:

msgbox "DSN is now: " &
pkg.Connections("My_Source_Conn_Name").DataSource

before and after when I set it in the ActiveX task, it tells me I am
doing it correctly-it changes each time.

If I look at the Source Connection object when the package is done, it
is set correctly to the last server's DSN. If I do an "execute step" at
that point it works and gets the data from the last server.

Every step in the package is set to run based on the success of the
previous step, but it is like the source connection object is not
getting set until the end of the package, at least as far as the
DataPump tasks are concerned. How can I ensure it gets set before each
next set of DataPump tasks?

This is urgent for me, so I greatly appreciate any help. Let me know if
you need more info.

Thanks,
Kayda

View Replies !

Error In Excel Source With Dynamic Connection

Hi SSIsians,

    I have a package with Excel Destination with dynamic connection.
    I did ExcelFilePath = [@user::VarSourceFolder]+[@user::VarSourceFileName]
    then i changed the Delayvalidation = True.

    When i try to run the package in BIDS it gives the error.
    ERROR:
    [Excel Source [30501]] Error: An OLE DB error has occurred. Error code: 0x80040E37.
    [Excel Source [30501]] Error: Opening a rowset for "DailySheet" failed. Check that the object exists in the    database.

    It saying there is no sheet in the name of "DailySheet" but when i removed the expression in connection manager property it is working fine.

Please let me know what is the problem OR how to configure the dynamic connection in ExcelSource.

Thanks
Thiru
SE - SSIS
Chennai

View Replies !

DataReader Source And ODBC Connection To PostgresSQL
Hi,

I am trying to use the DataReader Source to import a table from a PostgresSQL database into a new table in SQL 2005 database. It works for all tables except one, which has over 80,000 records with long text columns. When I limit the import to fraction of records (3,000 to 4,000 records) it works fine but when I try to get all it generates the following errors:

Source: DataReader using ADO.NET and ODBC driver to access PostgresSQL table
Destination: OLE DB Destination - new table in SQL 2005
(BTW - successful import with DTS packagein SQL 2000)

---Errors
Error: 0x80070050 at Import File, DTS.Pipeline: The file exists.

Error: 0xC0048019 at Import File, DTS.Pipeline: The buffer manager could not get a temporary file name. The call to GetTempFileName failed.

Error: 0xC0048013 at Import File, DTS.Pipeline: The buffer manager could not create a temporary file on the path "C:Documents and SettingsmichaelshLocal SettingsTemp". The path will not be considered for temporary storage again.

Error: 0xC0047070 at Import File, DTS.Pipeline: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.

Error: 0xC0209029 at Import File, DataReader Source - Articles [1]: The "component "DataReader Source - Articles" (1)" failed because error code 0x80004005 occurred, and the error row disposition on "output column "probsumm" (1639)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Import File, DataReader Source - Articles [1]: The component "DataReader Source - Articles" (1) was unable to process the data.

Error: 0xC0047038 at Import File, DTS.Pipeline: The PrimeOutput method on component "DataReader Source - Articles" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
---End

Any idea why it can't create a temp file or why it complains about the "The File exists", which file, where, etc. Any help or alternative suggestions are greatly appreciated. What I am missing or doing wrong here?

Best,

Michael Sh

View Replies !

Cannot Set Connection String For Bulk Insert Source Connection

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