December 18, 2020 at 4:57 pm
My apologies in advance as my SSIS knowledge is of a noob. I can create simple packages importing complete tables but that’s about it.
I already have created a few packages that import data from DB2/AS400 using the Microsoft OLE DB Provider for DB2. Importing the entire table is easy about 2M records in one of the tables for example.
So the problem at hand. I’m trying to create another package to import data but limit the number of records based on a Maintained Date field. An item to note is the Maintained Date field is Numeric (8,0) format; 20201218 (12/18/2020) for example. I’m trying to filter where the Maintained Date >= 1 month prior from the current date. In the OLE DB Source Editor, I’ve selected SQL command under Data access mode. Everything I try in the Where clause throws an error.
For simplicity, the fields would be ORNO (ord#), CSNO (cust#), MNDT (maintained date). I can add any of the additional fields after.
Any assistance is greatly appreciated.
December 18, 2020 at 6:06 pm
This is really a DB2 question - from a quick search I found this int(to_char(current date - 2 days, 'YYYYMMDD')) which you should be able to adapt in your where clause as:
WHERE MNDT >= int(to_char(current date - 30 days, 'YYYYMMDD'))
If you really want last month you could do something like this:
WHERE MNDT >= int(to_date(add_months(current date, -1), 'YYYYMMDD'))
I think either of these will work - but I don't have a DB2 system I can test on and I am not sure if the current date reference is correct.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 18, 2020 at 6:16 pm
When we need a dynamic query against DB2 we use variables to contain the SQL commands then use expressions for subbing in parameters. One reason we do this is to have a Parm in SSIS that has the Library name for switching between test/prod, but also use it for dates.
You can build the expression then try it using a query tool against db2 to test it.
We are also on V7.1 for the iSystem data integration components
Attached are some pics of the our conn mgr, expression builder for the variable, and the source component. We've been hitting the 400 for the last 15 years for a variety of things. We also use native SQL400 commands to do some of this logic and even pass in native OS400 commands to do things like CLRPFM (truncate a table) or to copy data between physical file.
Also did you try your commands against the 400 directly? Sometimes easier to use a tool like winsql to try out the commands first if they work there they should work in the source component. You can also use the green screen to run adhoc SQL commands from a command line type GO SQL that should bring you into the SQL editor.
You can also include at the end of your query the row limiter for testing stuff.
FETCH FIRST XX ROWS ONLY
hope this helps
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply