Compare Data Before Inserting

I have a DTS package in which I would like to move to SSIS and also add some new functionality. My current DTS package is pretty simple:

1. Download a flat file from an FTP site
2. Execute a SQL task that deletes the data from the table
3. Insert the data into the table.

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes this can be done in SSIS, but the real questions are what

identifies the data as "new" and how many records are you planning to

process at one time.

If you have a primary key in the source data and you are working with

few records < 50k, then you could perform a lookup on the

destination to see if the primary key exists. If it does not then

you could redirect the row to an insert data flow using the error

output. If it does exist (std output), you could (in the typical

case) direct it to an update data flow.

If you have a primary key and are working with many records, you could

perform a merge join on the source and destination and then use a

conditional split to filter out/redirect the rows where the destination

key is null.

If you have some other identying attributes, then the methodology may

change. You may even want to investigate the Slowly Changing

Dimension transform.

Larry|||

Jason92 wrote:

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes. Check out: http://www.sqlis.com/default.aspx?311

-Jamie

Compare Data Before Inserting

I have a DTS package in which I would like to move to SSIS and also add some new functionality. My current DTS package is pretty simple:

1. Download a flat file from an FTP site
2. Execute a SQL task that deletes the data from the table
3. Insert the data into the table.

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes this can be done in SSIS, but the real questions are what identifies the data as "new" and how many records are you planning to process at one time.
If you have a primary key in the source data and you are working with few records < 50k, then you could perform a lookup on the destination to see if the primary key exists. If it does not then you could redirect the row to an insert data flow using the error output. If it does exist (std output), you could (in the typical case) direct it to an update data flow.
If you have a primary key and are working with many records, you could perform a merge join on the source and destination and then use a conditional split to filter out/redirect the rows where the destination key is null.
If you have some other identying attributes, then the methodology may change. You may even want to investigate the Slowly Changing Dimension transform.
Larry
|||

Jason92 wrote:

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes. Check out: http://www.sqlis.com/default.aspx?311

-Jamie

Compare csv file layout to SQL Server table

I am creating a facility whereby you can select a source file (.csv), and a target (SQL table).

Then I call a DTS to copy the csv file to the SQL table.

How can I validate that the two files have the same number of columns ?

Moved to SQL Server Tools forum|||In order to check the number of rows you need to work on DTS with workflow, refer to books online for more information and you can schedule the DTS package a SQLagent job by right-click on selected package.|||I need to validate that the correct file has been selected before scheduling the DTS package. This is why I need to do it in my VB.Net Windows application.|||

You can write a custom Script Task inside the SSIS package that will open the file and check how many columns it has. You can utilize Excel object model for that or just go with regular .NET IO libraries.

Hope that helps.

Maciek Sarnowicz

Compare csv file layout to SQL Server table

I am creating a facility whereby you can select a source file (.csv), and a target (SQL table).

Then I call a DTS to copy the csv file to the SQL table.

How can I validate that the two files have the same number of columns ?

Moved to SQL Server Tools forum|||In order to check the number of rows you need to work on DTS with workflow, refer to books online for more information and you can schedule the DTS package a SQLagent job by right-click on selected package.|||I need to validate that the correct file has been selected before scheduling the DTS package. This is why I need to do it in my VB.Net Windows application.|||

You can write a custom Script Task inside the SSIS package that will open the file and check how many columns it has. You can utilize Excel object model for that or just go with regular .NET IO libraries.

Hope that helps.

Maciek Sarnowicz

Common DTS Source & Multiple Destinations

I want to run multiple DTS packages which export data into text files.
There is only one Data Source ..and multiple destinations.
When i write a code for this in VB ,for each Package i need to define the source connectioninividually. Can't i use the same Source connection which i used for the first package in the subsequent packages?http://www.sqldts.com/default.aspx?200

Commented Dynamic Properties -- SQL2000 to SQL2005 mgration

Hi,
After DTS Migration is "successful", I opened up dynamic properties step but I saw codes were all commented out inside the public class ScriptMain. Since I am not familiar with Microsoft.SqlServer.Dts.Runtime, I'm not sure what methods I should use as there aren't much examples available online. How do I do the following in SISS way?

' Source(Type = 2)
' Global variable = SPLastDate
' Destination = 'Tasks';'DTSTask_DTSExecuteSQLTask_7';'Properties';'SQLStatement'

How can i convert this code for ssis ?
Does anyone have any solution ? kind of urgency..

