Previously I'd blogged on importing the data from dBase (DBF) files to a SQL Server table which can be found in link below
http://visakhm.blogspot.in/2013/08/importing-dbf-files-to-ssis.html
Now lets see how we can do the reverse.
The scenario is as follows. There's a table in SQL Server (say Customers) whose data needs to be exported to dbf file. The process should pick only the deltas (daily changes) in the table and export it to dbf file with name having timestamp information.
As suggested before, DBF files will allow only 8 character max as the name for the tables. So we need to make sure that we use a name which falls within the limit for the destination. For this reason we will create the package as below
The first step is to copy the DBF template as per source data extract. This will be created first and maintained in a separate folder with the required output metadata.
Once copied the following step will be the data flow task which does the data transfer from the SQL server table to DBF file copy. The data flow will have dynamic SQL command as source as follows
The SQL Command would be based on a user created variable to make it dynamic. The variable will be based on an expression and so will have EvaluateAsExpression property true.
The expression would be like
"SELECT dname,numphds
FROM Dept
WHERE CreatedDate > '" + (DT_WSTR, 10) (DT_DBDATE) @[System::StartTime] + " " + SUBSTRING((DT_WSTR, 10) (DT_DBTIME) @[System::StartTime] ,1,5) + "'"
The above expression extracts data from source tables based on latest timestamp value. This is to pick up only the changes that happened since the start of the package. You can change the logic to make package extract data changes from predefined period like start of day, time since last execution etc.
Next step is to populate the DBF file using this data. The table name would be name of the DBF template that we copied before.
This will be followed by the final step to rename file to include the timestamp information. For this we use a File System Task as below
This will rename file appending timestamp information. The expression used will be as follows
This will make sure the file will get generated each time with timestamp information to indicate the time of data capture as follows.
http://visakhm.blogspot.in/2013/08/importing-dbf-files-to-ssis.html
Now lets see how we can do the reverse.
The scenario is as follows. There's a table in SQL Server (say Customers) whose data needs to be exported to dbf file. The process should pick only the deltas (daily changes) in the table and export it to dbf file with name having timestamp information.
As suggested before, DBF files will allow only 8 character max as the name for the tables. So we need to make sure that we use a name which falls within the limit for the destination. For this reason we will create the package as below
The first step is to copy the DBF template as per source data extract. This will be created first and maintained in a separate folder with the required output metadata.
Once copied the following step will be the data flow task which does the data transfer from the SQL server table to DBF file copy. The data flow will have dynamic SQL command as source as follows
The expression would be like
"SELECT dname,numphds
FROM Dept
WHERE CreatedDate > '" + (DT_WSTR, 10) (DT_DBDATE) @[System::StartTime] + " " + SUBSTRING((DT_WSTR, 10) (DT_DBTIME) @[System::StartTime] ,1,5) + "'"
The above expression extracts data from source tables based on latest timestamp value. This is to pick up only the changes that happened since the start of the package. You can change the logic to make package extract data changes from predefined period like start of day, time since last execution etc.
Next step is to populate the DBF file using this data. The table name would be name of the DBF template that we copied before.
This will be followed by the final step to rename file to include the timestamp information. For this we use a File System Task as below
This will rename file appending timestamp information. The expression used will be as follows
No comments:
Post a Comment