Build Query Dialog For OLEDB Source Editor

Mar 23, 2006

Can i extend the "Query Builder" dialog of OLEDB Source Editor for developing custom source component?

View 1 Replies


ADVERTISEMENT

BUG In SSIS OLE DB Source Build Query

Jun 28, 2007

Can anyone help me with this ?




I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :



OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)



SSIS parses this query succesfully but when i build the query it shows query



SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)


Please note :- SYSDATE IN " "




This query returns no Result Set.

View 6 Replies View Related

Oledb Source Query Through Expression

Jan 10, 2012

I am putting the below query in a OLEDB SOURCE through a variable (it is a select statement with a where clause from one date to another).

"select TestRecordtype, request_id from department
where LOAD_TMSTP between
(select max(END_TMSTP) LOG) and
(TO_DATE("+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System:tartTime] ), 2) + "-"
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System:tartTime] ), 2) + "-"
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System:tartTime] ), 2) + " "
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System:tartTime] ), 2) + "."
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System:tartTime] ), 2) + "."
+RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System:tartTime] ), 2) +",'DD-MM-YY HH24.MI.SS')) "

View 1 Replies View Related

Parameters For OLEDB Oracle Source Query

Jul 5, 2006

Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)


I am getting the above error while opening the parameter box at OLEDB source for Oracle using SQL command option at Data Access Mode?? Can you any one please help me in this regard and trouble shoot this problem..

View 8 Replies View Related

OLEDB Source Running Full MDX Query When Validating

Feb 18, 2008

Hi,

I have an Integration Services project which creates a flat file report from Analysis Services, I'm using an OLE DB as data source and running an Openquery in the SQL statement.

the problem is that Integration services runs the query twice before getting the data into the flat file. I know this because the query runs two times in Profiler, and because the same query takes half the time when run in Management Studio.

Integration Services is running the whole query when validating. how can I disable this validation or better make it validate properly.

thanks

View 11 Replies View Related

Mapping Package Variables To A SQL Query In An OLEDB Source Component

Nov 2, 2006

Learning how to use SSIS...

I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)

I wanted to add additional clauses to the where clause.

The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.

The package variable is called [User::Date_BeginningYesterday]

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.

http://msdn2.microsoft.com/en-us/library/ms139904.aspx

The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.

...cordell...

View 4 Replies View Related

SSIS OLEDB Data Source Query Parameters Connect To Oracle

Oct 26, 2007

Hi,

I'm trying to use query parameters with an Oracle OLEDB Source in a data

flow task and I'm having problems.

I've tried formatting the query each of the following ways...

--

select

frq_code,

frq_name,

update_frq,

uptime_frq

from frequency_bcs

where update_frq > ?

and update_frq <= ?

--

Parameters cannot be extracted from the SQL command. The provider might not

help to parse parameter information from the command. In that case, use the

"SQL command from variable" access mode, in which the entire SQL command is

stored in a variable.

Additional information

---> Provider cannot derive parameter information and SetParameterInfo has

not been called. (Microsoft OLE DB Provider for Oracle).

View 7 Replies View Related

Parameterized Query For Microsoft OLEDB Provider For Oracle Using OLE DB Source In SSIS

Apr 26, 2007

Hi,



Urgent Help required..........

Can anyone explain me steps how to parameterized query to send oracle.

If you know any other control which help to do this rather than OLEDB source.

Please let me know.



THanks

View 18 Replies View Related

Error When An OLEDB Source Points To An OLEDB Destination.

Oct 10, 2006

Hi all,

I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.

But i got the below error:

Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.

Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow TaSK: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)



View 5 Replies View Related

Error Output In Oledb Command Advance Editor

Sep 27, 2007

I have a data flow and inside the data flow , i have a ole db source and ole db command task to execute an insert transaction ( SP).. i would like to save the error output if the insert didn;t happend into a error log table..
but when I darg an error output line ( red) to another ole db command to insert an error log , i can only see two columns( error code and error column) are available in OLEDB command advance editor related to errors.. this doesn;t tell you much information about the error.how can i grap the error reson(desc) as the error output and store into a erro log table? so that i can see what the problem is?

