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


ADVERTISEMENT

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

XML Source - No External Columns Avail-- Syntax Error In XML/XSD?

Jan 28, 2008

Hi All,

Hopefully someone can help me with what I'm sure is a very simple question (new to the XML thing). I receive an XML file from "someplace" that I need to parse out using the XML Source in SSIS. I have SSIS generate me an XSD document, as one isn't provided for me. However, after I do this, SSIS does not show any available external columns to pull data from- the "Columns" section of the source is just blank. I'm pretty sure this has to do with a syntax error in either the XML file that is being provided to me, or the XSD doc that SSIS is generating. Below are both (obviously with data dummied up). Can someone take a look and let me what needs to be changed in either file to get this up and running? I'm looking to grab the AccountNumber, RecordNumber, ProcessedDate, Status, and StatusMessage elements.


XML File:



Code Snippet
<?xml version="1.0" encoding="utf-16"?>
<AccountResponse xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<AccountNumber>S12345678</AccountNumber>
<RecordNumber>AAA1122</RecordNumber>
<ProcessedDate>Monday, January 28, 2008 11:07 AM</ProcessedDate>
<Status>0</Status>
<StatusMessage>Complete</StatusMessage>
</AccountResponse>



XSD File:



Code Snippet
<?xml version="1.0"?>
<xsd:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified">
<xs:element name="AccountResponse">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="AccountNumber" type="xs:string" />
<xs:element minOccurs="0" name="RecordNumber" type="xs:string" />
<xs:element minOccurs="0" name="ProcessedDate" type="xs:string" />
<xs:element minOccurs="0" name="Status" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="StatusMessage" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xsd:schema>
Thanks!

View 1 Replies View Related

Error: The External Metadata Column Collection Is Out Of Synchronization With The Data Source Columns

Apr 17, 2007

Hello,

I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.



I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.



On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:

Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....



What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.



Can anyone help me to resolve this issue.



Thanks.

View 3 Replies View Related

Custom Properties In External Metadata Not Writeable Using The Advanced Editor

Jun 30, 2006

Hello,

I'm working on a custom dataflow destination component. It makes use of the External Metadata Collection. I also use Custom Properties with the external metadata collection.

When I open the destination component using the Advanced Editor, and select an External Metadata Collection and change the Custom Property it always changes back to the original value.

Additionally the method SetExternalMetadataColumnProperty never gets called.

Here is a little Test Component that surfaces the problem:



[DtsPipelineComponent(ComponentType=ComponentType.DestinationAdapter, DisplayName="Test Destination")]
public class Class1 : PipelineComponent
{
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();

ComponentMetaData.InputCollection.RemoveAll();
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Name";
input.ExternalMetadataColumnCollection.IsUsed = true;

IDTSExternalMetadataColumn90 ext = input.ExternalMetadataColumnCollection.New();
ext.Name = "ExtName";
IDTSCustomProperty90 customProp = ext.CustomPropertyCollection.New();
customProp.Name = "Custom Property";
customProp.Value = "Hallo";
}

public override IDTSCustomProperty90 SetExternalMetadataColumnProperty(int iID, int iExternalMetadataColumnID, string strPropertyName, object oValue)
{
return base.SetExternalMetadataColumnProperty(iID, iExternalMetadataColumnID, strPropertyName, oValue);
}
}

Any ideas how to make that work?

Georg

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

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

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

XML Source And External Configuration

Jan 17, 2006

hi guys,

Is possibile configure as expression the properties XMLData and XMLdataDefinition of a "XML Source"? looking the editors it seems it is not possibile... I think it is a great limit of the component because I must hardcode the path!

Regards,

M.rkino

--
Marco Barzaghi - [MVP - MCP]
http://mvp.support.microsoft.com - http://italy.mvps.org
UGIDotNet - User Group Italiano .NET, http://www.ugidotnet.org
Read my WebLog: http://blogs.ugidotnet.org/markino

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

Please Help -- So Stuck Now -- Cant Connect To Sql From External Source

Jul 20, 2005

