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.

dtsx load id list from xls

How to write a dtsx that loads a list of ids from an xls and incorporates
this list into sql ?
For example,
update table1 set column1 = false where id in ( {the list of ids } )On Mar 12, 7:30 pm, "John Grandy" <johnagrandy-at-gmail-dot-com>
wrote:
> How to write a dtsx that loads a list of ids from an xls and incorporates
> this list into sql ?
> For example,
> update table1 set column1 = false where id in ( {the list of ids } )
These directions will get an excel spreadsheet into a table that you
can then do: 'update table1 set column1 = false where id in (select id
from NewlyCreatedImportTable)':
1. Create a new Integration Services project.
2. Right click in the 'Connection Managers' tab and select 'New
Connection...' from the drop-down list.
3. Select the 'EXCEL' type and the 'Add' button.
4. Select the 'Browse...' button to browse for the Excel file and
select the 'Open' button.
5. If the first line of the Excel file contains the column names,
select the radio button to the left of: "First row has column names"
then select the 'Ok' button.
6. Right click in the 'Connection Managers' tab and select 'New OLE
DB Connection...' from the drop-down list.
7. Select the 'New...' button and select a Server name from the drop-
down list and select 'OK' and select the 'OK' button again.
8. Drag and drop a 'Data Flow Task' control from the Toolbox onto the
'Control Flow' tab/pane.
9. Select the 'Data Flow' tab/pane.
10. Drag and drop an 'Excel Source' control from the Toolbox onto the
'Data Flow' tab/pane.
11. Right click the 'Excel Source' control and select 'Edit...'
12. Below 'OLE DB connection manager:' select the connection manager
created in steps 2-5.
13. Select the sheet number from the drop-down list below: "Name of
the Excel sheet:" and then select 'OK.'
14. Drag and drop an 'OLE DB Destination' control from the Toolbox
onto the 'Data Flow' tab/pane.
15. Drag the green arrow from the 'Excel Source' control to the 'OLE
DB Destination' control.
16. Right click the 'OLE DB Destination' control and select the
'Edit...' button
17. Below 'OLE DB connection manager:', select the connection control
created in steps 6-7.
18. Select the 'New...' button and edit the sql query for the table
name and column names of the new table to be created (NOTE: SSIS is
very picky on the conversion types so
change datatypes very carefully, if at all).
19. Select the 'Mappings' option from the left-hand-side and map the
Input Columns and Destination columns as desired.
20. Select the 'OK' button and select 'F5' to execute the new package.
Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

DTS!! Plese help!

I've been stuck with this for a week and I seriously need help!

I have a DTS package with only 2 tasks, an ExecuteSqlTask that loads global variables and a TransformDataTask.

The TransformDataTask "source" is a query over one table, and the "destination" is another table. The transformation itself is an ActivexScript, written in VBscript, that performs several checks over source data and retrieves "data warehouse keys" from the DW tables using lookups.

So far, everything seems ok.
If I test the transformation it works fine, and produces a text output that (for testing purposes) I have tried to insert into the destination table, and this also works!!!!
But when I try to execute the package via Enterprise Manager the execution halts. I've set "Row Count Step" to 1, in order to guess wich line had the problem. This number changes but independetly from sorting data. Execution halts when less than 100 rows have been processed!!!! It actually doesn't halt, it says "Running" but stays like that for even days!!!!!!

Ok, I' ll send the code.
All the lookups "retrieve" values, except "actualiza_valores" that performs an update.

Any ideas would be apreciated!!!!
thanks in advance
lorena

Function Main()

Dim ASIGNADA
Dim CONFIRMADA
Dim NO_REALIZADA
Dim ULTERIOR
Dim PRIMERA_VEZ
Dim PROGRAMADA
Dim NO_PROGRAMADA
Dim NUEVA
Dim REPETIDA
Dim fecha
Dim TIEMPO_ESPERA
Dim cantAnio
Dim edad
Dim msg

ASIGNADA=0
CONFIRMADA=0
NO_REALIZADA=0
ULTERIOR = 0
PRIMERA_VEZ=0
NUEVA = 0
REPETIDA=0
TIEMPO_ESPERA= 0
PROGRAMADA = 0
NO_PROGRAMADA = 0
cantAnio = 0
msg="hola"

'Hay que ver si hay una consulta para el mismo paciente, profesional, servicio y fecha.
'Si es asi se actualizan las medidas de ese hecho.
'El tiempo de espera sera el promedio de los tiempos de espera

fecha = DTSLookups("get_id_fecha").Execute(DTSSource("fecha_consulta"))
servicio =DTSLookups("get_id_servicio").Execute(DTSSource("servicio_clave"))

If IsNull(DTSSource("profesional_clave")) Then
msg="El profesional llego NULL"
DTSLookups("set_entrada_log").Execute(msg)
profesional = DTSLookups("get_id_profesional").Execute(-1)
Else
profesional = DTSLookups("get_id_profesional").Execute(DTSSource("profesional_clave"))
End If
If IsEmpty(profesional) Then
msg="El profesional "&CStr(DTSSource("profesional_clave"))&" no esta en la dimension"
' DTSPackageLog.WriteStringToLog msg
profesional = DTSLookups("get_id_profesional").Execute(-1)
End If