View 1 Replies View Related

OLE DB Source Editor

Dec 20, 2006

hi,

I am using SSIS to extract data from sql server and import into MDB file. In
the process, under data flow task, I have used OLE DB Source Editor as source. Here
i have choosen SQL Command as mode of data population. In the box below i
have typed the following statements.

"Exec Site_Address"

I have used many temperory tables in this procedure.
When i run this procedure in the query analyzer window i get the desired data which has to be imported to an MDB. After typing the above statements and when i
click the button preview i can see the data. But when i click the
Columns.... i dont see anything there. I am unable to see any columns there.
This is getting to my nerves because, when i use OLE DB as Destination i am
unable to map the columns and i get an error.

I dont know how to solve this problem. cannot we map columns in temp tables .... or wat is it ??

Please help me to find a solution.

I will also paste the procedure code that i have used.

Create procedure Site_Address

as

begin



create table #Data_For_Site_Address_Table

(

unitid varchar(20),

city varchar(50),

cust_num varchar(40),

zip varchar(20),

CountryID varchar(20),

CreatedBy varchar(20)

)





-- tblcrdsiteaddress

insert into #Data_For_Site_Address_Table

select distinct * from

(select

(select top 1 fsu.ser_num

from fs_unit fsu

where ca.cust_seq <> 0 and fsu.cust_num = ca.cust_num

order by ca.city desc) as UnitID,ca.city,ca.cust_num,ca.zip,

CASE

WHEN ca.country like 'Luxembourg' THEN 'LU'

WHEN ca.country like 'Deutschland' THEN 'DE'

WHEN ca.country like 'Austria' THEN 'AT'

WHEN ca.country like 'Czech Republic' THEN 'CZ'

WHEN ca.country like 'Denmark' THEN 'DK'

WHEN ca.country like 'CHINA' THEN 'CN'

WHEN ca.country like 'CROATIA' THEN 'HR'

WHEN ca.country like 'Egypt' THEN 'EG'

WHEN ca.country like 'Germany' THEN 'DE'

WHEN ca.country like 'Hungary' THEN 'HU'

WHEN ca.country like 'Jordan' THEN 'JO'

WHEN ca.country like 'Korea, Republic Of' THEN 'KR'

WHEN ca.country like 'Poland' THEN 'PL'

WHEN ca.country like 'Switzerland' THEN 'CH'

WHEN ca.country like 'United Kingdom' THEN 'GB'

ELSE '- N/A -' END AS CountryID, CA.CreatedBy

from custaddr ca

) al

where unitid is not null





Select TT.Unitid as Short_Site_Name, TT.City as Site_Name,'N.A' as Street_Po_Box,TT.Zip as Postal_Code_City, Null as State_Region,

TT.CountryID as CountryID,Null as Zone, Null as Note, TT.CreatedBy as UserID, GetDate() as Date, 'A' as [Action]

From #Data_For_Site_Address_Table TT



END



Thanks.

Rgds,
Meher Krishna.V

View 9 Replies View Related

OLE DB Source Editor And External Columns

Apr 23, 2008

I have query like below that I am using as a OLE DB source

Set NOCOUNT ON

Select *
Into #temp1
from A

Select *
Into #temp2
From B

Select * from #temp1 a
Join #temp2 b on a.episode_key = b.episode_key


I can see the preview data , but when I click columns, there are no available external columns..
Howcan I fix this issue?

View 8 Replies View Related

Can I Use * To Specify 'Output Column' For OLD DB Source Editor?

Dec 7, 2006



I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.



thanks! -ZZ

View 8 Replies View Related

XL Source Editor Excluding Numeric Records

Dec 11, 2007

Hi All,

