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

Communication between tasks in an SSIS Package

Hi,
I have a Flat File Source and I want to retrieve few properties of it in an Script Component. How do I?

Also, How could I make the file path of Flat File Source or Connection manager dynamic or configurable through some file ?

any input is appreciated.

Fahad

Fahad349 wrote:

Hi,
Also, How could I make the file path of Flat File Source or Connection manager dynamic or configurable through some file ?

Fahad

Look at package configurations. The forum search will be your friend.

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
>.
>

command line parameters are invalid

I have a package that let me to import data from a excel book to a Sql server data base. When I try to run this package like a step into a SQL server Job it show me the next error.

"The command line parameters are invalid. The step failed."

the "command line" looks like this

/FILE "C:\Project\Package.dtsx" /CONNECTION ConexionExcel;"Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source=;Extended Properties=""EXCEL 8.0;HDR=YES"";" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

and in my excel conexion is:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="EXCEL 8.0;HDR=YES";

When i run the "Command Line" in the "Command window" the error is more expecific

"EXCEL 8.0;HDR=YES;" is not valid

So I chanded the "Command Line" in order to run it in de command window(look the double quote in the excel properties)

/FILE "C:\Project\Package.dtsx" /CONNECTION ConexionExcel;"Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source=;Extended Properties="EXCEL 8.0;HDR=YES";" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

then the package RUN, but when i tried to do the same thing in the

sql wizzard, it just dont work and it lost the threat from de package and the errors says this time:

Couldn't find the package

Some one who knows the answer or has any idea to helpme please?

thanks

Use in the Import/Export wizard in SQL.

Adamus

|||According to current Books Online, the double quote is the correct method to escape the quotes inside the parameter. Are you running SP1? I believe they made some changes to the parsing of command lines in SP1, so perhaps that may help.|||

I have a windows 2003 server and the SP1 is only for windows vista, I have installed this version but, i don't know if it has some thing to do with that

|||

I have found the soluction,

I you have seen the command line, the excel connection doesn't have a Data Source because it change in a bucle, I modified connection string parameter in the package in oder to recive a variable with all the connection string, then when i programming my step in the job i don't need to especified the excel connection string.

Thanks Cesar for the aswer

COMException when creating SSIS Package / DataSource

I'm running sql2000 and 2005 (RTM) side by side (and vs2003 and 2005 RTM). I want
to create a IntegrationServices project.
When i want to create a DataSource, i can specify the connection, but
when i click on finish the screen is not closed whatever info/name/etc.
i fill in. I can only cancel the dialog box.
When i add a new SSIS package i get this exception:
===================================
Error creating package (Microsoft Visual Studio)
===================================
Exception of type 'System.Runtime.InteropServices.COMException' was
thrown. (Microsoft.DataWarehouse.VsIntegration)

Program Location:
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy
.OnHierarchyNodeAppending(IHierarchyNode node, IHierarchyNode
parentNode)
at
Microsoft.DataWarehouse.VsIntegration.Hierarchy.Hierarchy.Add(IHierarchy
Node node, IHierarchyNode parentNode)
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy
.AddExistingFileToProject(IFileProjectNode& parentNode, String name,
String fullPath, VSADDITEMOPERATION dwAddItemOperation)
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy
.AddNewFileToProject(IFileProjectNode& parentNode, String name, String
sourceFullPath, VSADDITEMOPERATION dwAddItemOperation)
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.Projec
tItemsExt.AddFromTemplate(String FileName, String Name)
at
Microsoft.DataTransformationServices.Wizards.WizardUtils.AddProjectItemF
romTemplate(WizardInputs wizardInputs, String path, String itemName,
Boolean activateWindow)
at
Microsoft.DataTransformationServices.Wizards.Dts90WizardSaveNewTask.AddP
ackageToProject(Package package, String itemName)
at
Microsoft.DataTransformationServices.Wizards.Dts90WizardSaveNewTask.Crea
teAndAddPackageToProject()
at
Microsoft.DataTransformationServices.Wizards.Dts90WizardSaveNewTask.Exec
ute(WizardInputs wizardInputs)
Any idea what's causing this? ( i reinstalled SQL 2005/SSIS several
times)
Thanks
Marco
Do you use any third-party add-ins for Visual Studio?
Do you use source control integration with Visual Studio,
if yes - which source control provider?|||Yes. i'm using DevExpress CodeRush/Refactor... no source control integration.

Thanks

Marco|||Could you please try removing the DevExpress add-in and see if this fixes the situation? Looking at our code - we are simply notifying environment about new file, and some external code subscribed to this event reports failure, thus cancelling adding a new file. Since it does not happen normally, I suspect the DevExpress add-in causes this.

I'll try to get my hands on the DevExpress CodeRush/Refactor to check what's going on.|||Thanks!

Disabling fixed the problem.. I will send my errorreport also to DevExpress.

Marco