How To View AS400 Library Files From OLEDB Source (SSIS)

Jan 3, 2007

HI,

I'm trying to get data from AS400. using OLEDB source as my connection. i'm using IBM OLEDB provider for iSeries. and working on standard edition of SQL Server 2005.

While using OLEDB source task when i set my access mode to 'table or view' and try to see list of available libraryname.tablenames, i do not get and tables

where as when i use Data access mode as 'SQL Command' i can get data (can only see preview of data) from AS400 but not able to insert that into my destination table. At run time task Fails with the error mentioned below.

I have configured Data links tab inside the OLEDB connection manager also, but when tried to set a default library it gives me error. : "Error code :CWBZZ5042" - ( catalog is invalid ) but it does exist.

Is there some settings that needs to be done from AS400 side or SQL Server side to view the available libray and its tables ?

Can some one help me on the same.

thanks in advance

Shah

Error Message received when executed with SQL command:

Error: 0xC0202009 at Data Flow Task, OLE DB Source [1]: An OLE DB error has occurred. Error code: 0x80040E00.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Task failed: Data Flow Task

View 4 Replies


ADVERTISEMENT

SSIS OLEDB Source Data Access Mode (Table Name Or View Name Variable)

Apr 3, 2007

Thanks for any one can give me a help.

I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "===================================

Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided.

(Microsoft Visual Studio)

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

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

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)".

Some one can tell me what is the reason, or give me some examples.

Thanks in advance.

View 7 Replies View Related

SSIS Source Code Library For Others To Use

May 12, 2006

In SSIS can one create €œuser defined functions€? €œsubroutines€? and put them in the SSIS designer for other developers to use?

TIA,
Bill

View 12 Replies View Related

Problem:long Running Validation When Pointing OLEDB Source To A View.

Jan 24, 2007

One of our developers has written a view which will execute completely (returns ~38,000 rows) in approx 1 min out of SQLMS (results start at 20 sec and completes by 1:10 consistently).