I have a XL source file which contains the 1st column value is some of Numeric and alpha numeric (ex. 1,1A,1B,2,2A,2B),
i have fired the select statement (select * from [sch 1 a$A9155] where f1 is not null ) in XL source editor, its not showing the numeric records means 1 and 2 were excluding from the above select statement i think it was due to combining numeric and alpha numeric.

I need both records should select , please anyone can help me to sort out the issue.

Thanks in advance

Thanks,
syed

View 6 Replies View Related

Ole Db Source Editor Preview Throwing An Error

Sep 2, 2006

i have a data flow configured to use a ole db source.  the ole db source uses an ole db connection manager pointing to the adventureworks db which suceeded when i tested its connection.  the data access mode of the ole db source is "sql command".  below is the sql command text:

SELECT SpecialOfferID, Description
DiscountPct, Type, Category, StartDate,
EndDate, MinQty, MaxQty, ModifiedDate
FROM Sales.SpecialOffer
WHERE ModifiedDate >= ? AND ModifiedDate < ?

this query uses 2 paramaters, each of which is mapped to a datetime variable which falls with the range of the ModifiedDate column as follows:

Parameter0: User::ExtractStartDate

Parameter1: User::ExtractStopDate

ExtractStartDate is set to 7/1/2001 and ExtractStopDate is set to 3/31/2004.  however, i get the following error when i press the preview button in the ole db source editor: "there was an error displaying the preview. additional information: no value given for one or more required parameters (microsoft sql native client)".

as far as i can tell, i have the ole db source configured correctly.  thus, i can't figure out why this error is being generated.  has anyone else experienced this issue?  if so, were you able to resolve it? is this a bug?

thanks in advance.

 

View 5 Replies View Related

Advanced Editor For DataReader Source / SQLServer 2005

May 11, 2007

I want to import data from a remote MySql Database



So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:



Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
......
bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults)
bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing)
bei System.Data.Odbc.OdbcDataReader.Close()
bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)


the sql string is very simple, this should not be the problem:

SELECT Objektnr FROM m02a_tblObjektstamm



Thank you very much for your support

Martin

View 8 Replies View Related

Disable Automatic Renaming Of Source Tables By View Editor?

Sep 29, 2012

Is there a way to disable to automatic renaming of source tables by the View editor? (SLQ Server 2008 R2)

I have a view that is using several sub-queries (including a "rank/partition") and even though each sub-query is contained in ( ) and given an alias, the view designer automatically adds the _1, _2 to all of the tables it thinks are duplicates, which then invalidates the and explicit field calls in CASE WHEN statements ...

View 1 Replies View Related

XML Source Editor Columns Configuration Is NOT Persisting Output Name Selection

Oct 25, 2007

Greetings everyone,

I am seeing a particular problem in the XML Source Editor "Columns" configuration where it is not persisting the "Output name" selection.

Control Flow Tab:
1. I use a "Exec SQL Command" to drop, create, or alter the destination tables in the database that I want to be repository for the inbound XML data. The data types are fairly straightforward.

2. I add a singular "Data Flow"

Data Flow Tab:

1. I add a "XML Source" task, and assign a well-defined XML file. I then use the "Generate XSD" option in the "Connection manager"; and I am fairly satisfied with the generated XSD.

2. I create "OLE DB Destination"

3. I wire the "XML Source" to the "OLE DB Destination". In the "XML Source" in the "Columns".

4. I go to the dropdown list of "Output name" and see the list ordered with the various complex-types that I want to map and transfer to a target table.

For the sake of this report, I select the 5th one down on the list (for which I already have a target table) - let's call this "Mesh"

5. In the "Input Output" dialog, I select the "output" to be the desired 5th item, "Mesh"

6. I check all my mappings so that they map one-to-one ... XML name entries match SQL table destination mapping entries; correct types; correct size

7. Check the metadata and it all looks good.

8. When I hit "Debug" to test the package the failure occurs at the "XML Source". The error report comes back saying that it failed because "field xxx in Contributor was truncated". However, "Contributor" corresponds to the 1st name in the dropdown list presented in "Columns" "Output name:".