Thanks
YaseminYou'd have to use property expressions instead - there is no automatic conversion for Dynamic properties task from DTS 2000.

Command to clear DTS Package Logs + Versions

Hello. Is there any command that you can clear old DTS
package execution Logs? Also, when you store packages as
*.dts files, it saves the versions right in the file. Is
there a command that can clear older versions, either ALL
of them, or say, after 30 days?!? I know these things
can be done manully thru the GUI, looking for a way to
automate it if possible. Thanks, BruceRegarding the structured storage files, we always delete the existing DTS
file before saving so the file only contains one version. We keep the
packages under source control for versioning.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:3bc501c51fff$3d0b4f00$a601280a@.phx.gbl...
> Hello. Is there any command that you can clear old DTS
> package execution Logs? Also, when you store packages as
> *.dts files, it saves the versions right in the file. Is
> there a command that can clear older versions, either ALL
> of them, or say, after 30 days?!? I know these things
> can be done manully thru the GUI, looking for a way to
> automate it if possible. Thanks, Bruce|||And in terms of the package logs, you would need to write
your own utility using the DTS Object model. Search books
online for the RemovePackageLogRecords method. In terms of
deleting based on dates, the package log date is tied to the
PackageLogRecord object.
-Sue
On Thu, 3 Mar 2005 06:42:35 -0800, "Bruce de Freitas"
<bruce@.defreitas.com> wrote:

>Hello. Is there any command that you can clear old DTS
>package execution Logs? Also, when you store packages as
>*.dts files, it saves the versions right in the file. Is
>there a command that can clear older versions, either ALL
>of them, or say, after 30 days?!? I know these things
>can be done manully thru the GUI, looking for a way to
>automate it if possible. Thanks, Bruce|||Thanks Dan, was hoping there was some automated
method/command of clearing the old versions, as they
exist already on numerous packages. Doesn't sound it.
THanks, Bruce
>--Original Message--
>Regarding the structured storage files, we always delete
the existing DTS
>file before saving so the file only contains one
version. We keep the
>packages under source control for versioning.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in
message
>news:3bc501c51fff$3d0b4f00$a601280a@.phx.gbl...
as[vbcol=seagreen]
Is[vbcol=seagreen]
ALL[vbcol=seagreen]
>
>.
>|||ok Sue, intersting stuff. We'll check it out. THanks,
Bruce
>--Original Message--
>And in terms of the package logs, you would need to write
>your own utility using the DTS Object model. Search books
>online for the RemovePackageLogRecords method. In terms
of
>deleting based on dates, the package log date is tied to
the
>PackageLogRecord object.
>-Sue
>On Thu, 3 Mar 2005 06:42:35 -0800, "Bruce de Freitas"
><bruce@.defreitas.com> wrote:
>
as[vbcol=seagreen]
Is[vbcol=seagreen]
ALL[vbcol=seagreen]
>.
>

Command to clear DTS Package Logs + Versions

Hello. Is there any command that you can clear old DTS
package execution Logs? Also, when you store packages as
*.dts files, it saves the versions right in the file. Is
there a command that can clear older versions, either ALL
of them, or say, after 30 days?!? I know these things
can be done manully thru the GUI, looking for a way to
automate it if possible. Thanks, Bruce
Regarding the structured storage files, we always delete the existing DTS
file before saving so the file only contains one version. We keep the
packages under source control for versioning.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:3bc501c51fff$3d0b4f00$a601280a@.phx.gbl...
> Hello. Is there any command that you can clear old DTS
> package execution Logs? Also, when you store packages as
> *.dts files, it saves the versions right in the file. Is
> there a command that can clear older versions, either ALL
> of them, or say, after 30 days?!? I know these things
> can be done manully thru the GUI, looking for a way to
> automate it if possible. Thanks, Bruce
|||And in terms of the package logs, you would need to write
your own utility using the DTS Object model. Search books
online for the RemovePackageLogRecords method. In terms of
deleting based on dates, the package log date is tied to the
PackageLogRecord object.
-Sue
On Thu, 3 Mar 2005 06:42:35 -0800, "Bruce de Freitas"
<bruce@.defreitas.com> wrote:

