Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 604
  • Last Modified:

using SSIS to import text file and look up record from db

Hello there,
I am importing a text file daily through SSIS, inside the text there are productID, qty, selling price...etc. I need to check the productID against the table that will import the file to see if the same productID's was exist in the previous day. If yes the table column pending_day for the new import will increase 1, if not then the pending_day is 0.
I was thinking to use a derived column to accomplish this but can not figure how to do it. Can you advice what transform component should I use for this purpose? Thanks
0
tomcattyy
Asked:
tomcattyy
  • 13
  • 13
  • 2
1 Solution
 
PedroCGDCommented:
You should use Lookup transformation.
Dou you want an example?
regards!
Pedro
www.pedrocgd.blogspot.com
0
 
PedroCGDCommented:
dear Friend,
I made a project for you understand better!
Create a new SSIS project, add the attached package (rename it to dtsx), execute the Create table SQL Statment and update the connections paths for the text file and SQL in the connection manager.
Helped?!
regards!
pedro
CREATE TABLE [dbo].[Product](
	[ProductID] [varchar](50) NOT NULL,
	[...] [nchar](10) NULL,
	[PendingDay] [int] NULL,
 CONSTRAINT [PK_ProductID] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

Package-EE42-dtsx.txt
testFile.txt
Image1.JPG
0
 
HoggZillaCommented:
Pedro has a good solution, however I reccomend you bring the text file into a staging table and perform the logic in SQL - not SSIS.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PedroCGDCommented:
It all depends on your requirements, off course the logic of an ETL is Extract Transform and Load, and is not doing all the job at same time.
For this reason, you decide what you need and let me know. the diferent is to split into 2 phases... one to extract (import data from textfile to a temp table) and another to make transformation (read from temptable, do the lookup and transform the final table Product)

Helped?
regards!
0
 
tomcattyyAuthor Commented:
Thanks guys, I will try out the Pedro sample first. I can only do this later when I am back from field.

Since the final present would be in a webpage, user prepared the text files and with one button to click and import. Obviously I will need to use a store procedure for the above steps, but which way would be better for this approach?
0
 
PedroCGDCommented:
Try to use a stored procedure to call the ssis package.
If you call directly SSIS package you can get directly the SSIS Logs but you need to install SSIS in the webserver...
If you need more details I can give you the link of a similar subject
regards!
Pedro
0
 
tomcattyyAuthor Commented:
Thanks, I will use store procedures for this, and I apply the code above. Will advice shortly.
0
 
tomcattyyAuthor Commented:
Hello Pedro,

On the lookup component there is a parameter reference, but I did not find it on the left side parameter definition, please tell me from where did that parameter0 be defined?
Does the whole process bring in the new data to the table? I did not see command doing that? Thanks
0
 
PedroCGDCommented:

The loopup transformations is linked by Product Id in the dataset of SQL table with the new dataset from flatfile...
The parameter0 is the ? defined in each SQL command inside OLEDB command...
For existent:
UPDATE Product SET PendingDay=PendingDay+1 WHERE ProductID=?

For new:
UPDATE Product SET PendingDay=0 WHERE ProductID=?

Helped?
regards!
0
 
tomcattyyAuthor Commented:
Hello Pedro,

 On my last post I also asked if the process import the text file into db table? Should there be an OLEDB destination after the SQL command?  Thanks

0
 
PedroCGDCommented:
SQL Command already is upodating the data in the destination...
What data you need to insert? Tell me and I update the package if you need. Only let me know!
Regards!
Pedro
0
 
tomcattyyAuthor Commented:
Sorry not to make it clear enough. All of the fields  on the text file need to be imported into the table. the pendingDay column is calculated through the lookup transform, all other columns like the ones from text file - productID, qty, selling, price - will need to import into table also. Hope is this is clear.  thanks
0
 
HoggZillaCommented:
I realize the approach Pedro is offering will work, I appreciate that. But I suggest you bring it into SQL in it's entirety then run a stored procedure to process the data into the destination table. Keep it simple, remove obstructions. HZ
0
 
PedroCGDCommented:
so.. you want to insert the new data and update the old, it's that?
0
 
tomcattyyAuthor Commented:
Can we place a derived column after the lookup set the value to the calculated pending day and then into the OLEDB destination table.
0
 
tomcattyyAuthor Commented:
Miss your last post,
Insert the new data and insert pending_day column with the calculated lookup value , no change to old data. Thanks
0
 
tomcattyyAuthor Commented:
So the table structure should look like this
CREATE TABLE [dbo].[Product](
      [ProductID] [varchar](50) NOT NULL,
      [qty] [nchar](10) NULL,
      [Selling] [nchar](10) NULL,
      [price] [nchar](10) NULL,
      [PendingDay] [int] NULL,
 CONSTRAINT [PK_ProductID] PRIMARY KEY CLUSTERED
(
      [ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
Thanks
0
 
PedroCGDCommented:
Ok.. see if is this what you need! :-)
Check the package!
Regards!
Pedro
Package-EE42b-dtsx.txt
0
 
tomcattyyAuthor Commented:
Almost there, On the left hand side, The exist path need to do the same as right. No need to modify the old record but insert record into table but with the new calculated pending_day inserted.

I can not figure out in this left side how to assign the derived column with value like
select MynewPending=select pending_day from Product where productId=?

then into the table, Thanks
0
 
PedroCGDCommented:
Check it now! :-)
regards!
Package-EE42c-dtsx.txt
0
 
tomcattyyAuthor Commented:
Hello Pedro,

As I mentioned on last post, on  the exist path will need a devived column also. If the original pending_day is 3 (look up from OLEDB table) for the same productID then the the derived column myNewPending value would be 4. Then going to data conversion  to insert into the table.

 Thanks a lot
0
 
PedroCGDCommented:
ok..
You must add a ID identity to avoid duplicate key in the primary key.

And if you will insert new records, for the query select pending_day from Product where productId=? you will have several rows... maybe you wish tha max Pending day, correct?

Regards!
Pedro
0
 
PedroCGDCommented:
and then you need to add the derived column and change like the image...
helped?

regards!
DerivedColumn.JPG
0
 
tomcattyyAuthor Commented:
Hello Pedro, thanks for the help.
On my real table I have a date column that will keep the compare only to the last business day, so no duplication will happen.
Yes on the devried column, how can I make the Pendingday+1 work? Because the Pendingday is not in the text file, do I have to create an extra parameter to store it before reference it in the derived column?

regards
0
 
PedroCGDCommented:
In the lookup transform select/check the field PendingDay... and it will be able in the derived column.
Helped?
regards!
Pedro
0
 
tomcattyyAuthor Commented:
Thanks Pedro, I can only check 3 hours later, will advice good news then. : )
0
 
PedroCGDCommented:
ok...i hope so!!!! :-)
regards!
pedro
0
 
tomcattyyAuthor Commented:
Thanks Pedro, you are truly helpful, It is a complete solution.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 13
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now