How to load the current date date from parent to several child tables in SSIS package

SSIS and Import/Export
1

Hi Friends,

I am loading daily data through SSIS package on daily basis to my local system.
THe process is I will download the (.txt) files from Main Server through FTP in SSIS package and save in my local system.
And i will load those datas into Database .
First i will load full data in Master table.
ANd from Master table i will load into relevant Parent and child table.
.
The Problem is I will get the file data as yesterday date but while loading into DB i must load it into current date.
SO using Experssion i have changed by loading into Current date through GETDATE() and it will load in current date into All the DB.

SO when i am using this SSIS package to load into server .If i need to load a day before day data in server.It takes the current date only .so l need a solution like on which date i loading in Master tables.THose date only it get need to load in Parent and CHild tables.
How to do this please any one help me.

2

I read through your description several times but I really cannot understand what you need. please post some examples of incoming data (a few rows will do) and the resulting rows that you want to see in the master, parent and child tables

3

How to retrieve the currently Loaded date from master table and those data need to be loaded in to parent and child table.

In Master table i have a column like (Ftpdate),In this column the file date will get filled.
So when i try to load the data into parent and child table It takes the Ftpdate as Current system date.

In Master database i have these date datas

2015-05-10

2015-05-11

2015-05-12

SO load from this Master table to parent and child table i am using using expresssion like

select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;

If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.

My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.

If i run this expression to server which is present somewhere i cannot change the system date in server.

SO this expression fails,

so please tell me the Expression on which date i have loaded the master same date need to laoded in Parent and child table without changing the system Date.

4

From your query:

SELECT B.ID
     , A.*
FROM FLATFILE_INVENTORY AS A
INNER JOIN DMS_INVENTORY AS B ON A.ACDealerID = B.DMSDEALERID
     AND A.StockNumber = B.STOCKNUMBER
     AND A.InventoryDate = B.INVENTORYDATE
     AND A.VehicleVIN = B.VEHICLEVIN
WHERE convert(DATE, A.[FtpDate]) = convert(DATE, GETDATE())
     AND convert(DATE, B.Ftpdate) = convert(DATE, getdate());

which tables are master, parent and child? Also, since your query only uses two tables, which one is missing: master, parent or child?

5

BTW there is no English word "datas". The word "data" is already plural.

1 Like
6

Flatfile_Inventory is Master.
Dms_Inventory is Parent.

I will compare with this two table Master and Parent and then i will load revlavent date into child table.

7

First i will load from Master to Parent Table using the below expression

SELECT * from [dbo].[FLATFILE_INVENTORY] where
convert(date,[FtpDate]) = convert(date,GETDATE());

Once the data has loaded.

I will load 3 different child tables
using the below Expressions

1;
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;

2:
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;

3:
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;

8

I'd probably do something like this:

add a variable for the number of days difference:

DECLARE @days int;

Use DATEADD in my query:

WHERE DATEADD(days, -@days, convert(date,A.[FtpDate])) = convert(date,GETDATE())

Set the variable in a Stored Procedure call.

9

Hi
Can you please explain me more so that it will be very help full for me please.