>Hello. Is there any command that you can clear old DTS
>package execution Logs? Also, when you store packages as
>*.dts files, it saves the versions right in the file. Is
>there a command that can clear older versions, either ALL
>of them, or say, after 30 days?!? I know these things
>can be done manully thru the GUI, looking for a way to
>automate it if possible. Thanks, Bruce
|||Thanks Dan, was hoping there was some automated
method/command of clearing the old versions, as they
exist already on numerous packages. Doesn't sound it.
THanks, Bruce
>--Original Message--
>Regarding the structured storage files, we always delete
the existing DTS
>file before saving so the file only contains one
version. We keep the
>packages under source control for versioning.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in
message[vbcol=seagreen]
>news:3bc501c51fff$3d0b4f00$a601280a@.phx.gbl...
as[vbcol=seagreen]
Is[vbcol=seagreen]
ALL
>
>.
>
|||ok Sue, intersting stuff. We'll check it out. THanks,
Bruce
>--Original Message--
>And in terms of the package logs, you would need to write
>your own utility using the DTS Object model. Search books
>online for the RemovePackageLogRecords method. In terms
of
>deleting based on dates, the package log date is tied to
the[vbcol=seagreen]
>PackageLogRecord object.
>-Sue
>On Thu, 3 Mar 2005 06:42:35 -0800, "Bruce de Freitas"
><bruce@.defreitas.com> wrote:
as[vbcol=seagreen]
Is[vbcol=seagreen]
ALL
>.
>

Command to clear DTS Package Logs + Versions

Hello. Is there any command that you can clear old DTS
package execution Logs? Also, when you store packages as
*.dts files, it saves the versions right in the file. Is
there a command that can clear older versions, either ALL
of them, or say, after 30 days?!? I know these things
can be done manully thru the GUI, looking for a way to
automate it if possible. Thanks, BruceRegarding the structured storage files, we always delete the existing DTS
file before saving so the file only contains one version. We keep the
packages under source control for versioning.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:3bc501c51fff$3d0b4f00$a601280a@.phx.gbl...
> Hello. Is there any command that you can clear old DTS
> package execution Logs? Also, when you store packages as
> *.dts files, it saves the versions right in the file. Is
> there a command that can clear older versions, either ALL
> of them, or say, after 30 days?!? I know these things
> can be done manully thru the GUI, looking for a way to
> automate it if possible. Thanks, Bruce|||And in terms of the package logs, you would need to write
your own utility using the DTS Object model. Search books
online for the RemovePackageLogRecords method. In terms of
deleting based on dates, the package log date is tied to the
PackageLogRecord object.
-Sue
On Thu, 3 Mar 2005 06:42:35 -0800, "Bruce de Freitas"
<bruce@.defreitas.com> wrote:
>Hello. Is there any command that you can clear old DTS
>package execution Logs? Also, when you store packages as
>*.dts files, it saves the versions right in the file. Is
>there a command that can clear older versions, either ALL
>of them, or say, after 30 days?!? I know these things
>can be done manully thru the GUI, looking for a way to
>automate it if possible. Thanks, Bruce|||Thanks Dan, was hoping there was some automated
method/command of clearing the old versions, as they
exist already on numerous packages. Doesn't sound it.
THanks, Bruce
>--Original Message--
>Regarding the structured storage files, we always delete
the existing DTS
>file before saving so the file only contains one
version. We keep the
>packages under source control for versioning.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in
message
>news:3bc501c51fff$3d0b4f00$a601280a@.phx.gbl...
>> Hello. Is there any command that you can clear old DTS
>> package execution Logs? Also, when you store packages
as
>> *.dts files, it saves the versions right in the file.
Is
>> there a command that can clear older versions, either
ALL
>> of them, or say, after 30 days?!? I know these things
>> can be done manully thru the GUI, looking for a way to
>> automate it if possible. Thanks, Bruce
>
>.
>|||ok Sue, intersting stuff. We'll check it out. THanks,
Bruce
>--Original Message--
>And in terms of the package logs, you would need to write
>your own utility using the DTS Object model. Search books
>online for the RemovePackageLogRecords method. In terms
of
>deleting based on dates, the package log date is tied to
the
>PackageLogRecord object.
>-Sue
>On Thu, 3 Mar 2005 06:42:35 -0800, "Bruce de Freitas"
><bruce@.defreitas.com> wrote:
>>Hello. Is there any command that you can clear old DTS
>>package execution Logs? Also, when you store packages
as
>>*.dts files, it saves the versions right in the file.
Is
>>there a command that can clear older versions, either
ALL
>>of them, or say, after 30 days?!? I know these things
>>can be done manully thru the GUI, looking for a way to
>>automate it if possible. Thanks, Bruce
>.
>