Hi all,I am having real problems allowing a client to connect to sql 2000through enterprise manager.. I have tried all sorts but to no avail:From our local network all works fine.. Using the adsl line.. Even ifi try and connect to the external IP and use login details..(Asopposed to connecting local and using windows accounts) I can ping andtelnet the server and port.The server is behind a firewall so i opened up port 1433 and port1434(? is it needed).. To replicate the client i took the same laptophome that i use to connect in the office.. However using the exactsame config on my dial up at home i can ping the server but cannottelnet onto port 1433.. I think that this is the vital missing stepbut do not know hpw to resolve it..Any ideas anyoneCheersAnt

View 1 Replies View Related

Creating A View Of An External Source

Nov 7, 2007



Hello,

I want to create a view in sql 2000 that will pull in data from a ms access database. What is the correct syntax for the select statement?
Thanks

View 4 Replies View Related

External Columns Vs. Output Columns?

May 23, 2006

Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?

TIA,



Barkingdong

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

Transferring Data Between External Source And SQL-server

Dec 9, 1999

How can I move data from an external source to SQL-server 6.5.

If you for example has an oracle database on a UNIX or VMS-system and want
to move table data from a table in the oracle database to the SQL-server database.

There must be plenty of tools to do that via ODBC.

Any suggestions ?

View 1 Replies View Related

Starting A Job Based On External Source/event

Jun 6, 2003

We run a weekly update on one of our databases from a download (FTP) from our mainframe. What I want to do is start the update job automatically after all of the files are downloaded.

I have a similar process in another database where the users set a flag in an application after they've made certain changes. The job fires daily at 18:00 and checks the value of the flag in the table. It then processes or goes back to sleep. This works great but there is no flag that the users can update in this application.

Thanks in advance.

Sidney Ives
Database Administrator
Sentara Healthcare

View 2 Replies View Related

Code For Creating A Table In SQL From External Source

Nov 18, 2005

I'm looking to create a table in SQL using data from the as/400. I need some code that extracts it from as/400 using odbc and I need it to loop and create that table every minute.
The file its retrieving is a live file but not very large.. I need to basically poll the data. Does anyone have code for something like this..
thanks

View 8 Replies View Related

How To Make SISS Using CRM 3.0 As External Data Source

Jan 8, 2008

Hi All,


I have a knowledge of SISS and CRM 3.0 but don't get any proper steps to create such Integration Service in SQL Server 2005 using CRM 3.0 as external data source.

I don't get any option of external data source for CRM.

So if anybody know about this then let me know steps to do that.

Thanx in advance.

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

Can You Add Output Columns To The Script Transformation Editor On The Fly?

Jun 21, 2006

Can I add Output Columns to the Script Transformation Editor using code? I have to execute a SQL Statement to determine the number of years we have the data for for an item and then create the columns for the months in those years and populate them with the quantities. So my question is can I create output columns to the Script Transformation Editor on the fly that is as it is being executed?

Any input will be good.

Thanks,

MShah

View 3 Replies View Related

XML Source Doesn't Validate External Metadata Properly

Sep 26, 2007

I have an XML file that my XML Source component is accessing. I have noticed that is possible to set a column in the external metadata collection to a certain datatype and the matching output column to a different datatype and this doesn't not generate a warning like it does with other source components (e.g. Flat File Source Adapter).

Try it. Set a column in your external metadata to have a datatype of DT_WSTR. Set the matching output column to DT_UI8. You will NOT get a validation error. I think you should.

This behaviour was noticed on RTM (i.e. no service pack installed) by the way.

I think this is a bug. Anyone agree?

-Jamie

View 10 Replies View Related

No External Columns Available From SQL Command?

May 9, 2008

Hello.

I have a OLE DB Data Source Component that uses an SQL command as the data access mode. On the Connection Manager "tab" of the OLE DB Source Editor I can sucessfully parse my query and produce a preview of the data.

However, when I go to the Columns tab I see no available external columns? Apart from the fact I'm using a union I can't think of any reason why I can't see any columns, it doesn't make sense.

