No loading data from ScriptComponent.

Dear all,

I've created a Data Flow scenario as follow:

At first I've got a Flat File Source and then Script Component Task and then OleDb Destination, linked among them by arrows, of course. When I run the SSIS all of them is successfully executed except the last task. Why? I don't know but it isn't awared of nothing.

649 rows are passed to Script Component from the file but they aren't going to my Sql table.

Let me know any advice or thought regarding ths.

Thanks a lot,

We need far far more information than you have provided here.

Is it a synchronous or asynchronous compoennt?|||

Hi Jamie,

Thanks for your quick response.

You'll see the full contains for the .net script

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim valorColumna As String

valorColumna = Row.Column9.Substring(1, 1)

If valorColumna = "N" Then

Me.Output0Buffer.IMPBASE = -1 * CDbl(Row.Column10 / 100)

Else

Me.Output0Buffer.IMPBASE = CDbl(Row.Column10 / 100)

End If

Me.Output0Buffer.PORCRETEN = CDbl(Row.Column11 / 100)

Me.Output0Buffer.IMPRETEN = CDbl(Row.Column12 / 100)

Me.Output0Buffer.EJERCICIO = CInt(Row.Column2)

Me.Output0Buffer.CODPROV = CInt(Row.Column7)

Me.Output0Buffer.MODALIDAD = CInt(Row.Column8)

Me.Output0Buffer.NIFPERC = CStr(Row.Column3)

Me.Output0Buffer.NIFREP = CStr(Row.Column4)

Me.Output0Buffer.NOMBRE = CStr(Row.Column6)

Me.Output0Buffer.EJERDEV = CDbl(Row.Column13)

End Sub

I haven't idea if it's asyn or syn

I think that there is some big error very very stupid in all of this.

Thanks again,

Enric

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No column to map

Hello,

I am using oledb source and running sp_helpdb procedure but when I open the columns windows there are no column available.

your help is appreciated.

Thanks.

got the answer workaround is to use

SET FMTONLY OFF

Newby DTS Question

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

Thanks

Mikemike_kilby@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.
>
I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1
>
How would I go about performing such clauses using DTS?

Hi Mike,

Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:

SELECT my_columns
FROM dbo.My_Table
WHERE id ?

The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.

You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.

HTH,
-Tom.|||Thanks for your help Tom,worked a treat.

Thomas R. Hummel wrote:

Quote:

Originally Posted by

mike_kilby@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

>
Hi Mike,
>
Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:
>
SELECT my_columns
FROM dbo.My_Table
WHERE id ?
>
The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.
>
You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.
>
HTH,
-Tom.