Dumb question

I have a simple flow that loads a data table from some flat files. It works properly but I can't figure out how to add only rows that exist (so I won't get an error from the duplicate ID). I added a lookup that redirects records that don't match any ID, but when I run it I get a timeout error (?). It seems to pick up the right # of records to add, but when it gets to the SQL Server Destination it seems to generate a timeout.

SSIS package "ImportAL3.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [872]: Truncation may occur due to inserting data from data flow column "SampleID" with a length of 4000 to database column "SampleID" with a length of 10.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 17]) + REPLICATE(" ",10 - LEN(TRIM([Column 17])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 2]) + REPLICATE(" ",25 - LEN(TRIM([Column 2])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [872]: Truncation may occur due to inserting data from data flow column "SampleID" with a length of 4000 to database column "SampleID" with a length of 10.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 17]) + REPLICATE(" ",10 - LEN(TRIM([Column 17])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 2]) + REPLICATE(" ",25 - LEN(TRIM([Column 2])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002785.AL3" has started.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 17]) + REPLICATE(" ",10 - LEN(TRIM([Column 17])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 2]) + REPLICATE(" ",25 - LEN(TRIM([Column 2])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Information: 0x400490F4 at Data Flow Task, LookupGrade [2832]: component "LookupGrade" (2832) has cached 11 rows.
Information: 0x400490F4 at Data Flow Task, LookupTestID [5608]: component "LookupTestID" (5608) has cached 0 rows.
Error: 0xC0202009 at Data Flow Task, SQL Server Destination [872]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002785.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002785.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002786.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002786.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002786.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002787.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002787.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002787.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002788.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002788.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002788.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002789.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002789.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002789.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002790.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002790.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002790.AL3" has ended.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (872)" wrote 6 rows.
Warning: 0x80019002 at Data Flow Task: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Task failed: Data Flow Task
Warning: 0x80019002 at ImportAL3: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ImportAL3.dtsx" finished: Failure.

You have quite a few errors there.

You might want to replace the SQL Server destination with an OLE DB Destination.

It seems that you have the right idea using the lookup transformation. The red arrow will direct non-matches.|||Thanks for the help, that worked!

Actually, I get just one error - the rest are just truncation warnings. I don't see any need to address them - if the file has a >4,000 character key, it's been corrupted, and I want it to fail in that case! The one error that I did get, is solved by using an OLE DB destination. (I don't intend on scanning all files, every time, normally I should get no duplicates, but for the sake of error tolerance I had to put a check in.)

Now I'm doing a bit of reading to see just why the SQL Server Destination fails in such a case - it emphasises BULK transfers, I'm not sure of the significance of this.

Another dumb question, is there any way to post graphic files here? Since coding is being replaced by a graphical interface, you can't really post the code here.
|||You need to have your SQL Server destination database running on the same machine that the package is stored on. Also, a SQL Server destination won't perform any automatic type conversions -- you have to be sure that the metadata going into it is an exact match for the SQL Server table data types.|||Well, destination database is on the same machine. And when I went to the datatable, cleared out the records in question, and removed the SSIS module that checked for duplicate keys, and just went straight from the flat files into the database, it worked fine.

DTSTransform.DataConvert programmatic access?

I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.

The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.

I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.

I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?

Here is what is left of my code:

IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}

Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.

Phil Burns

Aptify

DTSTransform.DataConvert programmatic access?

I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.

The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.

I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.

I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?

Here is what is left of my code:

IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}

Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.

Phil Burns

Aptify

DTS Work Flow issue

OK, this shouldn't be this hard, I'm sure I am missing something
simple.
I have a DTS package with 1 source connection and 1 destination
connection.
I want to transform 2 tables, each as a separate Transformation task.
Now to the isssue.
(this one I get..)
If both Succeed, execute another set of procedures..
(This one I can't seem to figure out)
If Either of them fail, Execute a different set of procedures.
How can you set an OR precedence on failure?
TIA!
DavidOn Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david.hay@.gmail.com>
wrote:
>OK, this shouldn't be this hard, I'm sure I am missing something
>simple.
>I have a DTS package with 1 source connection and 1 destination
>connection.
>I want to transform 2 tables, each as a separate Transformation task.
>Now to the isssue.
>(this one I get..)
>If both Succeed, execute another set of procedures..
>
>(This one I can't seem to figure out)
>If Either of them fail, Execute a different set of procedures.
>How can you set an OR precedence on failure?
On success of a step, go to another step that sets a variable.
Have a step downstream that reads both variables.
And check out the .dts newsgroup!
J.|||On Aug 10, 12:17 am, JXStern <JXSternChange...@.gte.net> wrote:
> On Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david...@.gmail.com>
> wrote:
>
> >OK, this shouldn't be this hard, I'm sure I am missing something
> >simple.
> >I have a DTS package with 1 source connection and 1 destination
> >connection.
> >I want to transform 2 tables, each as a separate Transformation task.
> >Now to the isssue.
> >(this one I get..)
> >If both Succeed, execute another set of procedures..
> >(This one I can't seem to figure out)
> >If Either of them fail, Execute a different set of procedures.
> >How can you set an OR precedence on failure?
> On success of a step, go to another step that sets a variable.
> Have a step downstream that reads both variables.
> And check out the .dts newsgroup!
> J.
Thanks!

DTS Work Flow issue

OK, this shouldn't be this hard, I'm sure I am missing something
simple.
I have a DTS package with 1 source connection and 1 destination
connection.
I want to transform 2 tables, each as a separate Transformation task.
Now to the isssue.
(this one I get..)
If both Succeed, execute another set of procedures..
(This one I can't seem to figure out)
If Either of them fail, Execute a different set of procedures.
How can you set an OR precedence on failure?
TIA!
DavidOn Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david.hay@.gmail.com>
wrote:
>OK, this shouldn't be this hard, I'm sure I am missing something
>simple.
>I have a DTS package with 1 source connection and 1 destination
>connection.
>I want to transform 2 tables, each as a separate Transformation task.
>Now to the isssue.
>(this one I get..)
>If both Succeed, execute another set of procedures..
>
>(This one I can't seem to figure out)
>If Either of them fail, Execute a different set of procedures.
>How can you set an OR precedence on failure?
On success of a step, go to another step that sets a variable.
Have a step downstream that reads both variables.
And check out the .dts newsgroup!
J.|||On Aug 10, 12:17 am, JXStern <JXSternChange...@.gte.net> wrote:
> On Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david...@.gmail.com>
> wrote:
>
>
>
>
>
>
>
>
> On success of a step, go to another step that sets a variable.
> Have a step downstream that reads both variables.
> And check out the .dts newsgroup!
> J.
Thanks!

DTS vs BCP vs TSQL

Hi,

As I know, although DTS gives graphical view of the data transformation/conversion work flow, it tend to be slower than BCP and TSQL scripts. So, keeping all that in mind, can I still use DTS (b'cos its a good tool to illustrate what’s happening inside, easy to understand and maintain) but with TSQL scripts (i.e: insert into...) doing the operations instead of DTS copy column or vb scripting, to gain maximum performance provided by the TSQL scripts? If you have other view points pls advice., What is the possibility of calling BCP exe inside DTS ?.

Let me provide you info. about the work I need to do, following are the things I need to do;

1. SQL source to SQL destination (different servers)

2. Transformations same SQL server and the same database

3. SQL source to text files.

Thanx

According to what I found, Yes you can call BCP inside DTS, which is very handy. Furthermore BCP and copy column transformations gave almost same performance.

|||Based on what you need, you definitely will need DTS for the ransformation. BCP is just to prepare your data for bulk copy into staging tables. Scrub and transform your data using DTS

DTS to Access mdb - updates only

Is there a way to flow updates only to an Access mdb from SQL DTS. We have
a sql table that is a copy of an Access table, all adds/updates/deletes are
made to the sql table and replicated to the Access table. The problem is
the only options for replicating the data to the Access table are a.)Append
or b.) Full Table Delete then Insert. Append is out because we would get
duplicates, Full Table Delete/Insert works but this means for several
seconds we have an empty Access table (problem because many apps are reading
from the Access table 24/7). Is there a way to create a DTS job (or some
other strategy) to perform updates to an Access table in addition to inserts
and individual deletes. My goal is also avoid writing a full blown
sql/Access replication program.
Thanks,
JimHi
You may want to look at SQL Servers own replication options.
If your access database was a linked server your could write a T-SQL query
to insert only the rows that did not exist already in the table.
e.g.
INSERT INTO linkedsvr...accesstbl ( pkcol, col1, col2 )
SELECT pkcol, col1, col2 FROM SQLServerTable s
WHERE NOT EXISTS ( SELECT * FROM linkedsvr...accesstbl a WHERE a.pkcol =
s.pkcol )
John
"Jims" wrote:

> Is there a way to flow updates only to an Access mdb from SQL DTS. We hav
e
> a sql table that is a copy of an Access table, all adds/updates/deletes ar
e
> made to the sql table and replicated to the Access table. The problem is
> the only options for replicating the data to the Access table are a.)Appen
d
> or b.) Full Table Delete then Insert. Append is out because we would get
> duplicates, Full Table Delete/Insert works but this means for several
> seconds we have an empty Access table (problem because many apps are readi
ng
> from the Access table 24/7). Is there a way to create a DTS job (or some
> other strategy) to perform updates to an Access table in addition to inser
ts
> and individual deletes. My goal is also avoid writing a full blown
> sql/Access replication program.
> Thanks,
> Jim
>
>|||John - it was my understanding that linked access databases were read-only.
Have you heard different?
Thanks,
Jim
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2FC8D1AB-6784-455B-98F6-2231C2357228@.microsoft.com...
> Hi
> You may want to look at SQL Servers own replication options.
> If your access database was a linked server your could write a T-SQL query
> to insert only the rows that did not exist already in the table.
> e.g.
> INSERT INTO linkedsvr...accesstbl ( pkcol, col1, col2 )
> SELECT pkcol, col1, col2 FROM SQLServerTable s
> WHERE NOT EXISTS ( SELECT * FROM linkedsvr...accesstbl a WHERE a.pkcol =
> s.pkcol )
> John
> "Jims" wrote:
>|||Hi
The easiest way to check this out is to create yourself a test linked
server. I have certainly managed to insert/update data in a linked access
database. It could be that you have not set up permissions correctly . See
example B in the "sp_addlinkedserver" topic in books online on how to create
a linked access server.
John
"Jims" wrote:

> John - it was my understanding that linked access databases were read-only
.
> Have you heard different?
> Thanks,
> Jim
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:2FC8D1AB-6784-455B-98F6-2231C2357228@.microsoft.com...
>
>|||This is definitely possible.
There are a series of permissions that need to be set in order to do this.
RSH
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABFC3E5D-2C84-471A-BB5F-622A540ED955@.microsoft.com...
> Hi
> The easiest way to check this out is to create yourself a test linked
> server. I have certainly managed to insert/update data in a linked access
> database. It could be that you have not set up permissions correctly . See
> example B in the "sp_addlinkedserver" topic in books online on how to
> create
> a linked access server.
> John
> "Jims" wrote:
>