If I select return to Step 4, when I open up "Columns" I see that my previous selection of the 5th item on the list named "Mesh" was not persisted, but invariably and no matter how often I select item #5 "Mesh" and save to ensure that selection sticks, it is not persisted.

I hand-edited the .dtsx file and only then was I able to make this stick. However, if I ever re-save the package this non-persistency pops up again.

Am I doing something wrong here or is this a known defect? As I have several dozen XSD mappings that I want to transfer to tables, hand-editing is not something I relish.

I look forward to your reply.
RudyC

View 1 Replies View Related

Integration Services :: OLE DB Source Editor Doesn't See Excel Named Ranges

Sep 8, 2009

I have a Connection manager to an Excel workbook. I've set it up as Microsoft.Jet.OLEDB.4.0  Excel 8.0;HDR=NoWhen I create the DataFlow OLE DB source, the Name of Table or View dropdown shows only 3 items:

Resources$   Resources$_  and Volumes$_

My workbook has 2 tabs (Resources and Volumes; Volumes is hidden), two named ranges of the same name (Resources and Volumes)  and a bunch of other named ranges.

Why does the drop down only show these three things, and only the first one (the first tab Resources$) actually works.

The other two give a nasty message (0xC02020E8 Microsoft.SqlServer.DTSPipelineWrap)

Why can't I see my named ranges?New user to 2005

View 7 Replies View Related

Error Attempting To PREVIEW In OLE DB Source Editor - Could Not Load File Or Assembly

Feb 28, 2007

Please... any ideas? Is this a footprint config issue?



TITLE: Microsoft Visual Studio

There was an error displaying the preview.




ADDITIONAL INFORMATION:


Could not load file or assembly
'Microsoft.SqlServer.SQLTaskConnectionsWrap, Version=9.3.242.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its
dependencies. The system cannot find the file specified.
(Microsoft.DataTransformationServices.Design)




BUTTONS:


OK




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





There was an error displaying the preview. (Microsoft Visual Studio)





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





Could not load file or assembly
'Microsoft.SqlServer.SQLTaskConnectionsWrap, Version=9.3.242.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its
dependencies. The system cannot find the file specified.
(Microsoft.DataTransformationServices.Design)





------------------------------


Program Location:





at
Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String
sqlStatement, ConnectionManager connectionManager, Control
parentWindow, IServiceProvider serviceProvider,
IDTSExternalMetadataColumnCollection90 externalColumns)

at
Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object
sender, EventArgs e)

View 1 Replies View Related

Integration Services :: Can't Preview Data In Excel Source Editor Unless Sheet Is Open?

Nov 10, 2010

I have a package which has an Excel source with the 'Data access mode' set to SQL command and then a sql select statement.  When I try and hit the 'Preview...' button below the 'SQL command text' window I get the following error:

 "Error at Standard Data Flow Tasks [source tasks name]: No column information was returned by the SQL command"
 
Ordinarily this would be down to the fact that my SQL is shocking, I hit the 'Preview...' button whilst the workbook the source is pointing at was open and it works fine??
 
I can't figure this out, but needless to say the package errors with a NEEDSNEWMETADATA when I try and run it.

View 17 Replies View Related

Using Cube Views As A Data Source To Build Mining Model

Nov 16, 2006

Hi all,

I am new to SQL Server 2005 Analysis Services and would like to use the OLAP Cubes as a datasource to build Mining Model . However i would like to use a particular view of the OLAP cube that i have generated to be used as the datasource for the mining model . I find that i am not able to save the Cube View while browsing the OLAP cube in Business Intelligence Studio. Is there a way i can acheive this requirement.

Any ideas regarding this will be really appreciated.

Thanks & Regards,

Satya

View 6 Replies View Related

OLEDB Source Control

Jan 15, 2008