cliente=DTSLookups("get_id_cliente").Execute(DTSSource("cliente_clave"))
If IsEmpty(cliente) Then
' DTSPackageLog.WriteStringToLog "El cliente"&CStr(DTSSource("cliente_clave"))&" no esta en la dimension"
cliente = DTSLookups("get_id_cliente").Execute(-1)
End If

cantAnio = DTSLookups("cant_consultas_ao").Execute(cliente, servicio)
If (cantAnio>0) Then
REPETIDA = 1
NUEVA=0
Else
NUEVA = 1
REPETIDA=0
End If

'confirmada, asignada, nopresentada
If StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_cit").Execute())=0 Then
ASIGNADA=1
ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_visit").Execute())=0 Then
CONFIRMADA=1
ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_np").Execute())=0 Then
NO_REALIZADA=1
End If

If IsNull(DTSSource("TIPO_CEX")) Then
ULTERIOR = 0
PRIMERA_VEZ=0
ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_id_prim_vez").Execute()) Then
PRIMERA_VEZ=1
ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_id_ult").Execute()) Then
ULTERIOR = 1
End If

If IsNull(DTSSource("TIEMPO_ESPERA")) Then
TIEMPO_ESPERA=-1
Else
TIEMPO_ESPERA=DTSSource("TIEMPO_ESPERA")
End If


If DTSLookups("existe_consulta_dia").Execute(cliente, profesional,servicio,fecha) = 0 Then
'NO HAY EN LA BASE UNA CONSULTA PARA ESE CLIENTE EN ESE SERVICIO Y FECHA
DTSDestination("CLIENTE_ID") = cliente
DTSDestination("PROFESIONAL_ID") = profesional

If (DTSSource("edad")<=0) Then
DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(0,0)
Else
If IsEmpty(DTSLookups("get_id_gr_etareo").Execute(CInt(DTSSource("edad")),CInt(DTSSource("edad")))) Then
DTSPackageLog.WriteStringToLog "No sabe calcular para "&CStr(DTSSource("edad"))
Else
DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(CInt(DTSSource("edad")),CInt(DTSSource("edad")))
End If
End If

If IsNull(DTSSource("ICD_COD")) Then
DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(-1)
Else
DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(DTSSource("ICD_COD"))
End If

DTSDestination("SERVICIOS_ID")= servicio
DTSDestination("FECHA_ID") = fecha

If IsNull(DTSSource("MOTIVO_CONS_CLAVE")) Then
DTSDestination("MOTIVO_CONS_ID")=DTSLookups("get_id_motivo_cons").Execute(1)
Else
DTSDestination("MOTIVO_CONS_ID") = DTSLookups("get_id_motivo_cons").Execute(DTSSource("MOTIVO_CONS_CLAVE"))
End If

If IsNull(DTSSource("PROFESIONAL_DERIV_CLAVE")) Then
DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLookups("get_id_profesional").Execute(-1)
Else
DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLookups("get_id_profesional").Execute(DTSSource("PROFESIONAL_DERIV_CLAVE"))
End If
DTSDestination("TIEMPO_ESPERA") = TIEMPO_ESPERA

DTSDestination("CONSULTAS_CONFIRMADAS") = CONFIRMADA
DTSDestination("CONSULTAS_ASIGNADAS") = ASIGNADA
DTSDestination("CONSULTAS_NO_REALIZADAS")=NO_REALIZADA
DTSDestination("CONSULTAS_PRIMERA_VEZ") = PRIMERA_VEZ
DTSDestination("CONSULTAS_ULTERIORES")=ULTERIOR
DTSDestination("CONSULTAS_NUEVAS")=NUEVA
DTSDestination("CONSULTAS_REPETIDAS")=REPETIDA
DTSDestination("CONSULTAS_PROGRAMADAS")=PROGRAMADA
DTSDestination("CONSULTAS_NO_PROGRAMADAS")=NO_PROGRAMADA
DTSDestination("cant_consult") = 1


msg="Inserto "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)

'MsgBox(msg)
Main = DTSTransformStat_OK
Else
'HAY EN LA BASE, SE ACTUALIZA
a= DTSLookups("actualiza_valores").Execute(TIEMPO_ESPERA, CONFIRMADA,ASIGNADA,NO_REALIZADA,PROGRAMADA,NO_PRO GRAMADA,PRIMERA_VEZ,ULTERIOR,NUEVA,REPETIDA,CLIENT E,PROFESIONAL,SERVICIO,FECHA)

msg="Actualizo "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)
'MsgBox(msg)
Main = DTSTransformStat_SkipRow
End If


End FunctionI have just looked through the code and it all looks fine.

I suspect your problem lies elsewhere. Perhaps in the data you are processing or in the target table you are going to.

I'm not sure what it could be though. Perhaps some sort of lock being generated on the table or database. I really don't know.

It's possible that your MDAC installation is corrupted (it can cause some very strange behaviour). If possible try installing MDAC 2.8 and see if that helps. It won't hurt that is for sure.

Sorry I couldn't be of more help.