However, if he adds a data flow task in SSIS, adds an OLEDB Data Source and selects Data Access Mode to "Table or view" and then selects the same view, it is consistently taking over 30 minutes (at which point we've been killing it). I can see the activity in the Activity Monitor, it is doing a SELECT * from that view and is runnable the whole time.

If we modify the view to SELECT TOP 10, it returns in a short time.

Has anyone run into this problem? Any suggestions? It is very problematic, as if the views change we have to hack around this problem.

Thanks for any responses.

Jeff

View 5 Replies View Related

SQL 2012 :: SSIS OLEDB Source Date Parameter

Sep 22, 2015

Passing a date parameter in a stored procedure through OLEDB Source (Data flow task). We have a multipurpose stored proc in which one of the many expected parameter is a date. It keeps giving me the following error:Description: "Operand type clash: int is incompatible with date"- although there is no integer.

One way to fix it would be change the data type from date to datetime in proc but I can't do that since it is multi-purpose proc. I didn't find anything good on the net.

View 2 Replies View Related

SSIS Dataflow OLEDB Source Issue With DB2 Connection.

Jun 23, 2006

Hi,

I have problem to extract data from DB2 table using SSIS dataflow task with OLEDB source selecting the data access mode with command. Here is the scenario.(Note:If I select the data access mode as a table then it works fine.)

If I use the data access mode as SQL Command then it is not working. i.e. the flow stays yellow in controlflow nothing happens in dataflow and I noticed the SQL dumper and proc completes. I couldn't find any log to verify the issue including the package execution results.Any of your thoughts will help me to resolve this issue.

Thanks..Mako

View 1 Replies View Related

Using XML Source In SSIS Vs Script Source To Parse Xml Files.

Sep 14, 2007



Hi

I am having a huge xml file with nested section.
i also have a xsd file for that xml.
i have a destination table where the data from the xml should be loaded into.
i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.

so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination.
on comparing the two approach the one using the script source is working much faster than the xml source transformation.

i wanted to know is there any limitaion using the script source to parse through xml files.
also i would like to know any other better way of getting the data from xml source without using the joins.


Hari

View 7 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

Integration Services :: SSIS OLEDB Data Source - Flat File Generation

Apr 20, 2015

I am working to archive some old data from a data warehouse using SQL server and SSIS.  The data will be read and denormalized, then shipped out to a delimited text file.

The rowcount of the incoming data is significant, call it 10M+ rows per unit of work (one text file).

There are development advantages of using a stored proc for the data source - mainly ease of changing the denormalization logic as required.  Wondering if there are performance advantages of an embeded query for the data source instead?

It was mentioned by one developer that when using a stored procedure, the output stream from the proc and subsequent SSIS steps cannot start until the full procedure processing is complete; i.e. the proc churns out its' result set in one big chunk. 

He hinted that an embedded query does not have this same effect, but I am not sure that is accurate.

View 4 Replies View Related

Permissions For OleDb To An As400

Apr 9, 2007

I have a store procedure written in VB.NET to access an AS400 using System.Data.OleDb. I created the key for the procedure as a .pfx file and before adding the code to access the AS400, I tested it against a SQL Server database and it worked fine. When I added the code to access the AS400, I get the following error:



A .NET Framework error occurred during execution of user defined routine or aggregate 'ap_mapics_Data':

System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.OleDb.OleDbConnection.PermissionDemand()

at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.OleDb.OleDbConnection.Open()

at ProImage_Procedures_Test.StoredProcedures.ap_mapics_Data()

View 1 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

Using SSIS Package To Delete Source Files

Jan 31, 2013

is it possible to delete a source file(*.txt) when a SSIS package is done with it?

View 3 Replies View Related

SSIS - Dealing With Missing Source Files

Jul 9, 2007



I want to skip running the SSIS data flow task when the source file is missing. We have a scheduler that copies the source file to the staging area. This SSIS package runs as SQL server job. So when a SSIS package fails due to missing file the remaining steps in the SQL scheduler won't execute. I want to handle the missing source file condition grace fully. Please advise.



Thanks in advance.

View 1 Replies View Related

T-SQL (SS2K8) :: Load Data From Flat File Source Into OleDB Destination By Changing Data Types In SSIS

Apr 16, 2014

I have an source file and i have to load it into the data base by changing datatype of the columns in ssis

View 1 Replies View Related

T-SQL (SS2K8) :: Extracting Source / Dest Column Details From SSIS Package Files?

Jun 30, 2015

We have several hundred very simple ETL SSIS 2K8 package files (*.dtsx).

I'd like to be able to interrogate them to determine source and destination fields.

There's no great need to map source to dest or to extract data types.

So far, the most promising candidate is to load them using OPENROWSET into an XML field in a SS table.No problem there, but querying using OPENXML has me stumped.

The package files will change a couple of times per year, so the process needs to be repeatable with minimal manual intervention.

View 3 Replies View Related

Manage A Data Source For A Report Found In A SharePoint Document Library

Jan 18, 2007

I was working for 2 days on integrating reporting services with MOSS 2007.



The problem i faces was to create a data source for the
uploaded report (Not created using a wizard ) that will connect
me to the SQL server.



As i clicked on the arrow near the uploaded report to manage the data source, i found no

data source for this item.



I would be pleased to know the howto create this data source and where.



Best Regards,



Lana

View 4 Replies View Related

How To Import Data From AS400 Files To Sql 2005 Tables?

Apr 18, 2007

Hi All,

I want to Import data from AS400 to Sql2005. From the Sql Management studio I invoke the Import Data wizard. For the source I connect to the ISereis system and for the Destination I select the Sql2005 Database , When I go to the next step i.e select source tables I get the error
"An error has occured which the SQL Server Integration Wizard was not prepared to handle
No error message available,result codeB_E_CANTCANCEL(0*80040E15).(System.Data)" .After this I am unable to proceed further. I am using client Access tool to connect to ISereis

IBM DB2 UDB for isereis IBMDA400 OLE DB Provider





Regds,

Anu

View 2 Replies View Related

App.Config Files In A Custom Database Extension Class Library

Aug 15, 2006

Good Morning..

We're having a heck of a good time trying to implement our first CDE project in SSRS 2005.

In our SDE class library we have included an App.Config file where we want to store configuration settings..

Trouble is that when we view the configuration settings or connection string settings in debug mode, they're not being read for some reason..

Here's our app.config file:
-------------------------------------------------------------
<?xml version="1.0" encoding="utf-8" ?>


<configuration>

<configSections>

</configSections>

<appSettings>

<add key="eventLogName" value="FocusDPEEventLog" />

</appSettings>

<connectionStrings>

<add name="PassConnString" connectionString="Data Source=SOMEDATASOURSE;Persist Security Info=True;User ID=SOMEUSERNAME;Password=SOMEPASSWORD;Unicode=True"

providerName="System.Data.OracleClient" />

</connectionStrings>

</configuration>

-------------------------------------------------------------
Here's what our Immediate window Debugger is tellin' us about our configuration settings:


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

ConfigurationManager.AppSettings

{System.Configuration.KeyValueInternalCollection}

[System.Configuration.KeyValueInternalCollection]: {System.Configuration.KeyValueInternalCollection}

base {System.Collections.Specialized.NameObjectCollectionBase}: {System.Configuration.KeyValueInternalCollection}

AllKeys: {Dimensions:[0]}<----incorrect should be 1

ConfigurationManager.ConnectionStrings

Count = 1 <----ok, is one, but the wrong 1, see 3 lines down...

base {System.Configuration.ConfigurationElementCollection}: Count = 1

ConfigurationManager.ConnectionStrings[0]

{data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}<--Should be PassConnString

base {System.Configuration.ConfigurationElement}: {data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}

ConnectionString: "data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

Name: "LocalSqlServer"

ProviderName: "System.Data.SqlClient"

Now notice the stuff in bold...I have NO IDEA where this gosh-danged thing is reading, but it doesn't seem like it's the app.config file in our class library...

thanks..

Doug

View 1 Replies View Related

Loading Data Using Ole Db Source With Input Source Being A View

Dec 13, 2007

I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.

The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.

Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?

View 13 Replies View Related

Using SSIS Class Library

Oct 18, 2007



Hi,

I´m wondering if is it possible to use the SSIS Class Library without SQLServer installed in the machine or if is it necessary to install SLQ Server in order to use SSIS objects?

thaks.

View 4 Replies View Related

SSIS And DB2 On AS400

Feb 27, 2008

Hi,

Getting this error. Any ideas?

Test connection failed because of an error in initializing provider. The host resource could not be found. Check that the Initial Catalog value matches the host resource name. SQLSTATE: HY000, SQLCODE: -360

Thanks

View 8 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

SSIS Job To Wait And Run After AS400 Job

Mar 7, 2008



We have a few SSIS jobs that we are currently manually kicking off after we are sure that certain AS400 jobs have run. We want to completely automate this process, so that we don't have to babysit. What is the most efficient way to do this? In the past (on SQL Server 7 no less) I've seen the 400 job setting a flag to 'Y' in a 400 file, FTPing it down to a flat file, and then the SQL job running every five minutes checking the flag. When it was 'Y', the SQL job would run. We do not have the option of using FTP here. Any suggestions would be appreciated! After the job runs, we'd like it to kick off a report as well

View 2 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

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







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