I was having one package which uses a source query in OLEDB Source Control and fetches the record and a couple of lookups and then an oledb command to insert/update the records in the table using as SP. I changed the source query(Infact the package) and removed in lookup and a different SP was called similar to the old one. But my problem is the package which was before taking only minutes to update 50,000 records is now taking more than 2 hrs. The problem is the number of records it is fetching from the source each time is very less.. its fetching hardly 500 records a time compared to nearly 2500 records before. Where am i going wrong? Any suggestion greatly appreciated.

View 5 Replies View Related

MDX OLEDB Source Vs Datareader

Feb 12, 2008

Hi,

I am running an MDX query in SSIS but I don't know what is the best way of doing this, performance wise.
I know I can run the MDX query through an openquery in the OLEDB, and also run it through a Datareader, no openquery needed.

I know the datareader is slower in a normal basis due to .Net, but in this case the OLEDB is running an open query to a linked server which won't be fast like running a regular SQL.

If anyone knows which of this two run faster in this scenario I'll appretiate if you let me know.

View 1 Replies View Related

OleDB Source And Parameters

May 11, 2007

Hi,



Firtsly - I am new to SSIS if my approach could be improved then I welcome suggestions.



Scenario: I have a large SSIS package that consolidates / summarizes work week information from several data sources. Currently each data flow task in the control flow calculates the from and to date that is filtered on, for example:



DECLARE @FromDT AS DATETIME
SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)

DECLARE @ToDT AS DATETIME
SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)



I would like to remove these statements that appear in most steps and replace them with a global variable that is used throughout the package. This statement would only appear once & it would make the package much easier to run after failure etc.



Problem: I am using Data Reader Source with the 'SQLCommand' property specified. It looks like parameters are only supported if an OleDB connection is used?



So I switched to an OleDB connection and no parameters are recognised in the string - a forum search reveals that parameters in sub queries are not always found properly. The solution to this problem appears to be, to set 'Bypass Prepare' to True but this is a property for the Execute SQL task, not the Data Flow Task source.



Questions:

Does the Data Reader Source control from Data Flow Source toolbox section support parameters?
Can anyone suggest a fix to the OleDB Source issue with Parameters?
Is there a better way to solve my problem e.g. Using Execute SQL Task instead of Data Flow tasks etc

Example SQL:

This SQL is an example of the SQL for the OleDB Data Source (within a Data Flow task)



------------------------------
--RADIUS LOGINS
------------------------------
DECLARE @FromDT AS DATETIME
SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)



DECLARE @ToDT AS DATETIME
SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)



DECLARE @Attempts AS BIGINT
SET @Attempts =
(SELECT COUNT(*)
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN @FromDT AND @ToDT)



DECLARE @Failures AS BIGINT
SET @Failures =
(SELECT COUNT(*)
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN @FromDT AND @ToDT
AND Authen_Failure_Code IS NOT NULL)



DECLARE @Successes AS BIGINT
SET @Successes = @Attempts - @Failures

DECLARE @OcaV1Hits AS BIGINT
SET @OcaV1Hits = (SELECT COUNT(DISTINCT LoginName)
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN
@FromDT AND @ToDT
AND EAPTypeID = 25)



DECLARE @OcaV2Hits AS BIGINT
SET @OcaV2Hits = (SELECT COUNT(DISTINCT LoginName) AS OcaV2Hits
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN
@FromDT AND @ToDT
AND EAPTypeID = 13)


SELECT
@Attempts AS ConnectionAttempts,
@Failures AS ConnectionFailures,
(CAST(@Successes AS DECIMAL(38,2)) / CAST(@Attempts AS FLOAT) * 100) AS SuccessRate,
@OcaV1Hits AS OcaV1Hits,
@OcaV2Hits AS OcaV2Hits



Please remember, I'm new to SSIS - so be detailed in your response. Thanks for your help!

View 5 Replies View Related

Using Varibles In OLEDB Source

Jun 21, 2006

Iam migrating data from one database to another .I want give input of that source and traget database names through globally declared user variables (@sourcename,@targetname)