Please Help.

Thanks

View 7 Replies View Related

Reporting Services :: External Assembly Reference Same DB Data Source As Calling Report?

Aug 25, 2015

I have a SSRS 2012 report which references a custom c# assembly.   This report exists in multiple environments (alpha, beta and  production ) which are each associated with different data sources.

Is there a way for the assembly to determine the datasource used by the calling report so it can also connect to it?

View 2 Replies View Related

External Columns From Sybase Stored Procedures

May 31, 2006

I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.

1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).

2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.

3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.

It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.

Any ideas would be greatly appreciated!

View 3 Replies View Related

Redefining Columns Isn't Possible With Flat File Connection Manager Editor Properties Already Fixed??

May 29, 2006

Dear fellows,

I know that I think as sql2k programmer-dba yet but I can€™t avoid.

I€™ve got Flat File Connection Manager Editor dragged with a text file as €˜ragged right€™ format and CRLF as header row limiter. When from properties page and Columns option I€™m going to alter just a few colums I am not be able.
It seems that you must erase all of them in order to define one or two. And in the case you€™d have 50????
When I ran sql2k DTS designer did that without problems, alter columns again and again.

As far as I know it€™s a lose of flexibility, or not? Or is there any way for do that without deleting nothing else?

View 3 Replies View Related

OLE DB Source To Flat File Destintation Using Fixed Width Columns - Determining Source Column Width

Feb 13, 2007

Hi,

I am trying to create a program that transfers tables to flat files.
At this point in time, I have suceeded in created one that creates delimited files.

However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.

Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.

I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.

View 5 Replies View Related

Set The Data Source Of Data Flow From External Application (C#)

Jan 11, 2006

I am new to SSIS programming, so bear with me if my question seems naive to you gurus. I have a situation that needs to set the data source for a data flow from external .NET application ('external' means that the application will run on different process than the SSIS). I am trying to set the data source on which the data flow works from my C# application in a DataSet format. Ideal solution is not to save the DataSet to any file on harddisk (I know that will work, but has the overhead of writing, reading and managing the temp file). What I want to achive is that the business logic of picking data for SSIS Data Flow to process is controlled inside my C# application, the Data Flow just does what it does best - Transformation. Have any of you successfully done this before?. Thanks!

View 1 Replies View Related

Target And Source Columns DataType

Nov 27, 2011

I need to create an Bulk upload utility using ASP.Net and SQL Server. Below is the process for the uploads -

Excel Template wherein user will enter the details. A Tab-delimited output file will be generated using the VBA.
There are 2 tables - one is Temp Table which is replica of the the final table and second is the final table
Using File.OpenText(filePath).ReadLine() - All the Rows from the tab delimited data file will be inserted into DataTable.

using SQLBulkCopy the tab-delimited data file data will be inserted into the Temp Table.

Data will be validated based on the data inserted in the temp table. If the data as errors then the temp table will be cleared else the data will be inserted from the temp table to the final table.

My Issue is that in both the tables there is a column (Name : PeopleKey (Int PrimaryKey)). If the user enters Alphabetic value then the Bulk Utility is failing. Below are the two options in my mind -

1. I can change the DataType in Temp table from int to VARCHAR. So, the data can be inserted at first and then I can validate and get the data corrected. But i am not sure whether it is the right way to fix issue as the source and target tables columns are different.

2. When the data in inserted into the Datatable by following Step 3. So, once the data in inserted into DataTable then i can validate there. Thus the source and target tables Datatype will be same.

View 1 Replies View Related

Copy Columns From Source To Destination

Apr 10, 2007

Hi all,

I want to copy 2 columns from 1 database to another database.
I managed to do this, using a Ole DB source and a Ole DB destination dataset.

Now I want to merge 2 colums into 1:
Source Database: Column A: first name, Column B: Lastname.
Destination Database: Column 1: First and lastname customer.

Thanks for your help!

Kind regards,

Marcel Hijnen
eXDe Solutions B.V.

View 4 Replies View Related







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