How i can map the variables in OLE DB Source ..i dint find any option to that .
Can somebody help ?

Thanks
Kumar

View 1 Replies View Related

Debugging OLEDB Source Component

Apr 29, 2008

I am debugging a Data Flow task in my SSIS package. When I run the package in debug mode, one of the OLEDB Data Sources turns red. I have rerouted all Error Output to a flat file, and put a Data Viewer on that path: no rows get sent. When I click the Preview button on this component in Design mode, I see the expected data and get no error messages. The connection does a simple table access...no SQL command. I don't see anything different between this component and other OLEDB sources in the same package that don't trigger any errors. I've tried dropping and re-creating the component with the same results.

What else can I do to debug this?

View 7 Replies View Related

Oracle OleDb Provider As Source

Mar 16, 2007

Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.


Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.

Thanks,



Rick

View 3 Replies View Related

Trouble With OLEDB Data Source

Apr 20, 2006

A little background first. I have a header table and a detail table in my staging area/ods. I need to join them together to flatten them out for load. The Detail Table is pretty deep - approx 100 million rows.

If I use the setting (table or view) and set the table name (say, the detail table), the package starts up nicely. But if I switch the OLE DB Source to using a SQL Statement and then join the tables in the SQL, then the Pre-Execute phase of the package takes a VERY long time. I have waited as long as 30 minutes for this phase to complete, but it never finished.

Another twist...If I take the join select statement out of the OLEDB Source and put it in a view on the server, then swith the OLE DB Source to look at the view using the (table or view) mode, then the package gets through the Pre-Execute phase just fine.

Can someone go into detail as to what the Pre-Execute phase does and why a deep table might make it take a long time? I know already that the pre-execute phase caches the lookups, but not much else.

Any help?

Mark

http://spaces.msn.com/mgarnerbi

View 3 Replies View Related

Stored Procedure As OLEDB Source

Jul 18, 2006

Hi,

at first...
Yes, I have seen this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=366077&SiteID=1 and yes, my Stored Procedure contains a "Set nocount on"...
:-)

My Stored Procedure contains variable SQL-Code to check a table (given by parameter) against some masterdata tables and I would like to write the result of the check to a flatfile to be able to send it via mail.

but evoking the Stored procedure brings up no meta data for the Flatfile Destination - the preview works...

any other hints?
except for doing the ckeck completely in SSIS?
:-P

cheers
Markus

View 1 Replies View Related

Question OLEDB Source With SQL And Parameters

Mar 14, 2008

Hello All,

I understand that the parser is having issues parsing my second command but I was wonder if anyone had stumbled on a work around for this:

-- OLEDB Source has no problem with this:
SELECT *
FROM some_table
where
last_update_dt = ?


-- OLEDB Source errors on this:
SELECT *
FROM some_table
where
Convert( char(12), last_update_dt, 112 ) = Convert( char(12), Cast( ? as DateTime), 112 )

The first SQL statement won't return the desired rows because of the wonderful time stamp, the second works well but the provider won't parse it. I have used the "SQL command from variable" to work around this but we have some tables with 200+ columns, so the only way to use the "SQL command from variable" is to do a select *, which I'm trying to avoid, for both performance reasons and company standards.

Any thoughts?

Thanks,

Raymond

View 4 Replies View Related

Poor Sql Performance In Oledb Source?

Mar 31, 2008

i need to select data by using a very complex sql statement. when i use a ole db source componente and choose SQL command as data access mode the process never ends. but when i put the sql statement in an sql task component it works fine and fast. isn't an oledb source always based on an sql statement (select *)? so how is it possible that this component becomes so slow?

View 11 Replies View Related

OLEDB Source Table Locks?

Sep 15, 2006

Hi All,

Is it possible that an OLEDB Data Flow Source is imposing locks on the source tables? The source is an SQL Server OLTP environment, and although the package will be scheduled to run nightly when the application sees little to no use, I want to be sure that the process isn't impacting any application functions.

Thanks for the advice!

Rocco

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved